In my earlier article, I posted a solution how to calculate weekdays between weekends. Today I came across a situation where I need to calculate the weekends between two dates and the criterion is that weekend can be any day (not necessary Sunday only) of the week.
Given below is the function that calculates the weekends between two dates including the dates provided as input parameter. In some parts of the world weekends are on ‘Fridays’, ‘Saturdays’ and in other parts ‘Saturdays’ and ‘Sundays’.
In this function, you need to pass weekend as a parameter.
CREATE FUNCTION dbo.GetWeekendDays ( @StartDate DATETIME, @EndDate DATETIME , @Weekend VARCHAR(50) ) RETURNS INT BEGIN DECLARE @Xml AS XML DECLARE @WEEKEND_DAYS AS INT SET @Xml =CAST(('<a>'+replace(@Weekend,',' ,'</a><a>')+'</a>') AS XML) ;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1), N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y), N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y), N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n) FROM N3 AS X, N3 AS Y) SELECT @WEEKEND_DAYS=SUM(1) FROM n4 WHERE DATEADD(day,n-1,@startdate)<=@Enddate AND DATENAME(dw,DateAdd(day,n-1,@startdate)) In ( SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A) ) RETURN @WEEKEND_DAYS END GO SELECT dbo.GetWeekendDays ('02/01/2013','02/28/2013','Saturday,Sunday') as [Weekend Days] GO SELECT dbo.GetWeekendDays ('02/01/2013','02/28/2013','Friday,Saturday') as [Weekend Days] --OUTPUT Weekend Days ------------ 8 (1 row(s) affected) Weekend Days ------------ 8 (1 row(s) affected)
Have a better solution ?
Leave a Reply