I have a table that runs like an audit. It captures users, order numbers, person updating, activity (what they were updating) and date. There are other columns in there but these are the relevant ones. Querying the table, I need to find out when a specific activity by any user (activity = ‘x’) happens after any update by a specific user (updated_by = ‘x’). For example,
=============================================================
OrderNum | Updated_by | Activity | Updated_dt
2409363 | USER1 | Depart Changed | 2017-10-24 10:05
2409363 | USER2 | Paydetail Insert| 2017-10-25 07:49
2409363 | USER3 | Stop Inserted | 2017-10-25 13:09
What I need to find is every time a “stop inserted” happens after USER2 does their part. These rows I listed above all have the same OrderNum, so it’s just one order, but I really need to query the whole table for OrderNums that meet that criteria.
I tried using the row_number() over (partition by) window function, and that didn’t work. I’ve also tried a self-join with order by and that didn’t work either. Any help is greatly appreciated.