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: 6968
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 Extra character appears in file when ... Balu5491 All Other Mainframe Topics 1 Wed Jul 26, 2017 2:39 pm
No new posts SSH - known_hosts file configuration vasanthz All Other Mainframe Topics 2 Wed Jul 26, 2017 2:10 am
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Write out NODUPS but just from one file Jay Villaverde DFSORT/ICETOOL 8 Fri Jul 14, 2017 12:44 am
No new posts How to add header with Date(YYMMDD) i... Rajan Moorthy DFSORT/ICETOOL 2 Thu Jul 06, 2017 11:44 pm


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