SET DATEFIRST 1
DECLARE
@start_date DATETIME,
@end_date DATETIME
SET
@start_date = DATEADD(MONTH,-1,CONVERT(DATETIME,CONVERT(VARCHAR(4),YEAR(GETDATE()))+'-'+CONVERT(VARCHAR(4),MONTH(GETDATE()))+'-24'))
SET @end_date = CONVERT(DATETIME,CONVERT(VARCHAR(4),YEAR(GETDATE()))+'-'+CONVERT(VARCHAR(4),MONTH(GETDATE()))+'-23')
;WITH
Days_Of_The_Week AS (
SELECT 1 AS day_number, 'Monday' AS day_name UNION ALL
SELECT 2 AS day_number, 'Tuesday' AS day_name UNION ALL
SELECT 3 AS day_number, 'Wednesday' AS
day_name UNION ALL
SELECT 4 AS day_number, 'Thursday' AS day_name
UNION ALL
SELECT 5 AS day_number, 'Friday' AS day_name UNION ALL
SELECT 6 AS day_number, 'Saturday' AS day_name
UNION ALL
SELECT 7 AS day_number, 'Sunday' AS day_name
)
SELECT
day_name,
DATEDIFF(wk, @start_date, @end_date) -
CASE WHEN DATEPART(weekday, @start_date) > day_number THEN
1 ELSE 0 END -
CASE WHEN DATEPART(weekday, @end_date) <
day_number THEN 1 ELSE
0 END
FROM
Days_Of_The_Week
