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.
