This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to find the total working days in given start/end date (Exclude all Saturdays & Sundays)
Input Date:
|
1 2 3 |
StartDate EndDate ---------- ---------- 2017-09-01 2017-09-30 |
Expected Output:
|
1 2 3 |
TotalWeekDays ------------- 21 |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @StartDate AS DATE = '2017-09-01' DECLARE @EndDate AS DATE = '2017-09-30' ;WITH CTE AS ( SELECT @StartDate Sdate , DATEPART(weekday,@StartDate) Weeks UNION ALL SELECT DATEADD(day,1,Sdate) , DATEPART(weekday, DATEADD(day,1,Sdate)) Weeks FROM CTE WHERE DATEADD(day,1,Sdate) <= @EndDate ) SELECT COUNT(1) TotalWeekDays FROM CTE WHERE Weeks <> 1 AND Weeks <> 7 |
Please try the different solution for this puzzle and share it via comment...
