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

Tricky requirement using sort


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
N.Nirmal Raj

New User


Joined: 19 Sep 2013
Posts: 7
Location: India

PostPosted: Tue Oct 01, 2013 10:54 pm
Reply with quote

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
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Oct 02, 2013 12:18 am
Reply with quote

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
View user's profile Send private message
N.Nirmal Raj

New User


Joined: 19 Sep 2013
Posts: 7
Location: India

PostPosted: Wed Oct 02, 2013 6:38 am
Reply with quote

Skolusu,

Apologies typo error your are correct..
Back to top
View user's profile Send private message
N.Nirmal Raj

New User


Joined: 19 Sep 2013
Posts: 7
Location: India

PostPosted: Wed Oct 02, 2013 6:48 am
Reply with quote

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
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Wed Oct 02, 2013 10:50 pm
Reply with quote

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
View user's profile Send private message
N.Nirmal Raj

New User


Joined: 19 Sep 2013
Posts: 7
Location: India

PostPosted: Thu Oct 03, 2013 10:10 pm
Reply with quote

Hi Skolusu,

Hats off to you, awesome logic. Learn t about using SYMBOLS in SORT.
A very expertise solution.
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 -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts Sort First/last record of a subset th... DFSORT/ICETOOL 7
No new posts how to calculate SUM value for VB fil... DFSORT/ICETOOL 1
No new posts how to calculate SUM for VB file usin... JCL & VSAM 1
Search our Forums:

Back to Top