MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

How to find the DAY difference between 2 date fields in SORT

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
pzmohanty
Beginner


Joined: 20 May 2004
Posts: 97
Topics: 43
Location: hyderabad, India
PostPosted: Wed Feb 08, 2006 4:21 am    Post subject: How to find the DAY difference between 2 date fields in SORT Reply with quote
Hi All,

I have a specific requirement :-

In DB2 we have the facility of performing Arithmetic Operations on DATE fields, like subtracting n days from a date or finding the days difference between 2 date fields .

Ex :
select current_date - date('2005-12-27') from sysibm.sysdummy1;
select current_date - 3 months from sysibm.sysdummy1;

My requirement is , can we perform the same arithmetic operations on date fields using SORT.

Specifically what I am looking for is , I have a file having 2 Date fields.
I have to extract those records from the file where days differnce between these 2 fields is less than 3 months.

Currently we are doing this using a program , but just wondering if you people have some tricks under the sleeves to do the same using SORT.

Thanks
_________________
Priya Ranjan Mohanty
Consultant
Kanbay Software (I) pvt. Ltd.
Hyderabad
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Wed Feb 08, 2006 8:52 am    Post subject: Reply with quote
pzmohanty,

It can be done but will involve multiple passes.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
pzmohanty
Beginner


Joined: 20 May 2004
Posts: 97
Topics: 43
Location: hyderabad, India
PostPosted: Wed Feb 08, 2006 9:31 am    Post subject: Reply with quote
Hi ,

I coded following 3 step JCL to achieve , what I am looking for.
Please let me know , if it can be done in better way.
Code:

//*****GET The system date - 3 months*************************
//STEP0010 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)         
//***********************************************************
//SYSTSIN  DD  *                                             
  DSN SYSTEM(DB2D)                                           
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') -       
      LIB ('XXX.YYY.ZZZ')                     
  END                                                       
/*                                                           
//SYSIN    DD  *                                             
SELECT CURRENT_DATE - 3 MONTHS FROM SYSIBM.SYSDUMMY1;       
/*                                                         
//SYSREC00 DD  DSN=DOIG.CP000000.GLOB.FFB.DATE.CTC,         
//             DISP=(,CATLG,DELETE),                       
//             UNIT=SYSDA,                                 
//             SPACE=(CYL,(250,250),RLSE)                   
//SYSPUNCH DD  DUMMY                                       
//SYSTSPRT DD SYSOUT=*                                     
//SYSPRINT DD SYSOUT=*                                     
//SYSUDUMP DD SYSOUT=*                                     
//***************************************************** 
//*** dynamically create the control card ****************           
//STEP002  EXEC PGM=SORT         
//*****************************************************           
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD DSN=DOIG.CP000000.GLOB.FFB.DATE.CTC,DISP=SHR 
//SORTOUT  DD DSN=DOIG.CP000000.GLOB.FFB.FINAL.CTC,         
//         DISP=(NEW,CATLG,DELETE),                         
//         UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE),             
//         DCB=(LRECL=80,RECFM=FB,BLKSIZE=0)               
//SYSIN    DD *                                             
SORT FIELDS=COPY                                                     
 INREC FIELDS=(1:7,4,5:1,2,7:4,2,2X)                                   
 OUTFIL OUTREC=(2X,C'SORT FIELDS=COPY',80:X,/,                         
     2X,C'INREC FIELDS=(1,600,210,4,204,2,207,2)',80:X,/,             
     2X,C'OUTFIL FILES=1,INCLUDE=(601,8,ZD,GE,',1,8,C'),OUTREC=(1,600)',
     80:X)                                                                 
/*
//****************************************************                         
//STEP003  EXEC PGM=SORT                                               
//*****************************************************                       
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD DSN=DOIG.CP000000.GLOB.VSB.CLMPNOC1.T,DISP=SHR           
//SORTOF1  DD DSN=DOIG.CP000000.GLOB.FFB.CLMPNOC1.T,                   
//         DISP=(NEW,CATLG,DELETE),                                   
//         UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE),                       
//         DCB=(LRECL=600,RECFM=FB,BLKSIZE=0)                         
//SYSIN    DD DSN=DOIG.CP000000.GLOB.FFB.FINAL.CTC,DISP=SHR

_________________
Priya Ranjan Mohanty
Consultant
Kanbay Software (I) pvt. Ltd.
Hyderabad
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Wed Feb 08, 2006 10:10 am    Post subject: Reply with quote
pzmohanty,

If you are intention is to pick all records which are 3 months old (90 days) from the currrent date , then it is very simple and can be done in just 1 simple step. If you have syncsort of z/os 1.1 and higher , this 1 step JCL will give you the desired results.

Code:

//STEP0100 EXEC PGM=SORT             
//SYSOUT   DD SYSOUT=*               
//SORTIN   DD *                     
20051108                             
20060102                             
20040103                             
20040604                             
//SORTOUT  DD SYSOUT=*               
//SYSIN    DD *                     
  SORT FIELDS=COPY                   
  INCLUDE COND=(1,8,ZD,GE,&DATE1P-90)
/*                                 


The catch here is 3 months does not essentially translate to 90 days.

ex:
Code:

SELECT CURRENT DATE - 3 MONTHS           
  FROM SYSIBM.SYSDUMMY1;                 


The result is
Code:

2005-11-08                               


Actually Current date - (2005-11-08 ) is 92 days

If you are happy with 90 days selection then you can use the sample JCL shown above.

In any case your 3 step can actually be done in 2 just 2 steps. Can you tell me the format(yyyy-mm-dd or mm-dd-yyyy or ...) of the date in your input file. Also what format of date do you get when you run your db2 query?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
pzmohanty
Beginner


Joined: 20 May 2004
Posts: 97
Topics: 43
Location: hyderabad, India
PostPosted: Wed Feb 08, 2006 3:13 pm    Post subject: Reply with quote
Hi Kolusu ,

As per the requirement , I have to extract exactly only those records where date field value is not prior to exactly 3 months.

Format of the date field in file is 'MM/DD/YYYY'.
Also the DB2 query returns the date in 'MM/DD/YYYY' format.

Thanks
_________________
Priya Ranjan Mohanty
Consultant
Kanbay Software (I) pvt. Ltd.
Hyderabad
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Wed Feb 08, 2006 3:33 pm    Post subject: Reply with quote
Assuming that your date fields in the file starts from pos 204 for 10 , the following JCL will give you the desired results.

Code:

//STEP0100 EXEC PGM=IKJEFT01                   
//SYSTSPRT DD  SYSOUT=*,DCB=BLKSIZE=121       
//SYSPRINT DD  SYSOUT=*                       
//SYSTSIN  DD  *                               
 DSN SYSTEM(xxxx)                             
 RUN  PROGRAM(DSNTIAUL) -                     
      PLAN(DSNTIAUL)    -                     
      PARMS('SQL')      -                     
      LIB('DB2P.RUNLIB.LOAD')                 
//SYSREC00 DD DSN=&T1,DISP=(,PASS),UNIT=SYSDA,SPACE=(TRK,(1,1),RLSE)
//SYSPUNCH DD SYSOUT=*                         
//SYSIN    DD *                               
 SELECT CHAR('CUTOFFDATE')                     
       ,CHAR(',')                             
       ,CHAR('''')                             
       ,CURRENT DATE - 3 MONTHS               
       ,CHAR('''')                             
       ,CHAR(' ',57)                           
   FROM SYSIBM.SYSDUMMY1                       
   ;                                           
//STEP0200 EXEC PGM=SORT                       
//SYMNAMES DD DSN=&T1,DISP=SHR         
//SYSOUT   DD SYSOUT=*                       
//SORTIN   DD DSN=YOUR INPUT DSN,
//            DISP=SHR                         
//SORTOUT  DD SYSOUT=*                       
//SYSIN    DD *                               
  SORT FIELDS=COPY                             
  INCLUDE COND=(204,10,CH,GE,CUTOFFDATE)         
/*                   


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
pzmohanty
Beginner


Joined: 20 May 2004
Posts: 97
Topics: 43
Location: hyderabad, India
PostPosted: Thu Feb 09, 2006 7:16 am    Post subject: Reply with quote
Hi Kolusu ,

Thanks for the suggestion of using SYMNAMES DD in SORT , which has significantly reduced the code complexity.

But , I think the date comparison will not work as we are comparing dates in 'MM/DD/CCYY' format. Any date comparisons with these format will behave as Character comparison.
For example , '12/28/2005' will be greater than '12/27/2006' ( which is wrong)

Code:

//STEP0200 EXEC PGM=SORT                       
//SYMNAMES DD DSN=&T1,DISP=SHR         
//SYSOUT   DD SYSOUT=*                       
//SORTIN   DD DSN=YOUR INPUT DSN,
//            DISP=SHR                         
//SORTOUT  DD SYSOUT=*                       
//SYSIN    DD *                               
  SORT FIELDS=COPY                             
  INCLUDE COND=(204,10,CH,GE,CUTOFFDATE)         
/*                   


I had modified the code to perform date comparisons on date format of 'CCYYMMDD'.
Code:

//*******************************************************
//STEP0010 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(4,LT)     
//*******************************************************       
//STEPLIB  DD  DSN=XXX.YYY.ZZZ,DISP=SHR       
//SYSTSIN  DD  *                                         
  DSN SYSTEM(XXX)                                       
  RUN PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARMS('SQL') -   
      LIB ('AAA.BBB.CCC')                 
  END                                                   
/*                                                       
//SYSIN    DD  *                                         
 SELECT CHAR('CUTOFFDATE'),                             
        CHAR(','''),                                     
        SUBSTR(CHAR(CURRENT DATE - 3 MONTHS),7,4) ,     
        SUBSTR(CHAR(CURRENT DATE - 3 MONTHS),1,2) ,           
        SUBSTR(CHAR(CURRENT DATE - 3 MONTHS),4,2) ,           
        CHAR('''') ,                                         
        CHAR(' ',59)                                         
   FROM SYSIBM.SYSDUMMY1;                                     
/*                                                           
//SYSREC00 DD  DSN=&T1,         
//             DISP=(,PASS),                         
//             UNIT=SYSDA,                                   
//             SPACE=(CYL,(250,250),RLSE)                     
//SYSPUNCH DD  DUMMY                                         
//SYSTSPRT DD SYSOUT=*                                       
//SYSPRINT DD SYSOUT=*                                       
//SYSUDUMP DD SYSOUT=*                                       
//*******************************************************
//STEP0200 EXEC PGM=SORT                                     
//*******************************************************
//SYMNAMES DD DSN=&T1,DISP=SHR 
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD DSN=DATASET.NAME.INPUT,         
//            DISP=SHR                                       
//SORTOF1  DD DSN=OUTPUT.DATASET.NAME1,               
//         DISP=(NEW,CATLG,DELETE),                               
//         UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE),                   
//         DCB=(LRECL=600,RECFM=FB,BLKSIZE=0)                     
//SORTOF2  DD DSN=OUTPUT.DATASET.NAME2,   
//         DISP=(NEW,CATLG,DELETE),                               
//         UNIT=SYSDA,SPACE=(CYL,(10,10),RLSE),                   
//         DCB=(LRECL=600,RECFM=FB,BLKSIZE=0)                     
//SYSIN    DD *                                                   
  SORT FIELDS=COPY                                                 
  INREC FIELDS=(1,600,210,4,204,2,207,2)                           
  OUTFIL FILES=1,INCLUDE=(601,08,CH,GE,CUTOFFDATE),OUTREC=(1,600) 
  OUTFIL FILES=2,SAVE,OUTREC=(1,600)                               
/*                                                                 
//*                                                               

Thanks
_________________
Priya Ranjan Mohanty
Consultant
Kanbay Software (I) pvt. Ltd.
Hyderabad
Back to top
View user's profile Send private message Send e-mail Yahoo Messenger
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
PostPosted: Mon Oct 25, 2010 12:23 pm    Post subject: Reply with quote
pzmohanty,

With PTF UK90025 for z/OS DFSORT V1R10 and PTF UK90026 for z/OS DFSORT V1R12(Oct, 2010), DFSORT now supports date arithmetic which can be used to calculate the number of days difference between two dates. The result is an 8-byte value consisting of a sign and 7 digits (sddddddd). If the first date is greater than or equal to the second date, the sign is + (plus). If the first date is less than the second date, the sign is - (minus).


Code:

//STEP0100 EXEC PGM=SORT                       
//SYSOUT   DD SYSOUT=*                         
//SORTIN   DD *                                 
99991231 00010101                               
20081231 20091231                               
20100101 20091231                               
//SORTOUT  DD SYSOUT=*                         
//SYSIN    DD *                                 
  SORT FIELDS=COPY                             
  INREC OVERLAY=(35:1,8,Y4T,DATEDIFF,10,8,Y4T) 
//*


The output from the above job is

Code:

99991231 00010101                 +3652058
20081231 20091231                 -0000365
20100101 20091231                 +0000001


For complete details of date arithmetic functions and other new functions see "User Guide for DFSORT PTFs UK90025 and UK90026" paper (sortugph.pdf) at:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000242
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group