Reading subsequent rows in a query in DB2.



IBM's flagship relational database management system

Reading subsequent rows in a query in DB2.

Postby gopurs » Fri Apr 02, 2021 11:06 am

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
gopurs
 
Posts: 3
Joined: Fri Sep 09, 2016 10:30 am
Has thanked: 0 time
Been thanked: 0 time

Re: Reading subsequent rows in a query in DB2.

Postby enrico-sorichetti » Fri Apr 02, 2021 6:56 pm

duplicate, posted and answered on ibmmainframes
locked, will deleted in a few days
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2967
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 160 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post