View previous topic :: View next topic
Author
Message
N.Nirmal Raj New User Joined: 19 Sep 2013Posts: 7 Location: India
Hi,
I have a file from which i need to extract records 12 months data prior last quarter end date. To put more light on it, say I am running the job on 'JAN14' or 'FEB14' or 'MAR14' I need to extract records from 'DEC13' TO 'JAN13'.
Input File
1111 2013-09-13 xxxxxxxx
1112 2013-08-13 xxxxxxxx
1113 2013-07-13 xxxxxxxx
1114 2013-06-13 xxxxxxxx
1115 2013-05-13 xxxxxxxx
1116 2013-04-13 xxxxxxxx
1117 2013-03-13 xxxxxxxx
1118 2013-02-13 xxxxxxxx
If I run the job on SEP13 then i should get data 12 months data prior quarter end date (i.e.) records from '2013-06-13' to '2012-06-13'.
I used the below sort to get last 12 months data but stuck with actual requirement.
//SYSIN DD *
SORT FIELDS=COPY
INCLUDE COND=(1,5,6,10,CH,GT,DATE2(-)-12)
Back to top
Skolusu Senior Member Joined: 07 Dec 2007Posts: 2205 Location: San Jose
N.Nirmal Raj wrote:
If I run the job on SEP13 then i should get data 12 months data prior quarter end date (i.e.) records from '2013-06-13' to '2012-06-13' .
Unless I am mistaken the Quarter end dates for any year are
March 31
June 30
September 30
December 31
If your current date is September 2013 then Last Quarter end date is 2013-06-30, so your actual range of Dates should be 2012-06-30 to 2013-06-30.
How did you pick the 13 as the date?
Back to top
N.Nirmal Raj New User Joined: 19 Sep 2013Posts: 7 Location: India
Skolusu,
Apologies typo error your are correct..
Back to top
N.Nirmal Raj New User Joined: 19 Sep 2013Posts: 7 Location: India
Actually this is how my file looks contains month end dates only,
Input File
1111 2013-09-30 xxxxxxxx
1112 2013-08-31 xxxxxxxx
1113 2013-07-31 xxxxxxxx
1114 2013-06-30 xxxxxxxx
1115 2013-05-31 xxxxxxxx
1116 2013-04-30 xxxxxxxx
1117 2013-03-31 xxxxxxxx
1118 2013-02-28 xxxxxxxx
Back to top
Skolusu Senior Member Joined: 07 Dec 2007Posts: 2205 Location: San Jose
use the following DFSORT JCL
Code:
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
//SORTOUT DD DSN=&&S,DISP=(,PASS),SPACE=(TRK,(1,0),RLSE)
//SYSIN DD *
OPTION COPY
INREC OVERLAY=(01:DATE1,
11:01,8,Y4T,LASTDAYQ,TOGREG=Y4T,
21:11,8,Y4T,SUBMONS,+3,TOGREG=Y4T,
31:21,8,Y4T,LASTDAYM,TOGREG=Y4T,
41:31,8,Y4T,SUBYEARS,+1,TOGREG=Y4T,
51:31,8,Y4T,TOGREG=Y4T(-),
62:41,8,Y4T,TOGREG=Y4T(-))
OUTFIL BUILD=(C'BEGIN-QTR-DATE,C''',62,10,C'''',/,
C'END-QTR-DATE,C''',51,10,C'''',80:X)
//*
//STEP0200 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SYMNAMES DD DISP=SHR,DSN=&&S
//SYMNOUT DD SYSOUT=*
//SORTIN DD *
1111 2013-09-30 XXXXXXXX
1112 2013-08-31 XXXXXXXX
1113 2013-07-31 XXXXXXXX
1114 2013-06-30 XXXXXXXX
1115 2013-05-31 XXXXXXXX
1116 2013-04-30 XXXXXXXX
1117 2013-03-31 XXXXXXXX
1118 2013-02-28 XXXXXXXX
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INCLUDE COND=(6,10,CH,GT,BEGIN-QTR-DATE,AND,
6,10,CH,LE,END-QTR-DATE)
//*
Back to top
N.Nirmal Raj New User Joined: 19 Sep 2013Posts: 7 Location: India
Hi Skolusu,
Hats off to you, awesome logic. Learn t about using SYMBOLS in SORT.
A very expertise solution.
Back to top
Please enable JavaScript!