IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Reading subsequent rows in a query.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
gopurs

New User


Joined: 04 Apr 2005
Posts: 13

PostPosted: Fri Apr 02, 2021 11:06 am
Reply with quote

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
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1590
Location: Andromeda Galaxy

PostPosted: Fri Apr 02, 2021 12:52 pm
Reply with quote

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?
Back to top
View user's profile Send private message
Joerg.Findeisen

Active Member


Joined: 15 Aug 2015
Posts: 513
Location: Bamberg, Germany

PostPosted: Fri Apr 02, 2021 1:07 pm
Reply with quote

@gopurs: Do not cross-post in different forums. It will not speed up things.
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1079

PostPosted: Fri Apr 02, 2021 5:51 pm
Reply with quote

What output from this example is expected?
Code:
T8QC1 - 5 days
T9QC1 - 4 days
Or something else?

The task is very unclear, but it seems to be no DB2 question, not a bit. Highly likely it cannot be done in SQL only, and hence, some algorithm needs to be developed (after clarification), and implemented by means of other tools - REXX, COBOL, SORT, whatever else.

Not a DB2 question. Data only extracted from DB2, and next - nothing to do with SQL.
Back to top
View user's profile Send private message
gopurs

New User


Joined: 04 Apr 2005
Posts: 13

PostPosted: Fri Apr 02, 2021 6:27 pm
Reply with quote

Pandora-Box wrote:
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?


Hi Pandora-Box,

I am looking for the time it is taking to change from one status to another. Yes, in this example all rows are pointing to a single transaction (transaction 1). A single transaction can be in different status till it gets closed.

Yes, T8QC1, T9QC1 are different states of the transaction. D8QC1 is a typo, it should be T9QC1.
Back to top
View user's profile Send private message
gopurs

New User


Joined: 04 Apr 2005
Posts: 13

PostPosted: Fri Apr 02, 2021 6:33 pm
Reply with quote

sergeyken wrote:
What output from this example is expected?
Code:
T8QC1 - 5 days
T9QC1 - 4 days
Or something else?

The task is very unclear, but it seems to be no DB2 question, not a bit. Highly likely it cannot be done in SQL only, and hence, some algorithm needs to be developed (after clarification), and implemented by means of other tools - REXX, COBOL, SORT, whatever else.

Not a DB2 question. Data only extracted from DB2, and next - nothing to do with SQL.


Hi sergeyken,

Yes, that is what I am looking in the output. Once I get that value for each transaction I will apply the avg for the status code to know how much time each status is taking. Thanks again for the help.
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1079

PostPosted: Fri Apr 02, 2021 7:32 pm
Reply with quote

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
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1590
Location: Andromeda Galaxy

PostPosted: Fri Apr 02, 2021 7:54 pm
Reply with quote

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)
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1079

PostPosted: Fri Apr 02, 2021 8:29 pm
Reply with quote

Pandora-Box wrote:
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)

If using SORT, one pass should be enough.

Anyway, non-DB2 question.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2671
Location: NYC,USA

PostPosted: Sat Apr 03, 2021 2:32 am
Reply with quote

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.
Back to top
View user's profile Send private message
gopurs

New User


Joined: 04 Apr 2005
Posts: 13

PostPosted: Sat Apr 03, 2021 4:35 am
Reply with quote

Thanks everyone for your responses.
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1079

PostPosted: Sun Apr 04, 2021 7:46 pm
Reply with quote

gopurs wrote:
Thanks everyone for your responses.

There is not a minor sign that the TS is interested in following any advice, or doing any homework at all. Waiting for the final working solution?

BTW: how come that date values from DB2 are received in this weird format: ‘m/d/yy’ - ??? 824.gif
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2671
Location: NYC,USA

PostPosted: Sun Apr 04, 2021 8:03 pm
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts DB2 SQL query to read and update data... DB2 12
No new posts Write records with 3 or less rows in ... DFSORT/ICETOOL 3
No new posts Cobol-db2 : SQL query is running for ... DB2 4
No new posts Build two or more rows given some con... DFSORT/ICETOOL 9
No new posts SYSIBM Tables Query DB2 8
Search our Forums:

Back to Top