I have 2 files. One shows the transactions when a ticket is paused. The other shows transaction when a ticket is unpaused.
They both contain a ticketID.
A ticket can be paused and unpaused multiple times.
So what I need to do is be able to read the paused transaction and match it to the unpaused transaction that is closest the the pause datetime if there are multiples. Then read the next paused transaction and do it again for as many times as it has happened. If the ticket was not unpaused the latest time then there would be no unpaused time and I would move onto the next ticket ID.
I'm not sure what final result you are seeking. I would likely join both tables with a flag for paused, unpaused and sort by date. Then you can use a group by statement or a PEEK() function to search for the next record.
Something like this.
,1 as Flag.Paused
,0 as Flag.Paused
LOAD * RESIDENT [Combined Table]
ORDER BY TicketID, TransactionDate desc;
Then I don't know what output you want? But I'm guessing something like this?
TransactionID , IF(PEEK(Flag.Paused,-1)=1 AND PEEK(TicketID,-1)=TicketID, PEEK(TicketID,-1), NULL())
RESIDENT [Ordered Table];
You can play around with the PEEK function to get the desired result.