What is the quickest/ most effective way in SQL Server of getting the number of working days in a Month (defining the number of working days as being the days monday-friday)
What is the quickest/ most effective way in SQL Server of getting the number of working days in a Month (defining the number of working days as being the days monday-friday)
All months always has exactly 20 workdays for the first 28 days. You only have to check day 29, 30 and 31 for being workdays. This code is regional and language independant.
DECLARE @theDate DATETIME SET @theDate = GETDATE() SELECT 20 + COUNT(*) FROM ( SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 28) AS theDate UNION SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 29) UNION SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @theDate), 30) ) AS d WHERE DATEPART(DAY, theDate) > 28 AND DATEDIFF(DAY, 0, theDate) % 7 < 5
Here's a way:
SELECT TOP 365 IDENTITY(INT, 1, 1) AS n INTO #nums FROM sys.all_objects SELECT -- this gets the first day of the month so we have something to group on DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, n, '1/1/2008')), 0) AS month_Start, COUNT(*) FROM #nums WHERE -- this gets only weekdays. DATEPART(weekday, DATEADD(DAY, n, '1/1/2008')) BETWEEN CASE @@DateFirst WHEN 7 THEN 2 WHEN 6 THEN 3 WHEN 5 THEN 4 WHEN 4 THEN 5 WHEN 3 THEN 6 WHEN 2 THEN 7 ELSE 1 END AND CASE @@DateFirst WHEN 7 THEN 6 WHEN 6 THEN 7 WHEN 5 THEN 1 WHEN 4 THEN 2 WHEN 3 THEN 3 WHEN 2 THEN 4 ELSE 5 END GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, n, '1/1/2008')), 0) ORDER BY month_start DROP TABLE #nums
You can read this post on my blog to see why I am using @@DateFirst. Obviously this does not take holidays into account. You'd need a calendar table for that.
Another way is to use a numbers table and a little date math.
DECLARE @dt DATETIME SET @dt = '2009-09-22' SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0) AS Mnth, SUM(CASE WHEN DATENAME(WEEKDAY,DATEADD(DAY,Number,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0))) IN('Monday','Tuesday','Wednesday','Thursday','Friday') THEN 1 ELSE 0 END) AS Cnt FROM [master].dbo.[spt_values] WHERE type = 'P' AND DATEADD(DAY,Number,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)) < DATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0))
2 People are following this question.