Help with matching up transactions

[MRitter]

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.

Hopefully that explained it.

My data would look something like this:

[AndrewHughes]

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.

[Combined Table]:

LOAD

TicketID

,TransactionID

,TransactionDate

,1 as Flag.Paused

FROM UnPausedTable;

CONCATENATE

LOAD

TicketID

,TransactionID

,TransactionDate

,0 as Flag.Paused

FROM PausedTable;

[Ordered Table]:

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?

[PeekMatch]:

LOAD 

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.