Date Difference.



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Date Difference.

Postby Vineet » Mon Mar 09, 2015 9:35 am

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
Vineet
 
Posts: 86
Joined: Tue Jun 19, 2007 11:38 am
Has thanked: 0 time
Been thanked: 0 time

Re: Date Difference.

 

Re: Date Difference.

Postby BillyBoyo » Mon Mar 09, 2015 1:19 pm

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?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Date Difference.

Postby Vineet » Mon Mar 09, 2015 7:45 pm

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
Vineet
 
Posts: 86
Joined: Tue Jun 19, 2007 11:38 am
Has thanked: 0 time
Been thanked: 0 time

Re: Date Difference.

Postby BillyBoyo » Mon Mar 09, 2015 7:57 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Date Difference.

Postby Vineet » Mon Mar 09, 2015 8:06 pm

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
Vineet
 
Posts: 86
Joined: Tue Jun 19, 2007 11:38 am
Has thanked: 0 time
Been thanked: 0 time

Re: Date Difference.

Postby BillyBoyo » Mon Mar 09, 2015 9:27 pm

Use OUTFIL with INCLUDE= or OMIT= to do the selection.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post