Need help in SORT and getting the record with latest date

jegan_selvan · New User Joined: 23 Oct 2006 Posts: 24 Location: India

Hi All,

Need help from you on the below scenario.

I/P File

Record No(10 Char) Suffix (04 Char) Filler(06 Char) Date (08Char)

0000000001|aaaa|rec001|20101010
0000000001|aaaa|rec002|20101014
0000000002|bbbb|rec002|20101012
0000000003|cccc|rec003|20101014
0000000003|cccc|rec003|20101018

Output File:

0000000001|aaaa|rec002|20101014
0000000002|bbbb|rec002|20101012
0000000003|cccc|rec003|20101018

Requirement: I need to sort the record with Record number and Suffix ( Ascending).
If there is two records with same Record# and Suffix then Verify the date. Take the record which is having the latest date and write to output file.

Thanks in advance for your timely help.

sqlcode1 · Active Member Joined: 08 Apr 2010 Posts: 577 Location: USA

jegan_selvan,
See if below works for you...

jegan_selvan · New User Joined: 23 Oct 2006 Posts: 24 Location: India

Thanks a lot. Working to my requirement.

Regards,

Jegan S

jegan_selvan · New User Joined: 23 Oct 2006 Posts: 24 Location: India

Hi All,

I need one more additional requirement on the same itself.

Already Done: I need to sort the record with Record number and Suffix ( Ascending).
If there is two records with same Record# and Suffix then Verify the date. Take the record which is having the latest date and write to output file

Additional One:

1. I need to sort the record with Record number and Suffix ( Ascending).
If there is two records with same Record# and Suffix then Verify the date. Take the record which is having the latest date and write to output file
2. I need to write the skipped record with older date ( as XDUP) as different file.

sqlcode1 · Active Member Joined: 08 Apr 2010 Posts: 577 Location: USA

jegan_selvan,
Use below... All the skipped records are written to XSUM dataset.