Fetch records for a particular quarter using date



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Fetch records for a particular quarter using date

Postby Healy » Mon May 12, 2014 10:05 pm

I have a requirement as follows

I have a flat file which contains a Date in the format 'YYMMDD'. In the Record there is some additional information along with the date. The input file may contain dates corresponding to all months of a year

I need to extract all the Data for a particular quarter.

That is the Dates corresponding to Jan-March, Apr-June, July-Sept, Oct-Dec

If i run my Job say on the 1st of April then only the dates that belong in the quarter of Jan-Mar should be fetched. Similarly if i run my Job on 1st of July, only the dates corresponding to quarter Apr-June qualify.

To Summarise i need the dates three months prior to the current date. I also need to take into consideration leap year condition if it does arise for a particular year.

Example:
If The input flat file is as follows and i am looking for dates in the first quarter assuming the current date is 1st of April 2014(140401)


140224
140423
140903
140105
140809
140331


My output should be

140224
140105
140331



I have tried using “DATEDIFF”, “SUBMONS” and used it with Y2T for the 'YYMMDD' format. However i get a “Syntax Error” when i try to do so. I have tried many variations of the sort card using DTNS for conversion but am unable to get past the Syntax error.

Example Sort Card i Used :-

//SYSIN DD *
   OPTION COPY
   OUTREC BUILD=(1:1,6,Y2T,SUBMONS,+3,Y2T)
/*


I received a syntax error for the same with an '*' placed on the SUBMONS. Similarly when i used DATEDIFF i receive the same error.

FYI:
I am using SYNCSORT: Version 1.4.1


Please let me know if there is any other method other than using DATEDIFF or SUBMONS to fulfill my requirement using DFSORT/SYNCSORT.
Any Help would be deeply appreciated.

Thank You,
Healy
Healy
 
Posts: 14
Joined: Mon May 12, 2014 9:47 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Fetch records for a particular quarter using date

Postby NicC » Tue May 13, 2014 3:11 am

You are more like;y to get a relevant reply if you post in the correct part of the forum! Syncsort queries are dealt with in the Syncsort section of the forum. I have moved the post for you.

Also, please use the code tags when posting code - I have added them for you.

I see no attempt to include the records that you want - all you are doing (if your cards worked) is copying the entire file and changing the date.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Fetch records for a particular quarter using date

Postby BillyBoyo » Tue May 13, 2014 4:01 am

In your SORT documentation, does it tell you how to conver Julian to Gregorian? If so, do that, and just check the month.

To my mind it is bad, bad, bad, to use the current date. How are you ever going to re-run anything? Test anything? Pararllel-run anything, etc?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Fetch records for a particular quarter using date

Postby Healy » Tue May 13, 2014 7:29 am

NicC,

I am very much new to this forum and this is my first post. I am sorry for not posting int the correct section and not using code tags. I shall keep this in mind for future posts.
You are correct, i am not at this point of time selecting and including the records. This is because if can get my SUBMONS or DATEDIFF to work then i can easily put an "INCLUDE COND" and compare the resulting value with the dates on file. That is why instead of writing the whole sort card with comparisons now itself, i am just trying to see if i am able to obtain the subtracted date(SUBMONS) or the difference in dates in terms of months(DATEDIFF).

i am getting a Syntax Error for the same, hence i wanted to know if there was any other way through SYNCSORT wherein i can get the subtracted date which will also fulfill my requirements.

BillyBoyo,

Yes i am aware of how to convert Julian to Gregorian. However my date is in Gregorian format i.e YYMMDD, so you mean to say i convert this to Julian i.e YYYYDDD or YYDDD. How will i Check the month in this format?. I am using the current date because the Job can be run anytime of the year, after a quarter is over.

Thank you for your responses.
Healy
 
Posts: 14
Joined: Mon May 12, 2014 9:47 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Fetch records for a particular quarter using date

Postby NicC » Tue May 13, 2014 1:46 pm

I am very much new to this forum and this is my first post

So the first rule should be fresh in your mind!
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Fetch records for a particular quarter using date

Postby BillyBoyo » Tue May 13, 2014 2:02 pm

Of the Gregorian date, you look at the month. 01-03 is Qtr 1, 04-06 Qtr 2 etc.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Fetch records for a particular quarter using date

Postby Healy » Tue May 13, 2014 3:47 pm

BIlly,

I understand but how can i look at the month when the Job can be run any time of the year. For example my Job can run either on the 16th of April which means i need dates three months prior to 16th of April. SImilarly if i run my job on 1st December which refers to the fourth quarter that is three months prior to the run date.

This is why i need to calculate the prior dates falling under that quarter using the Current Date. I do not know before hand which quarter the job will run for so i cannot harcode the values for the month accordingly.

Healy
Healy
 
Posts: 14
Joined: Mon May 12, 2014 9:47 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Fetch records for a particular quarter using date

Postby BillyBoyo » Tue May 13, 2014 4:18 pm

Are your "quarters" the quarters of the year, or the three months prior to the run-date?

16 April gives you Jan-Mar, or something-approaching-three-months-to April 16?

If the former, you look at the month-quarter you are running in, and work out what the previous quarter is - only remotely tricky for running in the first quarter.

Perhaps you can do some examples of run-dates, and the range you would expect for that?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: Fetch records for a particular quarter using date

Postby Healy » Tue May 13, 2014 4:53 pm

The quarters are three months prior to the run date(sorry for the confusion). That is if my run date is 16th of April 2014 then dates that correspond to exactly three months before the run date. It is as you said "Something approaching 3 months to April 16"

i understand when you say to work out what the previous quarter is through the month in the current date. However i do not know how to do that using SYNCSORT.

Example 1 : if i take 16thApril 2014 as my run date(i.e 140416) then i am going to be looking for months in the range 01-03. However, how do i achieve this through SYNCSORT because i cannot use any variables or anything and the Job can any time of the year


Records that will qualify
140223
140123
140405
140409
140322



Records that will not Qualify

140416
140417
140623
140712
140818
140915



Example 2: if i take say "2nd Jan 2014" as my run date(i.e 140102) then i have to look for months in the range 10-12.


Records that will qualify
141224
141205
141104
141028
140101


Records that will not Qualify

140102 (That is the Run date itself)
140114
140103
140213
140522
140924


I know my requirement can be easily achieved through the use of SUBMONS or DATEDIFF but the problem is i get a Syntax error every time i use them.

Please let me know if you require any more clarifications.

Healy
Healy
 
Posts: 14
Joined: Mon May 12, 2014 9:47 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Fetch records for a particular quarter using date

Postby BillyBoyo » Tue May 13, 2014 5:22 pm

Try to find out some information from the Analyst.

Should it be a number of days? Or Calendar Months? Because Months don't all have the same number of days, "three months" can be a moving feast, or expressed as an (approximate) number of days. It is down to what the business requirement is.

If it is "Calendar Months", then work out, pencil and paper, how the last day of each month should translate to the first day of each period and then get that confirmed.

Are you using the "Machine Date" for your run-date?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Next

Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post