Thursday, February 20, 2014

T-SQL Tips: Get Business Weeks For a Month

Recently in one of my reporting projects, we required generating a list of business weeks along with the start and end dates for a given month so as to consolidate the data and do some weekly calculations on it.  I created a UDF for the purpose of reusing the logic wherever we needed to generate business week dates. I'm sharing UDF here for others benefit.
In this case, week days  are from Monday to Friday. If you want to change it, you just need to change the logic as per below blog to change additive factor (highlighted in the UDF code)
http://visakhm.blogspot.in/2012/08/creating-server-independent-day.html

the UDF will look like below

CREATE FUNCTION GetBusinessWeeksForMonth
(
@dt datetime-- Pass any date value of month for which you need business week info
)
RETURNS @RESULTS TABLE
(
WeekNo int,
BusinessStart datetime,
BusinessEnd datetime
)
AS
BEGIN

;With Monthdates
AS
(
SELECT DATEADD(mm,DATEDIFF(mm,0,@Dt),0) AS dt
UNION ALL
SELECT Dt +1
FROM MonthDates
WHERE dt< DATEADD(mm,DATEDIFF(mm,0,@dt)+1,0)
)
INSERT @RESULTS
SELECT ROW_NUMBER() OVER (ORDER BY DATEDIFF(dd,0,dt)/7) AS WeekNo,
MIN(CASE WHEN DATEDIFF(dd,0,dt)% 7 < 5 THEN dt END) AS StartDt,
MAX(CASE WHEN DATEDIFF(dd,0,dt)% 7 < 5 THEN dt END) AS EndDt
FROM Monthdates
GROUP BY DATEDIFF(dd,0,dt)/7
HAVING SUM(CASE WHEN DATEDIFF(dd,0,dt)% 7 < 5 THEN 1 ELSE 0 END) > 0
RETURN
END

And it can be invoked as follows


As you see from output above it lists all business weeks in Feb 2014 with starting day as Monday and ending on Friday.
This will help anyone in scenarios like this where we need to do calculations based on business weeks.If you want you may even extend it to list all business weeks of year passed rather than the month. That part I leave it for readers. Feel free to comment if you want any more clarification or help on that.