January 7, 2013 at 9:03 am
I had been searching for a query to calculate number of weekdays per month. It was not successful on google search.
Holidays can be included. All I need is total of Monday thru Friday.
We need it to go back to 15 full months. So, this is January 2013 and it is not the end of the month. We will not include January 2013.
It will be a rolling 15 months. (When February 2013 starts, it will be from November 2011 - January 2013...)
Data needed will be from October 2011 - December 2012.
The output will be like`:
Month-Year, Total_Days
October 2011, 22
November 2011, 23
December 2011, 22
.......
I think you get an idea... No Saturday and Sunday.
I am not sure whether to use anything like SUM (over partition by...) in order to get the result.
Thanks.
January 7, 2013 at 9:13 am
I make it really easy on myself and keep a Calendar table in a database called Common, on each server.
The table has each day as a row. Columns include the date, the day of the week, fiscal period, and whether it's a workday or not. Also split out the date components (year, month, week, day) into separate columns, and index those.
So, need the workdays per month?
SELECT [Year], [Month], COUNT(*) AS WorkDays
FROM Common.dbo.Calendar
WHERE Workday = 1
AND [Date] >= @StartDate AND [Date] <= @EndDate
GROUP BY [Year], [Month]
ORDER BY [Year], [Month];
It's easy, it's fast, it's accurate, it's customizable (easily).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 7, 2013 at 9:17 am
+1
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply