Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Comparing two date fileds in the same input file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
gvenkateshmca

New User


Joined: 10 Jul 2008
Posts: 6
Location: 23rdSt-Ely Ave,Long Island city,NY

PostPosted: Fri Aug 27, 2010 4:03 pm    Post subject: Comparing two date fileds in the same input file
Reply with quote

Hi,

I need help for the below query .

I have one file with the two fileds have timestamp .I need to compare the both dates (date format is YYYY/MM/DD), If the date difference is more than one day I need to write one file otherwise I need to write into another file .

Input records

key date1 date2
01111 2010-08-23 2010-08-22
01111 2010-08-22 2010-08-20
01111 2010-08-23 2010-08-25
01111 2010-08-20 2010-08-21

Output1
----------

01111 2010-08-20 2010-08-21
01111 2010-08-23 2010-08-22

Output2
--------
01111 2010-08-22 2010-08-20
01111 2010-08-23 2010-08-25
Quote:
Back to top
View user's profile Send private message

sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Aug 27, 2010 5:54 pm    Post subject:
Reply with quote

VenkateswarluG,
Please provide RECFM, LRECL of the input file along with the positions for both the date fields.

It would also help us to have your DFSort function level.

Thanks,
Back to top
View user's profile Send private message
gvenkateshmca

New User


Joined: 10 Jul 2008
Posts: 6
Location: 23rdSt-Ely Ave,Long Island city,NY

PostPosted: Fri Aug 27, 2010 7:33 pm    Post subject:
Reply with quote

Hi,
The record length of the file is 209 and record format is FB,
the first date filed is starting from 49 to 58 and second date filed starting from 128 to 137
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Aug 27, 2010 7:34 pm    Post subject:
Reply with quote

VenkateswarluG,

sorry to intrude but IT work requires precision.

you don't have a timestamp, you have a date field.

VenkateswarluG wrote:
(date format is YYYY/MM/DD)

VenkateswarluG wrote:

key date1 date2
01111 2010-08-23 2010-08-22
01111 2010-08-22 2010-08-20
01111 2010-08-23 2010-08-25
01111 2010-08-20 2010-08-21

so which is it? yyyy/mm/dd or yyyy-mm-dd

also, use [ code ] [ /code ] tags to provide better spacing for you examples:

Code:

key   date1      date2
01111 2010-08-23 2010-08-22
01111 2010-08-22 2010-08-20
01111 2010-08-23 2010-08-25
01111 2010-08-20 2010-08-21


if you don't know how, either
  • go to your profile and check the 'Advanced' Quick reply options to obtain the buttons
  • click the hyperlink that I provided to learn how to manually enter BB code
Back to top
View user's profile Send private message
gvenkateshmca

New User


Joined: 10 Jul 2008
Posts: 6
Location: 23rdSt-Ely Ave,Long Island city,NY

PostPosted: Fri Aug 27, 2010 8:57 pm    Post subject:
Reply with quote

In the time stamp i need to comapre the date filed , that is yyyy-mm-dd .Sorry in the above mail i have give as YYY/MM/DD .

Regards
Venkat
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Fri Aug 27, 2010 10:43 pm    Post subject:
Reply with quote

gvenkateshmca,

I don't know any function within DFSort to do the date difference directly. Since you haven't yet given your DFSort function level, I am assuming you have the latest PTF available...

Check if below works for you...
Code:

//STEP01   EXEC PGM=SORT                                               
//SYSOUS   DD SYSOUT=*                                                 
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD YOUR INPUT FILE                                           
//OUT      DD YOUR DATE DIFF. OF 1 FILE                                 
//SAVE     DD YOUR REMAINING RECORDS FILE                               
//SYSIN    DD *                                                         
 INREC IFTHEN=(WHEN=INIT,                                               
  OVERLAY=(211:049,04,054,02,057,02,2X,221:211,08,Y4T,TOJUL=Y4T,3X,     
           231:128,04,133,02,135,02,2X,241:231,08,Y4T,TOJUL=Y4T,3X,     
           251:((((221,04,ZD,SUB,+1901),MUL,+36525),DIV,+100),ADD,     
                   225,03,ZD),TO=ZD,LENGTH=5,5X,                       
           261:((((241,04,ZD,SUB,+1901),MUL,+36525),DIV,+100),ADD,     
                   245,03,ZD),TO=ZD,LENGTH=5,5X,                       
           271:(261,5,ZD,SUB,251,5,ZD),TO=ZD,LENGTH=5))                 
 SORT FIELDS=COPY                                                       
 OUTFIL FNAMES=OUT,INCLUDE=(271,5,ZD,EQ,+1,OR,                         
                            271,5,ZD,EQ,-1),BUILD=(1,209)               
 OUTFIL FNAMES=SAVE,BUILD=(1,209)                                       
/*                                                                     


However, using cobol, it gets less complicated. Below is a cobol logic for the same.
Code:

*                                                                 
      COMPUTE WS-CCYYMMDD-N1 = FUNCTION                           
              INTEGER-OF-DATE(WS-CCYYMMDD1)  --> your first date field                     
      END-COMPUTE                                                 
*                                                                 
      COMPUTE WS-CCYYMMDD-N2 = FUNCTION                           
              INTEGER-OF-DATE(WS-CCYYMMDD2) --> your second date field                     
      END-COMPUTE                                                 
*                                                                 
                                                                 
      COMPUTE WS-CCYYMMDD-N =                                     
              WS-CCYYMMDD-N1 - WS-CCYYMMDD-N2                     
      END-COMPUTE                                                 
      EVALUATE WS-CCYYMMDD-N                                     
              YOUR LOGIC GOES HERE...                                         
      END-EVALUATE                                               
                                                                 


Thanks,
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Sat Aug 28, 2010 12:50 am    Post subject:
Reply with quote

gvenkateshmca,
Please use updated sort card as below. In the earlier solution,I had a mistake in field positioning and SAVE was missing in the final OUTFIL statement.

However, please note that this formula should only work when the dates are between 1901-01-01 through 2099-12-31.

Code:

INREC IFTHEN=(WHEN=INIT,                                               
  OVERLAY=(211:049,04,054,02,057,02,2X,221:211,08,Y4T,TOJUL=Y4T,3X,     
           231:128,04,133,02,136,02,2X,241:231,08,Y4T,TOJUL=Y4T,3X,     
           251:((((221,04,ZD,SUB,+1901),MUL,+36525),DIV,+100),ADD,     
                   225,03,ZD),TO=ZD,LENGTH=5,5X,                       
           261:((((241,04,ZD,SUB,+1901),MUL,+36525),DIV,+100),ADD,     
                   245,03,ZD),TO=ZD,LENGTH=5,5X,                       
           271:(261,5,ZD,SUB,251,5,ZD),TO=ZD,LENGTH=5))                 
 SORT FIELDS=COPY                                                       
 OUTFIL FNAMES=OUT,INCLUDE=(271,5,ZD,EQ,+1,OR,                         
                            271,5,ZD,EQ,-1),BUILD=(1,209)               
 OUTFIL FNAMES=SAVE,SAVE,BUILD=(1,209)                     


Thanks,
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Wed Nov 03, 2010 12:59 am    Post subject:
Reply with quote

gvenkateshmca,

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 *                                               
----+----1----+----2----+----3----+----4----+----5----+----6--
01111 2010-08-23 2010-08-22                                   
01111 2010-08-22 2010-08-20                                   
01111 2010-08-23 2010-08-25                                   
01111 2010-08-20 2010-08-21                                   
//OUT1     DD SYSOUT=*                                       
//OUT2     DD SYSOUT=*                                       
//SYSIN    DD *                                               
  SORT FIELDS=COPY                                           
  INREC IFTHEN=(WHEN=INIT,                                   
  OVERLAY=(81:7,10,UFF,M11,LENGTH=8,18,10,UFF,M11,LENGTH=8)),
  IFTHEN=(WHEN=INIT,OVERLAY=(97:81,8,Y4T,DATEDIFF,89,8,Y4T)) 
                                                             
  OUTFIL FNAMES=OUT1,BUILD=(1,80),                           
  INCLUDE=(97,8,SFF,EQ,+1,OR,97,8,SFF,EQ,-1)                 
                                                             
  OUTFIL FNAMES=OUT2,BUILD=(1,80),SAVE                       
//*


The out1 file will have the following records

Code:

01111 2010-08-23 2010-08-22
01111 2010-08-20 2010-08-21


The out2 file will have the following records

Code:

01111 2010-08-22 2010-08-20
01111 2010-08-23 2010-08-25


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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SORTJOIN - Copy Matched and Unmatched... Steve Ironmonger DFSORT/ICETOOL 5 Tue Jan 17, 2017 4:26 pm
No new posts Copy RECFM =VB TO FB file with RECL =... sahil41352 DFSORT/ICETOOL 3 Wed Dec 28, 2016 11:29 pm
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 31 Tue Dec 06, 2016 8:04 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us