Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Calculate How many days monday-saturday in a month

 Go Search Notify Tools
 [SOLVED] Calculate How many days monday-saturday in a month
Platinum Member
 posted October 03, 2014 10:44 AM
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

 Posts: 134 | Location: USA | Registered: August 21, 2008 IP
Virtuoso
 posted October 03, 2014 11:11 AM Hide Post
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
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

 Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013 IP
Virtuoso
 posted October 03, 2014 11:49 AM Hide Post
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]*
```

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.

 Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005 IP
Platinum Member
 posted October 03, 2014 12:49 PM Hide Post
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

 Posts: 134 | Location: USA | Registered: August 21, 2008 IP
Virtuoso
 posted October 03, 2014 01:05 PM Hide Post
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

 Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013 IP
Platinum Member
 posted October 03, 2014 02:07 PM Hide Post
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

 Posts: 134 | Location: USA | Registered: August 21, 2008 IP
Virtuoso
 posted October 03, 2014 03:30 PM Hide Post
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

 Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013 IP
Member
 posted October 03, 2014 04:57 PM Hide Post
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

 Posts: 29 | Location: Cincinnati, OH | Registered: August 23, 2011 IP
Gold member
 posted October 03, 2014 05:48 PM Hide Post
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

 Posts: 61 | Registered: November 15, 2005 IP
Virtuoso
 posted October 04, 2014 03:04 AM Hide Post
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

 Posts: 1451 | Location: Portugal | Registered: February 07, 2007 IP
Platinum Member
 posted October 06, 2014 11:06 AM Hide Post
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

 Posts: 134 | Location: USA | Registered: August 21, 2008 IP
Virtuoso
 posted October 06, 2014 11:09 AM Hide Post
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

 Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005 IP
Virtuoso
 posted October 07, 2014 04:58 AM Hide Post
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

 Posts: 1451 | Location: Portugal | Registered: February 07, 2007 IP
Platinum Member
 posted October 08, 2014 10:14 AM Hide Post
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

 Posts: 134 | Location: USA | Registered: August 21, 2008 IP