I have an input file of record length of 80 and Record format is fixed block. The layout is Jobname, Ran Date, ran time.
I want select the record where the jobname is unique OR if it is duplicate (on Jobname),
(i) the difference between the two run is 8 hours or less then select both the record and
(ii) if the diffrence is more than 8 hours then select the record which has greater ran date and time.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Gylbharat,
Hmm I thought my control cards are self explanatory.
The basic idea to check for a time difference is to convert the time format into seconds
HH:MM:SS = HH * 3600 + MM * 60 + SS = total time in seconds
So convert the time for every record into the desired format. However since we are dealing with jobs we need to care if the job ran the next day. ie. a job can start 11:30 PM and finish at 1 am the next day. if you just go with the time difference then it would be more than 8 hour (8* 3600 = 28,800 seconds) window. So we need to check the date difference too. If the date difference is greater than zero we need to add 86,400 seconds (24 hours = 24 * 3600) and then subtract the time in seconds from the next record.
We use keybegin to push the record on to the next record.
Since OP wanted both unique and duplicate job records, I used join keys operation to figure out how many records are there for each job name. So JNF2 will sum the records based on the jobname and build a joined record.
Using OUTFIL we check for 8 hour window records and unique records.
The best method to debug/learn is coding IFTHEN statement at a time and checking the results. If my memory serves right you are using syncsort , so you would need an additional pass to mimic the functionality of JNF2CNTL