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>,

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,

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.

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 
 

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.

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

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.

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');

 

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...

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.

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 
 

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,

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

Thanks Jack, Alan and others..
Issue resolved.