Focal Point
[SOLVED] Calculate How many days monday-saturday in a month

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/9007049276

October 03, 2014, 10:44 AM
FOCdeveloper
[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

 
DEFINE FUNCTION SCNDSAT/A8YYMD (SEEDDATE/A8YYMD)
 SEEDYYMD/YYMD = SEEDDATE ;
 CALC1/YYMD = DATEMOV(SEEDYYMD, 'BOM') ;
 DOW/W = CALC1 ;
FIX/I1 = IF DOW EQ 7 THEN 7 ELSE 0 ;
 CALC2/YYMD = CALC1  + (6 - DOW) + 7 + FIX ;
 SCNDSAT/A8YYMD = CALC2;
END

-RUN

-SET &TESTDATE = '20141001' ;
-SET &RES = SCNDSAT(&TESTDATE) ;
-SET &DAYOFWK = DOWKL(&RES, 'A12');
-TYPE &TESTDATE - &RES
-TYPE DAY OF WEEK &DAYOFWK 

 

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


Resulting in
ACC_MTH_NO NB_SUN 
1          4 
2          4 
3          5 
4          4 
5          4 
6          5 
7          4 
8          5 
9          4 
10         4 
11         5 
12         4 
Total     52 

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;
FST/YYMDWt=MONTH;
NXMONTH/YYM=MONTH+1;
NXT/YYMDWt=NXMONTH;
LST/YYMDWt=NXT-1;
WFST/W=FST;
WLST/W=LST;
IFST/I1=IMOD(WFST,7,'I1');
ILST/I1=IMOD(WLST,7,'I1');
SUN1/YYMDW=FST-IFST;
SUN2/YYMDW=LST+7-ILST;
DAYS/I3=INT((SUN2-SUN1)/7)*6 - ( MAX(0,IFST-1) + (6-ILST) );

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