This will work in the script, assuming you have table Calendar with Month, Year, and Date fields:
JOIN (Calendar) LOAD
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"
GROUP BY Month,Year;
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:
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])
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?
You might have less Surgery Dates than calendar dates, so just using the SurgeryStartDate table might miss some weekdays when doing the weekday aggregation in the join.
I think you should prepare a master calendar (there are hundreds of threads, design blogs here in the forum etc how to do this) based on your surgery date field, which contains all calendar dates. Then use this master calendar to create your number-of-weekdays-in-a-month field like suggested above.
You can also change the logic a little to perform a GROUP BY Year, Month, Weekday and do a count of weekdays and set the result into a common field (WeekdaysInYearMonth or similar) which could make it easier to retrieve the number afterwards.
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 .
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)
First use this Set statement in your Script
Then you the below code .
Floor((MonthEnd (Today()) - MonthStart(Today()) )/7 ) +
if( num( WeekDay(MonthStart(Today())))+1<= Match('Mon', $(=chr(39) & Replace(DayNames, ';', chr(39) & ',' & chr(39)) & chr(39))) and Match('Mon', $(=chr(39) & Replace(DayNames, ';', chr(39) & ',' & chr(39)) & chr(39))) <=num( WeekDay(MonthEnd(Today())))+1 ,1,0)
So if you want to count Tuesday then Replace 'Mon' by 'Tue' at 2 places as you can see above .
Use only strings among these Mon,Tue,Wed,Thu,Fri,Sat,Sun
Please reply me for the same .