10 Replies Latest reply: Apr 15, 2013 10:59 AM by Stan Krajewski RSS

    How to count # of Mondays, Tuesdays etc in any given calendar month.

      Is there an easy way to count up the number of each specific named day in a calendar month?  I need to be able to multiply certain numbers by say the number of wednesdays in a month..


      Thanks,


      Stan

        • Re: How to count # of Mondays, Tuesdays etc in any given calendar month.
          Michael Solomovich

          Stan,

           

          This will work in the script, assuming you have table Calendar with Month, Year, and Date fields:

           

          JOIN (Calendar) LOAD
          Month,
          Year,
          sum(if(weekday(Date)=0,1,0) as "# Mondays",
          sum(if(weekday(Date)=1,1,0) as "# Tuesdays",
          ...
          sum(if(weekday(Date)=6,1,0) as "# Sundays"
          RESIDENT Calendar
          GROUP BY Month,Year;

           

          Regards,
          Michael

            • Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

              Hi Michael,

               

              First off, thanks for the response.

               

              I actually don't have a standalone calendar like that.  I'm loading a calendar out of my data warehouse as follows:

               

               

              SurgeryStartDate:

              replace LOAD

               

              date("Surgery Date",'MM/DD/YYYY') as "Surgery Date",
              num("Surgery Start Date CY Year") as "Surgery Start Date CY Year",
              "Surgery Start Date CY Qtr",
              "Surgery Start Date CY Month",
              num("Surgery Start Date FY Year") as "Surgery Start Date FY Year",
              "Surgery Start Date FY Qtr",
              "Surgery Start Date FY Month",
              "Surgery Start Weekday",
              "Surgery Start Month"
              where exists ([Surgery Date])
              ;
              SQL

              SELECT
              D.FULL_DT AS "Surgery Date",
              D.C_YYYY AS "Surgery Start Date CY Year",
              SUBSTR(D.CY_YYYY_QTR,3,8) AS "Surgery Start Date CY Qtr",
              SUBSTR(D.CY_YYYY_MM_NM,3,9) AS "Surgery Start Date CY Month",
              D.F_YYYY AS "Surgery Start Date FY Year",
              SUBSTR(D.FY_YYYY_QTR,3,8) AS "Surgery Start Date FY Qtr",
              SUBSTR(D.FY_YYYY_MM_NM,3,9) AS "Surgery Start Date FY Month",
              SUBSTR(D.DAY_NM,0,3) AS "Surgery Start Weekday",
              SUBSTR(D.MONTH_NM,0,3) AS "Surgery Start Month"
              FROM CDW.MASTER_DATE D
              JOIN (SELECT MIN(CONTACT_DT_KEY) AS MIN_DT ,MAX(CONTACT_DT_KEY) AS MAX_DT FROM CDW.VISIT WHERE CONTACT_DT_KEY >= 20090701 AND CONTACT_DT_KEY <> 99991231) X1
              ON D.DT_KEY BETWEEN X1.MIN_DT AND X1.MAX_DT;

               

              So, being a total rookie to this, how would I incorporate your load statement in to that.  I can't just call the SurgeryStartdate that I'm using for my load statement can I?

            • Re: How to count # of Mondays, Tuesdays etc in any given calendar month.
              Yusuf Ali

              Hi ,

                     Can you try the below code.

              Here 'Date' is the Date field which can be any Date so that you can get the Number of Mondays in that month .

               

              If you want to calculate the Tue ,Wed ,Thu , Fri , Sat or Sun

              just past instead of  'Mon' in the below code 

               

                Floor((MonthEnd (Date) - MonthStart(Date) )/7 ) +if(WeekDay( WeekStart(MonthEnd(Date)))='Mon' ,1,0)

               

              If there is anything please let me know .

                • Re: How to count # of Mondays, Tuesdays etc in any given calendar month.

                  That was a good stab, but its not calculating correctly.  I set a variable to equal addmonths(today),-1) and save that to vPriorMonth, which for today's date saves 3/12/13 (since today is 4/12/13).

                   

                  From there I applied your formula as is, substituting my variable for the Date and it counted that March of 2013 had 5 Mondays, and if set to 'Fri' it says there are 4 Fridays.  When looking at a physical calendar its 4 mondays, 5 fridays.  Perhaps its not liking the functions in the variable to force it to prior month?


                  April 2013    = 4 Sun, 5 Mon, 5 Tue, 4 Wed, 4 Thu, 4 Fri, 4 Sat

                  March 2013 = 5 Sun, 4 Mon, 4 Tue, 4 Wed, 4 Thu, 5 Fri, 5 Sat

                   

                  It's like so close and easy to drop right in to variables... just math is slightly off unless I missed something.

                   

                  I even went as far as throwing the functions of my variable in to the full function:

                  =floor((monthend(addmonths(today(), -1))-monthstart(addmonths(today(), -1)))/7)+if(weekday(weekstart(monthend(addmonths(today

                  (), -1))))='Fri', 1, 0)