SQL Query - Calculate the number of weekdays in a given month

By steve, 10 June, 2016

The following SQL will calculate the number of workdays in a given month:

WITH t as 
	(
	select 1 x
	union all 
	select x + 1
	from t
	where x < day(dateadd(day, -1, dateadd(month, 1, datefromparts([year], [month], 1))))
	)
SELECT COUNT(x) NumWeekDays from t 
whereDATEPART(dw, datefromparts([year], [month], x)) not in (1,7)

And for Connectwise, the following would calculate the number of work days in a given month:


WITH t as 
	(
	select 1 x
	union all 
	select x + 1
	from t
	where x < day(dateadd(day, -1, dateadd(month, 1, datefromparts([year], [month], 1))))
	)
SELECT COUNT(x) - (SELECT COUNT(Holiday_List_RecID) FROM Holiday WHERE Year(Holiday_Date)=[year] AND Month(Holiday_Date)=[month]) NumWorkDays from t 
where DATEPART(dw, datefromparts([year], [month], x)) not in (1,7)

Tags

Comments