-1

Trying to match some events in SQL. A user will place a work order into a wait status. This has a datetime stamp. Once resolved, they will release the wait. This has a datetime stamp.

I can use Lead Window Function if B comes after A, but they are not always in perfect chronological order so I have some orphaned records on both A/B sides that I cannot match up

In this example, how can I match line 4 with line 1?

work_order_id status datetime           datetime_end (lead)   line_Nbr
======================================================================
10            Wait   2022-01-21 00:56:32                       1
10            Wait   2022-01-21 01:00:00 2022-01-24 12:30:40   2
10            Release 2022-01-24 12:30:40                      3
10            Release 2022-01-24 12:30:40                      4
jarlh
  • 42,561
  • 8
  • 45
  • 63
Winston Snyder
  • 67
  • 2
  • 7

1 Answers1

0

I ended up splitting into 2 queries and creating a key in each based on work_order + row_nbr.

I then joined these queries based on the new id's. Results appear to be correct.

Winston Snyder
  • 67
  • 2
  • 7