[SOLVED] Calculate How many days monday-saturday in a month
Hi all : I need to calculate week days (Mon-Sat) in a given month.. I need to do this is in a Define.. I have a YearMonth field for that I need toi find out weekdays (mon-sat) OR find how many Sundays in that month and then subtract # of Sundays from the Last Day of the Month . I looked around and found the following code, does not exactly gives me the answer I need, anyone dealt with this issue, please let me know Thanks
This message has been edited. Last edited by: <Kathryn Henning>,
Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 ) SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2 WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
October 03, 2014, 11:11 AM
MartinY
Here something that I have to give you a starting point :
DEFINE FILE BUSINESS_CALENDAR
WDAY /A3 = DOWK(CAL_DTE, WDAY);
NB_SUN /D2 = 1;
END
TABLE FILE BUSINESS_CALENDAR
SUM NB_SUN
BY ACC_MTH_NO
WHERE ACC_YR EQ 2014;
WHERE ACC_MTH_NO GE 01;
WHERE ACC_MTH_NO LE 12;
WHERE WDAY EQ 'SUN';
ON ACC_MTH_NO SUBFOOT
ON TABLE COLUMN-TOTAL AS 'Total'
END
This message has been edited. Last edited by: MartinY,
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
October 03, 2014, 11:49 AM
j.gross
If you are unconcerned with excluding holidays, here's the approach I would take:
Sunday_before/YYMD= [Sunday at the beginning of the (Sunday through Saturday) calendar week of the start date.]*
Sunday_after/YYMD = [Sunday following the end of the calendar week of the end date.]*
gross_count/I4=6*INT((Sunday_after - Sunday_before)/7);
adjust_before/I1=[number of Mon-Sat days preceeding start date within its calendar week}*
adjust_after/I1= [number of Mon-Sat days following end date within its calendar week]*
net_count/I4 = gross_count - (adjust_befpore + adjust_after);
Once the start and end dates are converted to YYMD ("smart date") format, the day-of-week of each can be obtains simply by casting from format YYMD to format W, and the number-of-days adjustments for calculating the starred items can be computed using DECODE.
October 03, 2014, 12:49 PM
FOCdeveloper
Thank you Martin , I tried your method, but I get only one Count Sunday.. My reporting file has one row Year1, Year2, Year3,. Mon1, Mon2, Mon3...
-* Find Sundays in a Month
GL_DTE3X/A6=EDIT(GLRE_ACT_YR3,'$$99')|GLRE_ACCT_MTH3|'01';
GL_DTE3Y/I6=EDIT(GL_DTE3X);
GL_DTE3/I6YMD=GL_DTE3Y;
WDAY /A3 = DOWK(GL_DTE3, WDAY);
NB_SUN /D2 = 1;
END
-RUN
TABLE FILE ITREND3
SUM GL_DTE3X GLRE_ACT_YR3 GLRE_ACCT_MTH3
GL_DTE3Y GL_DTE3 WDAY
NB_SUN
BY GL_DTE3
-*WHERE RPT_YR EQ 2014;
-*WHERE RPT_MO GE 01;
-*WHERE RPT_MO LE 12;
WHERE WDAY EQ 'SUN';
ON GL_DTE3 SUBFOOT
" "
ON TABLE COLUMN-TOTAL AS 'Total'
END
-RUN
-EXIT
Result
I printed all to see whats going on
GL_DTE3 GL_DTE3X GLRE_ACT_YR3 GLRE_ACCT_MTH3 GL_DTE3Y GL_DTE3 WDAY NB_SUN
12/12/01 121201 2012 12 121201 12/12/01 SUN 1
Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 ) SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2 WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
October 03, 2014, 01:05 PM
MartinY
You need to process the count by month, if you count it by a date it will only gives you 1 Sunday.
Try first to do
TABLE FILE ITREND3
PRINT GL_DTE3X
GLRE_ACT_YR3
GLRE_ACCT_MTH3
GL_DTE3Y
WDAY
BY GL_DTE3
END
Just to validate that your DEFINES are good.
It certainly not good because appending 01 as the day will generates every months with only one day. So you won't have all the days of the month and you need all of them to count number of Sunday.
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
October 03, 2014, 02:07 PM
FOCdeveloper
Yes I already did print the DEFINES and they are working..before I posted Since I have only one row where months are in 12 buckets Mon1. Mon2...Mon12 Yr1, Yr2. Yr3..Yr12
In your sample above what is defined CAL_DTE, DAY_X as? my source flds are Yr and Mon Thanks
Thanks
Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 ) SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2 WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
October 03, 2014, 03:30 PM
MartinY
CAL_DTE is a date field from the source table.
To count how many Sunday you have in a month, you need all days from that month! Not only a YearMonth field.
How will I know how many Sunday I have if I don't know which days falls a Sunday ? For what I understand, you don't so you'll have to create a "calendar" such as:
-* BELOW VARIABLE SHOULD BE ASSIGNED TO YOUR YEAR/MONTH FIELD
-SET &YEAR = 2014;
-SET &MONTH = 9;
-SET &I = 0;
-SET &NBSUNDAY = 0;
-* NOT PROCESSING FOR LEAP YEAR, MUST DO A TEST ON YEAR FOR IT
-SET &NBMTHDAY = DECODE &MONTH (1 31 2 28 3 31 4 30 5 31 6 30 7 31 8 31 9 30 10 31 11 30 12 31);
-LOOP
-SET &I = &I + 1;
-SET &DTE&I.EVAL = (&YEAR * 10000) + (&MONTH * 100) + &I;
-SET &DAY = DOWK(&DTE&I.EVAL, 'A3');
-SET &NBSUNDAY = IF &DAY EQ 'SUN' THEN &NBSUNDAY + 1 ELSE &NBSUNDAY;
-IF &I LT &NBMTHDAY THEN GOTO LOOP;
-TYPE NB_SUNDAY: &NBSUNDAY
This gives you the number of Sunday in September 2014.
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
October 03, 2014, 04:57 PM
Todd_Wallace
Can't this be accomplished by using the business days function with a DATEDIF()?
SET BUSDAYS = _MTWTFS
In a define set
Days_Without_Sunday/I6=DATEDIF(<Begin Date>,<End Date>, 'BD');
WebFOCUS 8.1.05 Windows-iSeries DB2, All Outputs HTML
October 03, 2014, 05:48 PM
Edward Wolfgram
Try:
-DEFAULTS &YYM=201410
DEFINE FILE CAR
-*MYMON/YYM : target year-month
-*MYFST/YYMD : date of 1st day of month
-*MYFSTWD/I1 : weekday of 1st day of month (monday=0)
-*FSTMON/YYMD : date of 1st monday of month
-*MYNEXT/YYM : year-month after target month
-*MYNXFST/YYMD : date of 1st day of next month
-*MYNFSTWD/I1 : weekday of 1st day of next month
-*LSTMON/YYMD : date of last monday of month
-*WEEKS/I1 : number of full weeks between 1st and last mondays
-*LDAYS/I1 : number of weekdays in week before 1st monday
-*FDAYS/I1 : number of weekdays in week after last monday
CTR/I3 WITH BODYTYPE = CTR+1 ;
MYMON/YYM WITH BODYTYPE = '&YYM' ;
MYMON/YYM = MYMON-12+CTR ;
MYFST/YYMD = MYMON ;
MYFSTWD/I1 = MYFST - ((INT(MYFST/7))*7) ;
FSTMON/YYMD = MYFST + DECODE MYFSTWD(0 0 1 6 2 5 3 4 4 3 5 2 6 1) ;
MYNEXT/YYM = MYMON + 1 ;
MYNXFST/YYMD = MYNEXT ;
MYNFSTWD/I1 = MYNXFST - ((INT(MYNXFST/7))*7) ;
LSTMON/YYMD = MYNXFST - DECODE MYNFSTWD(0 7 1 1 2 2 3 3 4 4 5 5 6 6) ;
WEEKS/I1 = (LSTMON-FSTMON)/7 ;
LDAYS/I1 = MYNXFST - LSTMON ;
LDAYS/I1 = IF LDAYS GE 5 THEN 5 ELSE LDAYS ;
FDAYS/I1 = (FSTMON - MYFST)-2 ;
FDAYS/I1 = IF FDAYS LT 0 THEN 0 ELSE FDAYS ;
DAYSINMON/I2 = WEEKS*5 + FDAYS + LDAYS ;
END
TABLE FILE CAR
PRINT MYMON DAYSINMON
END
-RUN
It seems to work for this year and the first few months of next...
IBI Development
October 04, 2014, 03:04 AM
Alan B
That's nice Todd, It is important to get the correct end date:
SET BUSDAYS = _MTWTFS
DEFINE FUNCTION WD_MONTH (year/I4,month/I2)
this_iyym/I6YYM = (year*100)+month;
bom_yymd/YYMD = this_iyym;
WD_MONTH/I2 = DATEDIF(bom_yymd,DATEMOV(bom_yymd,'EOM')+1,'BD');
END
seems to work okay when year and month are sent as integers.
Alan. WF 7.705/8.007
October 06, 2014, 11:06 AM
FOCdeveloper
Thank you all.. All the above worked .. but I picked Alan's approach as it takes less code.. I have 12 of these buckets, I have to do it for.. See below
Thanks everyone
here is the code..
SET BUSDAYS = _MTWTFS
..
..
-* Find mon-sat days in a month - (6 days a week)
DEFINE FUNCTION WD_MONTH (year/I4,month/I2)
this_iyym/I6YYM = (year*100)+month;
bom_yymd/YYMD = this_iyym;
WD_MONTH/I2 = DATEDIF(bom_yymd,DATEMOV(bom_yymd,'EOM')+1,'BD');
END
-RUN
..
..
DEFINE FILE TREND3
-* Find weekdays mon-sat by month
GLRE_ACTYR1/I4 =EDIT(GLRE_ACT_YR1);
GLRE_ACTMTH1/I2=EDIT(GLRE_ACCT_MTH1);
WEEKDAYS1/I2 = WD_MONTH(GLRE_ACTYR1,GLRE_ACTMTH1);
-*
GLRE_ACTYR2/I4 =EDIT(GLRE_ACT_YR2);
GLRE_ACTMTH2/I2=EDIT(GLRE_ACCT_MTH2);
WEEKDAYS2/I2= WD_MONTH(GLRE_ACTYR2,GLRE_ACTMTH2);
-*
GLRE_ACTYR3/I4 =EDIT(GLRE_ACT_YR3);
GLRE_ACTMTH3/I2=EDIT(GLRE_ACCT_MTH3);
WEEKDAYS3/I2 = WD_MONTH(GLRE_ACTYR3,GLRE_ACTMTH3);
..
..
END
-RUN
TF..
Results
YR1 MTH1 WDAYS1 YR2 MTH2 WDAYS2 YR3 MTH3 WDAYS3
2012 10 27 2012 11 26 2012 12 26
Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 ) SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2 WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7
October 06, 2014, 11:09 AM
j.gross
My suggestion above (for a closed form definition of days, excluding Sundays, in a given month), fleshed out.
TABLE FILE CAR LIST MODEL IF RECORDLIMIT EQ 14 ON TABLE HOLD END DEFINE FILE HOLD AMONTH/A6YYM = DECODE LIST ( 1 '201409' 2 '201410' 3 '201411' 4 '201412' 5 '201501' 6 '201502' 7 '201503' 8 '201504' 9 '201505' 10 '201506' 11 '201507' 12 '201508' 13 '201509' 14 '201510' ELSE ''); MONTH/YYM=AMONTH;
END TABLE FILE HOLD PRINT MONTH FST LST IFST ILST WEEKDAYS BY LIST END
MONTH is the month, in smart-date format
FST is first date in the month. (Casting a YYM as YYMD yields the first of the month) NXMONTH is the following month NXT is its start date LST is the last date of the given month
WFST is the day-of-week of FST. (WF convention for the internal value is Mon=1 thru Sun=7) WLST - ditto for LST IFST is weekday index (0=Sun to 6=Sat) of FST ILST - ditto for LST
SUN1 is date of the last Sunday on or before the 1st of the given month SUN2 is date of first Sunday following the end of the given month DAYS is the desired result.This message has been edited. Last edited by: j.gross,
- Jack Gross WF through 8.1.05
October 07, 2014, 04:58 AM
Alan B
If the values are Alpha, change the function to accept those, less code needed:
SET BUSDAYS = _MTWTFS
DEFINE FUNCTION WD_MONTH (year/A4,month/A2)
this_iyym/I6YYM = (EDIT(year)*100)+EDIT(month);
bom_yymd/YYMD = this_iyym;
WD_MONTH/I2 = DATEDIF(bom_yymd,DATEMOV(bom_yymd,'EOM')+1,'BD');
END
Alan. WF 7.705/8.007
October 08, 2014, 10:14 AM
FOCdeveloper
Thanks Jack, Alan and others.. Issue resolved.
Prod/Dev/Test: WF 8.1.5 on (Windows Server 2012 R2 ) SandBox: WebFocus Server 8.1.5 on Windows Server 2008 R2 WebFOCUS App Studio 8.1.5 and Developer Studio 8.1.5 on Windows 7