How to find second Transaction date in a Table

arundwh · Post by **arundwh** » Thu Dec 03, 2009 3:24 am

Find the second day transaction amount for a Accnt_id.

Ex: Epected records are marked in red.
Acct_Hist
Accnt_id, Trans_Dt, Seq_Num
1001 23-10-09 1
1001 24-10-09 1
1001 25-10-09 1
1002 26-10-09 1
1002 27-10-09 1
1002 27-10-09 2
1002 28-10-09 1

Output:
Accnt_id, Trans_Dt ,Seq_Num
1001 24-10-09 1
1002 27-10-09 1
1002 27-10-09 2

gssr · Post by **gssr** » Thu Dec 03, 2009 3:52 am

Can you be Specific?

You are getting all the Trans_Date other than the first one?
Is that your requirement?
If "Yes" then ,why did you drop the date of 25/10/09 for 1001?

Sreenivasulu · Post by **Sreenivasulu** » Thu Dec 03, 2009 3:52 am

Do a group by on transaction date. put this to a dataset with the record order.
Then get the second record by doing @INROWNUM =2

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Dec 03, 2009 5:37 am

Assuming you are checking by the ids following with same date and incrementing sequence, you can

1.) Sort data on id and date with keyChange capture
2.) Set a stage variable - say svIncrNum - to zero on change of id
3.) if the next id is same with incrementNumber = svIncrNum + 1, then take it. else reset the 'previous' values.

ray.wurlod · Post by **ray.wurlod** » Thu Dec 03, 2009 2:30 pm

Sort by transaction date in ascending order, then use Remove Duplicates stage to preserve the last from each group. (I'm assuming a maximum of two rows per group.)