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
 

 

Select records with date in the last year

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

New User


Joined: 21 Mar 2009
Posts: 11
Location: UK

PostPosted: Wed Aug 24, 2011 10:20 pm    Post subject: Select records with date in the last year
Reply with quote

I want to include records in my sort which have a date within the last year.

The input file has a date in the format YYYY-MM-DD

Code:

2008-10-01 RECORD 01
2009-10-02 RECORD 02
2010-12-03 RECORD 03
2011-05-05 RECORD 04


I have come up with this solution

Code:

INCLUDE COND=(1,10,CH,GE,DATE1(-)-365)


This works fine but does not cater for leap years when the date calculation should be DATE1(-)-366.

I have come up with various complicated solutions for this but I'm keep to keep the code as simple as possible. Is there a simple solution?

Thanks for any help.
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7251

PostPosted: Wed Aug 24, 2011 10:38 pm    Post subject:
Reply with quote

What does the user want? Sometimes we are too "literal" about things. You might get lucky.

Perhaps if you can convert 60th day of the year to a date, then see if it is Feb 29 or not? Then one or t'other.
Back to top
View user's profile Send private message
joeempson

New User


Joined: 21 Mar 2009
Posts: 11
Location: UK

PostPosted: Wed Aug 24, 2011 10:51 pm    Post subject:
Reply with quote

I hear what you're saying and in fact this is not specifically for a user requirement. It came out of some analysis I was doing and I just wanted to make sure that I haven't missed the DATE1(-)-1year function
icon_biggrin.gif
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Wed Aug 24, 2011 10:58 pm    Post subject: Reply to: Select records with date in the last year
Reply with quote

joeempson,

Use the following DFSORT JCL which will give you the desired results. I assumed that your input is FB recfm and LRECL=80

Code:

//STEP0100 EXEC PGM=SORT                                     
//SYSOUT   DD SYSOUT=*                                       
//SORTIN   DD *                                             
2008-10-01 RECORD 01                                         
2009-10-02 RECORD 02                                         
2010-12-03 RECORD 03                                         
2011-05-05 RECORD 04                                         
//SORTOUT  DD SYSOUT=*                                       
//SYSIN    DD *                                             
  SORT FIELDS=COPY                                           
  INREC OVERLAY=(81:DATE1,81,8,Y4T,SUBYEARS,+1,TOGREG=Y4T(-))
  OUTFIL BUILD=(1,80),INCLUDE=(1,10,CH,GE,89,10,CH)         
//*


SUBYEARS function subtracts n years from the p,m,Yxx date field. If the resulting date is February 29 of a non-leap year, it will be adjusted backwards to a valid date of February 28. ex :

Code:

//STEP0100 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD *                                           
20090228                                                   
20080229                                                   
20100228                                                   
20110228                                                   
20120229                                                   
//SORTOUT  DD SYSOUT=*                                     
//SYSIN    DD *                                           
  SORT FIELDS=COPY                                         
  INREC OVERLAY=(30:1,8,Y4T,SUBYEARS,+1,TOGREG=Y4T(-),     
                 45:1,8,Y4T,SUBDAYS,+365,TOGREG=Y4T(-))   
//*


Using leap year and non leap year dates you can see how the date is adjusted when using the SUBYEARS function vs subtracting 365 days.
Code:

20090228                     2008-02-28     2008-02-29
20080229                     2007-02-28     2007-03-01
20100228                     2009-02-28     2009-02-28
20110228                     2010-02-28     2010-02-28
20120229                     2011-02-28     2011-03-01
Back to top
View user's profile Send private message
joeempson

New User


Joined: 21 Mar 2009
Posts: 11
Location: UK

PostPosted: Thu Aug 25, 2011 6:02 pm    Post subject:
Reply with quote

That's perfect !!

Exactly what I needed. Thank you
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 Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm


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