I basically have the same question as specified in the question:
Pairing Send and Receive Data Rows in SQL
But I do not have a user/batch combination that could make things unique, so basically having something like this:
KEY MODULE EVENTDTTM ACTION
-------------------------------------
1 A 01/01 SENT
2 A 02/01 RECEIVE
4 A 03/01 SENT
3 A 04/01 RECEIVE
5 A 05/01 SENT
6 A 06/01 SENT
7 A 07/01 RECEIVE
8 A 08/01 SENT
There are missing events, so they don't add up nicely and the ordering relies on the DTTM, not the key order, but I need to establish some pairing for each record, ending up with something like:
MODULE CUR_KEY NEXT_KEY PREV_KEY
-----------------------------------------------
A 1 2 NULL
A 2 NULL 1
A 3 NULL 4
A 4 3 NULL
A 5 (NULL/7) NULL
A 6 7 NULL
A 7 NULL 6
A 8 NULL NULL
Basically matching the sent-receive pairs, such that they link with the closest one according to the timestamp. Whether 5 points to 7 or NULL is not important as long as the rest is in order. 8 does not have any match (yet), and its opposite (a receive without sent) also exists.
This is slightly above my cognitive level, so any help appreciated :) Should I join receive as cur and subquery a suitable next and then union with the opposite or...?