Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


T-SQL Query | [How Many Work/Week Days In Date Range Puzzle]

The puzzle is very simple. You have to calculate how many week days existed in an arbitrary range of dates today. We have count no of days between the dates but Saturday’s and Sunday’s. Please check out the sample input and expected output for details.

Sample Input

For example date range is-

Start Date – ’10/1/2008′
End Date – ’10/31/2008′

Expected output

WeekDaysinGivenPeriod
23

Rules/Restrictions
• The solution should be should use “SELECT” statement or “CTE”.
• Do not use Loop.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

UPDATE – 11-Apr-2015 – Solution 1


DECLARE @st AS DATETIME = '10/1/2008'
DECLARE @Et AS DATETIME = '10/31/2008'

;WITH CTE AS
(
     SELECT @st Dts , DATEPART(weekday,@st) wk
     UNION ALL
     SELECT DATEADD(d,1,Dts) ,  DATEPART(weekday, DATEADD(d,1,Dts)) wk  FROM CTE
     WHERE DATEADD(d,1,Dts) <= @Et
)
SELECT COUNT(1) WeekDaysinGivenPeriod FROM CTE WHERE wk <> 1 AND wk <> 7

--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com