Reading subsequent rows in a query in DB2.

[gopurs]

I have a situation where I have to process each record in update time stamp order, looking for TD record, if I find one, read a future TD (greater than current timestamp with a different status code) OR a TR record with no route status for the same trans# - if I find one, take that update timestamp date, and add as an attribute to the TD row as a new column - if I don't find any, this new column will be default date - once you do that, to calculate the work days - you subtract update timestamp date from the new column date (excluding default date).

Basically, I am trying to find out how many days a status stayed for a transaction till it changes or the transaction was closed (TR status). Appreciate any help!

Here is a sample

Trans 1 TD T8QC1 1/5/21
Trans 1 TD T8QC1 1/5/21 – no date to prior row
Trans 1 TD T8QC1 1/8/21 – no date to prior row
Trans 1 TD T9QC1 1/10/21 – when this happens, calculate days worked as 5 days and allocate to the first instance of D8QC1

Trans 1 TD T9QC1 1/12/21 – no date to prior row
Trans 1 TR blank 1/14/21 – when this happens, calculate days worked as 4 days and allocate to first instance of T9QC1

[enrico-sorichetti]

duplicate, posted and answered on ibmmainframes
locked, will deleted in a few days