Page 1 of 1

Date Difference.

PostPosted: Mon Mar 09, 2015 9:35 am
by Vineet
Hi All,
I have 2 Queries Please reply to same.

1. I have a file having LRECL = 100, RECFM = FB. There are 2 Date Fields (Date1 & Date2). Need to Calculate the Date Difference (Date2 - Date1). Output File to have all records from Input File along with Date Difference. Both Dates is in format YYYY-MM-DD (2015-03-08).

2. Output file generated (file having Date Difference), need to be Sortd in a Way that If the Difference between 2 Date is Same where Date1 is same then both records need to be written to the Output file. Below is an Example. Here Need to SORT on the basis of Start Positon = 1, End Psition = 20. Output File Should not have Date Difference Field. O/P file to hve LRECL = 100

ABCD|1234|2015-03-10|2015-03-17|XYZ|8  ---> Date1 =  2015-03-10, Date2 = 2015-03-17, Date Difference = 8,    SORT CRITERIA = ABCD|1234|2015-03-10   --> Written to output File
ABCD|1234|2015-03-10|2015-03-17|XYZ|8  ---> Date1 =  2015-03-10, Date2 = 2015-03-17, Date Difference = 8,    SORT CRITERIA = ABCD|1234|2015-03-10  --> Written to output File
EFGH|2345|2015-03-21|2015-03-26|KKK|6 ---> Date1 =  2015-03-21, Date2 = 2015-03-26, Date Difference = 6,     SORT CRITERIA = EFGH|2345|2015-03-21  --> Written to output File
IJKL  |7890|2015-03-21|2015-03-26|KKK|6 ---> Date1 =  2015-03-21, Date2 = 2015-03-26, Date Difference = 6,     SORT CRITERIA = IJKL  |7890|2015-03-21 -->  Written to output File
MNOP|8888|2015-03-21|2015-03-26|KKK|6 ---> Date1 =  2015-03-21, Date2 = 2015-03-26, Date Difference = 6,    SORT CRITERIA = MNOP|8888|2015-03-26  --> Written to output File
MNOP|8888|2015-03-21|2015-03-26|KKK|6 ---> Date1 =  2015-03-21, Date2 = 2015-03-26, Date Difference = 6,    SORT CRITERIA = MNOP|8888|2015-03-26 --> Written to output File
MNOP|8888|2015-03-21|2015-03-30|KKK|10 ---> Date1 =  2015-03-21, Date2 = 2015-03-26, Date Difference = 10, SORT CRITERIA = MNOP|8888|2015-03-30 --> Should NOT Get Written to File.


Thanks
Kin Rgd's

Code'd

Re: Date Difference.

PostPosted: Mon Mar 09, 2015 1:19 pm
by BillyBoyo
1) DFSORT has many date functions. What did you try?

2) Is the data you need to compare (the date) in a fixed positions? Can their only be pairs of records with the same date, or can there also be three or more?

Re: Date Difference.

PostPosted: Mon Mar 09, 2015 7:45 pm
by Vineet
Hi Billy,

Below is my reply for UR queries.

1. I tried Datediff. Example what I seen over net. For Date format is YYYYMMDD to use DATEDIFF. Want to confirm can I use DATEDIFF, for Date format like YYYY-MM-DD.
2. It's a Fixed Position. We can have more records for same date, but has to be in combination of SORT fields specified (I mention as SORT CRITERIA).

Thanks
Kind Rgd's

Re: Date Difference.

PostPosted: Mon Mar 09, 2015 7:57 pm
by BillyBoyo
For information, DATEDIFF is not just restricted to using YYYYMMDD.

However, that is the format you have, apart from it having the "/"s in. If you "get rid of" the slashes, you should be able to proceed.

Extend the record temporarily: OVERLAY=(101:11,4,16,2,19,2). Then you can use that date for your DATEDIFF.

You can use IFOUTLEN or BUILD, as is most convenient, to turn your record back to 100 bytes.

Re: Date Difference.

PostPosted: Mon Mar 09, 2015 8:06 pm
by Vineet
Thanks Billy for Clarifying the Doubt. Part 2 of my Query is still Unanswered.

ABCD|1234|2015-03-10|2015-03-17|XYZ|8 ---> Date1 = 2015-03-10, Date2 = 2015-03-17, Date Difference = 8, SORT CRITERIA = ABCD|1234|2015-03-10 --> Written to output File
ABCD|1234|2015-03-10|2015-03-17|XYZ|8 ---> Date1 = 2015-03-10, Date2 = 2015-03-17, Date Difference = 8, SORT CRITERIA = ABCD|1234|2015-03-10 --> Written to output File
EFGH|2345|2015-03-21|2015-03-26|KKK|6 ---> Date1 = 2015-03-21, Date2 = 2015-03-26, Date Difference = 6, SORT CRITERIA = EFGH|2345|2015-03-21 --> Written to output File
IJKL |7890|2015-03-21|2015-03-26|KKK|6 ---> Date1 = 2015-03-21, Date2 = 2015-03-26, Date Difference = 6, SORT CRITERIA = IJKL |7890|2015-03-21 --> Written to output File
MNOP|8888|2015-03-21|2015-03-26|KKK|6 ---> Date1 = 2015-03-21, Date2 = 2015-03-26, Date Difference = 6, SORT CRITERIA = MNOP|8888|2015-03-26 --> Written to output File
MNOP|8888|2015-03-21|2015-03-26|KKK|6 ---> Date1 = 2015-03-21, Date2 = 2015-03-26, Date Difference = 6, SORT CRITERIA = MNOP|8888|2015-03-26 --> Written to output File
MNOP|8888|2015-03-21|2015-03-30|KKK|10 ---> Date1 = 2015-03-21, Date2 = 2015-03-26, Date Difference = 10, SORT CRITERIA = MNOP|8888|2015-03-30 --> Should NOT Get Written to File.

If U see last 3 Records "MNOP|8888|2015-03-21", want to write the record where Date difference = 6 i.e. 2 Records, don't want the record with Date Difference = 10 to be written to the file. How to do this.

Thanks

Re: Date Difference.

PostPosted: Mon Mar 09, 2015 9:27 pm
by BillyBoyo
Use OUTFIL with INCLUDE= or OMIT= to do the selection.