How do I get count of weekend days from a range of dates

Question

I have to find out total number of saturday and sunday between Start Date & End Date.

Example #1:

StartDate = Getdate(), EndDate = GetDate() + 5      -- result should be 2.

Example #2:

StartDate = Getdate(), EndDate = GetDate() + 10     -- result should be 4.

Can anyone suggest please.

Answer 1

Here it is

DECLARE @STARTDATE DATE='01/JAN/2014'    
DECLARE @ENDDATE DATE='01/MAR/2014'

;WITH  CTE as
(
    SELECT  CAST(@STARTDATE AS DATE) as [DAYS] 
    UNION ALL
    SELECT DATEADD(DAY,1,[DAYS]) [DAYS]
    FROM    CTE
    WHERE   [DAYS] < CAST(@ENDDATE AS DATE)
)
SELECT DISTINCT COUNT([DAYS]) OVER(PARTITION BY DATENAME(WEEKDAY,[DAYS])) CNT,
DATENAME(WEEKDAY,[DAYS]) WD
FROM CTE 
WHERE DATENAME(WEEKDAY,[DAYS]) = 'SATURDAY' OR DATENAME(WEEKDAY,[DAYS]) = 'SUNDAY'
ORDER BY DATENAME(WEEKDAY,[DAYS]) 

• SQL FIDDLE

Here is your result