Reading subsequent rows in a query.

gopurs · New User Joined: 04 Apr 2005 Posts: 13

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

Pandora-Box · Posted: Fri Apr 02, 2021 12:52 pm

Hi gopurs

Your explanation is pretty vague for me

When transaction is closed (TR), Why are you not calculating difference between 1/14/21 and 1/5/21

For me it looks all the rows point to same transaction for TRANS 1

What is D8QC1,T8QC1,T9QC1? Are they intermediary states for same transaction?

Joerg.Findeisen · Posted: Fri Apr 02, 2021 1:07 pm

@gopurs: Do not cross-post in different forums. It will not speed up things.

sergeyken · Posted: Fri Apr 02, 2021 5:51 pm

What output from this example is expected?

gopurs · New User Joined: 04 Apr 2005 Posts: 13

gopurs · New User Joined: 04 Apr 2005 Posts: 13

sergeyken · Posted: Fri Apr 02, 2021 7:32 pm

You need to design the logic: the steps needed to process your data as yo wish. But first: to clarify for yourself what you really want?

Again: this is not a DB2 question. This is an algorithmic question; it has nothing to do with a particular tool, or language. Just the ability to think logically. This should be the major duty for a software developer, before he is allowed clicking buttons.

Something like this:
1) order data by Trans ID, and by Date
2) within each Trans ID group, detect the lines where (unknown) entity T8QR1 changes
3) calculate the dates differences between each pair of detected lines, and associate the difference with the (unknown) entity of the first line of each pair
4) repeat this for each Trans ID

Pandora-Box · Posted: Fri Apr 02, 2021 7:54 pm

You would need to write a MULTIPLE pass SORT anyway

Logic

-Pick the first value per state
-This dataset should be replicated twice one with sequence starting 0 and other sequence starting 1
-Join both these datasets based on sequence
- For Matched records calculate the date difference (you might need to align the date to standard format)

sergeyken · Posted: Fri Apr 02, 2021 8:29 pm

Rohit Umarjikar · Posted: Sat Apr 03, 2021 2:32 am

This has nothing to with a sql but basically you are asking how to build the logic for this requirement which is almost defined above in few replies see if that works.

If you want to know further , how to write sql for this requirement then then show us table data , restate rules clearly again and expected output data.

gopurs · New User Joined: 04 Apr 2005 Posts: 13

Thanks everyone for your responses.

sergeyken · Posted: Sun Apr 04, 2021 7:46 pm

Rohit Umarjikar · Posted: Sun Apr 04, 2021 8:03 pm

ALTDATE-‘m/d/yy’ probably or combination of other scalar functions but I have never a need to use it yet.

If it’s a DB2 question then TS basically require to compare current row with previous row and when value changes calculate the date difference. Db2 analytical functions like RANK- OLAP can be used to get that , not sure if lag or lead will help here but that’s another way to get this done.