Does anyone know of a way of getting the beginning and ending dates of quarter ranges, given that you have the starting and ending dates of each quarter. I can do it easily if one quarter is selected the tricky part is when more than one is selected. For instance the user selects to Q1, Q3 and Q4? Should I just have four filters or is there a more efficient way of doing it?

Thanks

This message has been edited. Last edited by: FP Mod Chuck,

Hi Trudy,

Not certain of your setup with who can access validation tables, but FTVFSPD has a listing of fiscal periods along with their start & end dates. You could probably write a define to obtain the info (Q1 = FSPD 01 Start Date & FSPD 03 End date) you want then use a -READFILE to create params off them then -Include the procedure in your report.

As per your example (selected Q1, Q3 and Q4) you will have to test three date range which is, according to me, the simplest way.
Assuming "normal" civil quarter :
1- Jan 1 To March 31st
2- July 1st To Sept 30th
3- Oct 1st To Dec 31st

So you cannot use a single start / end date test since no date should be selected between May 1st and June 30th.

Does your user just select a quarter (Q1, Q2, Q3 or Q4) or must also have to select a year (which I think it should and gives more flexibility) ?

How you quarter selection is displayed and come from where (static/dynamic drop list, radio, ...) ?

What I would do is
1- have a dynamic drop list with the available years
2- have a static multi drop list or radio or check box with 4 options : a- Q1, b- Q2, c- Q3, d- Q4
3- based on selection made built your date range (start/end) by quarter

But this is one of the multiple options that can came up from other people and since I don't know all your request, it may not be suitable.

How's this? ( easy maintenance / low-code solution )

-PROMPT &Q1.(,).Q1;
-PROMPT &Q2.(,).Q2;
-PROMPT &Q3.(,).Q3;
-PROMPT &Q4.(,).Q4;

TABLE FILE data
...
WHERE
( DATE GE q1_start AND DATE LE q1_end AND '&Q1' EQ 'yes' )
OR
( DATE GE q2_start AND DATE LE q2_end AND '&Q2' EQ 'yes' )
OR
( DATE GE q3_start AND DATE LE q3_end AND '&Q3' EQ 'yes' )
OR
( DATE GE q4_start AND DATE LE q4_end AND '&Q4' EQ 'yes' )
END

-SET &&LDATE = 01011900;
-IF &&CHGDATE IS 'Y' GOTO RPTDATE;
-SET &TIMENOW = HHMMSS ('A8');
-SET &CHCKHH = EDIT (&TIMENOW,'99$$$$$$');
-*
-RPTDATE
-*
-SET &DAY1 = EDIT(&YMD,'$$$$99');
-SET &MON1 = EDIT(&YMD,'$$99$$');
-SET &YER1 = EDIT(&YMD,'99$$$$');
-SET &CNT1 = IF &YER1 LT '90' THEN '20' ELSE '19';
-SET &FULLYR = &CNT1 | &YER1;
-SET &LASTYR = (&FULLYR - 1);
-SET &ALASTYR = EDIT(&LASTYR,'$$99');
-SET &LASTMO = DECODE &MON1 ('01' '12' '02' '01' '03' '02'
- '04' '03' '05' '04' '06' '05'
- '07' '06' '08' '07' '09' '08'
- '10' '09' '11' '10' '12' '11');
-SET &LASTMO2= DECODE &MON1 ('01' '11' '02' '12' '03' '01'
-SET &LASTYR2=&LASTYR - 1;
-SET &&CYBEG = '0101' | &FULLYR;
-SET &&PYBEG = '0101' | &LASTYR;
-SET &&PYEND = '1231' | &LASTYR;
-SET &&PYEND2 = '1231' | &LASTYR2;
-SET &&CFYBEG = '1001' | &LASTYR;
-SET &&CFYEND = '0930' | &FULLYR;
-SET &&PFYBEG = '1001' | &LASTYR2;
-SET &&PFYEND = '0930' | &LASTYR;
-SET &&CFY = &FULLYR;
-SET &&PFY = &LASTYR;
-SET &&NXFY = &NXTYR;
-SET &&NXFYBEG = '1001' | &FULLYR;
-SET &&NXFYEND = '0930' | &NXTYR;
-*
-SET &&NEXTYR= &MON1 | &DAY1NX | &NXTYR;
-SET &&NEXTYR5= &MON1 | &DAY1NX5 | &NXTYR5;
-SET &&NEXTYR10= &MON1 | &DAY1NX10 | &NXTYR10;
-***
-*
-SET &&CQTR = DECODE &MON1 ('01' '01' '02' '01' '03' '01'
- '04' '02' '05' '02' '06' '02'
- '07' '03' '08' '03' '09' '03'
- '10' '04' '11' '04' '12' '04');
-SET &CQBEG1 = DECODE &&CQTR ('01' '0101' '02' '0401'
- '03' '0701' '04' '1001');
-SET &&CQBEG = &CQBEG1 | &FULLYR;
-SET &CQEND1 = DECODE &&CQTR ('01' '0331' '02' '0630'
- '03' '0930' '04' '1231');
-SET &&CQEND = &CQEND1 | &FULLYR;

-*
-SET &&PQTR = DECODE &MON1 ('01' '04' '02' '04' '03' '04'
- '04' '01' '05' '01' '06' '01'
- '07' '02' '08' '02' '09' '02'
- '10' '03' '11' '03' '12' '03');
-SET &PQBEG1 = DECODE &&PQTR ('01' '0101' '02' '0401'
- '03' '0701' '04' '1001');
-SET &PQEND1 = DECODE &&PQTR ('01' '0331' '02' '0630'
- '03' '0930' '04' '1231');
-SET &PREVYR = IF &&PQTR IS '04' THEN &LASTYR ELSE &FULLYR;
-SET &&PQBEG = &PQBEG1 | &PREVYR;
-SET &&PQEND = &PQEND1 | &PREVYR;
-*
-*
-SET &&PQTR2 = DECODE &MON1 ('01' '03' '02' '03' '03' '03'
- '04' '04' '05' '04' '06' '04'
- '07' '01' '08' '01' '09' '01'
- '10' '02' '11' '02' '12' '02');
-SET &PQBEG2A= DECODE &&PQTR2 ('01' '0101' '02' '0401'
- '03' '0701' '04' '1001');
-SET &PQEND2A= DECODE &&PQTR2 ('01' '0331' '02' '0630'
- '03' '0930' '04' '1231');
-SET &PREVYR2 = IF &&PQTR2 IS '04' THEN &LASTYR ELSE
- IF &&PQTR IS '04' THEN &LASTYR ELSE
- &FULLYR;
-SET &&PQBEG2 = &PQBEG2A | &PREVYR2;
-SET &&PQEND2 = &PQEND2A | &PREVYR2;
-*
-*
-*
-SET &&PQTR3 = DECODE &MON1 ('01' '02' '02' '02' '03' '02'
- '04' '03' '05' '03' '06' '03'
- '07' '04' '08' '04' '09' '04'
- '10' '01' '11' '01' '12' '01');
-SET &PQBEG3A = DECODE &&PQTR3 ('01' '0101' '02' '0401'
- '03' '0701' '04' '1001');
-SET &PQEND3A = DECODE &&PQTR3 ('01' '0331' '02' '0630'
- '03' '0930' '04' '1231');
-SET &PREVYR3 = IF &&PQTR3 IS '04' THEN &LASTYR ELSE
- IF &&PQTR2 IS '04' THEN &LASTYR ELSE
- IF &&PQTR IS '04' THEN &LASTYR ELSE
- &FULLYR;
-SET &&PQBEG3 = &PQBEG3A | &PREVYR3;
-SET &&PQEND3 = &PQEND3A | &PREVYR3;
-*
-*
-SET &&PQTR4 = DECODE &MON1 ('01' '01' '02' '01' '03' '01'
- '04' '02' '05' '02' '06' '02'
- '07' '03' '08' '03' '09' '03'
- '10' '04' '11' '04' '12' '04');
-SET &PQBEG4A = DECODE &&PQTR4 ('01' '0101' '02' '0401'
- '03' '0701' '04' '1001');
-SET &PQEND4A = DECODE &&PQTR4 ('01' '0331' '02' '0630'
- '03' '0930' '04' '1231');
-SET &PREVYR4 = IF &&PQTR4 IS '04' THEN &LASTYR ELSE
- IF &&PQTR3 IS '04' THEN &LASTYR ELSE
- IF &&PQTR2 IS '04' THEN &LASTYR ELSE
- IF &&PQTR IS '04' THEN &LASTYR ELSE
- &FULLYR;
-SET &&PQBEG4 = &PQBEG4A | &PREVYR4;
-SET &&PQEND4 = &PQEND4A | &PREVYR4;
-*
-*
-*
-SET &&PQTR5 = DECODE &MON1 ('01' '04' '02' '04' '03' '04'
- '04' '01' '05' '01' '06' '01'
- '07' '02' '08' '02' '09' '02'
- '10' '03' '11' '03' '12' '03');
-SET &PQBEG5A = DECODE &&PQTR5 ('01' '0101' '02' '0401'
- '03' '0701' '04' '1001');
-SET &PQEND5A = DECODE &&PQTR5 ('01' '0331' '02' '0630'
- '03' '0930' '04' '1231');
-SET &PREVYR5 = IF &&PQTR5 IS '04' THEN &LASTYR2 ELSE &LASTYR;
-SET &&PQBEG5 = &PQBEG5A | &PREVYR5;
-SET &&PQEND5 = &PQEND5A | &PREVYR5;
-*
-*
-*

Nice complicated code lolu , but this is specific for your case. And it seems that your fiscal year starts on Oct 1st.

Things that I denoted :
1- If you would had used &YYMD instead of &YMD you shouldn't have to bother about century assignation.
2- IBI functions do exist to subtract years, months and days from a date. So you may not need to split in each element (DD, MM, YY) and re-construct the dates as you did.
3- Your sample as is will not work since some variable are not defined such as &DAY1NX and &NXTYR, ...

quote:

Originally posted by Dave:
How's this? ( easy maintenance / low-code solution )

-PROMPT &Q1.(,).Q1;
-PROMPT &Q2.(,).Q2;
-PROMPT &Q3.(,).Q3;
-PROMPT &Q4.(,).Q4;

TABLE FILE data
...
WHERE
( DATE GE q1_start AND DATE LE q1_end AND '&Q1' EQ 'yes' )
OR
( DATE GE q2_start AND DATE LE q2_end AND '&Q2' EQ 'yes' )
OR
( DATE GE q3_start AND DATE LE q3_end AND '&Q3' EQ 'yes' )
OR
( DATE GE q4_start AND DATE LE q4_end AND '&Q4' EQ 'yes' )
END

Dave,

That's a great solution.

As a test I utilized that validation table I had mentioned in my earlier post, created a procedure with a -READFILE to capture the quarter start and end dates as parameters (based on a parameter where the user is prompted to enter the Fiscal Year). I then used a -INCLUDE to bring in those values into my new procedure, added your -PROMPTS:

  
-PROMPT &Q1.(YES,NO).Q1;
-PROMPT &Q2.(YES,NO).Q2;
-PROMPT &Q3.(YES,NO).Q3;
-PROMPT &Q4.(YES,NO).Q4;

then added my TABLE file & other criteria along with the OR's for the Quarters in the WHERE statement:

( DATE_FIELD GE &Q1_START AND DATE LE &Q1_END AND '&Q1' EQ 'YES' )
OR
( DATE_FIELD GE &Q2_START AND DATE LE &Q2_END AND '&Q2' EQ 'YES' )
OR
( DATE_FIELD GE &Q3_START AND DATE LE &Q3_END AND '&Q3' EQ 'YES' )
OR
( DATE_FIELD GE &Q4_START AND DATE LE &Q4_END AND '&Q4' EQ 'YES' )

In this case, the user is prompted for only the Fiscal year and to select which quarters they desire. This does make for a pretty dynamic/easy to maintain solution. I'll forward my code to Trudy as we utilize the same DB and Fiscal Year schedule.

Hi Lolu

Welcome to Focal Point.. I love that your first post is trying to help someone else! This forum will be a great place for you! Keep it up!

Thanks,

Glad to be of assistence :-)

I use "fake" WHERE statements for all kind of stuff :-)
also to disable or enable stuff, sometime a life-saver.

WHERE
 YEAR EQ &PARAM AND &PARAMTYPE EQ 'year'
OR
 WEEK EQ &PARAM AND &PARAMTYPE EQ 'week'