Given a date range, I’d like to return all of the Saturdays and Sundays that fall within that range, with these conditions:
- Include Saturdays only if their ordinal position is either the 1st or 3rd Saturday within the month they fall (not within the entire range).
- Include all Sundays, along with the ordinal position of that Sunday within the month it falls.
So for example, if the start date is Aug 15, 2021 and the end date is Sep 20, 2021, the output would be:
Dates Saturday Number (in its own month) ---------- --------------- 2021-08-21 3 2021-09-04 1 2021-09-18 3 Dates Sunday Number (in its own month) ---------- --------------- 2021-08-15 3 2021-08-22 4 2021-08-29 5 2021-09-05 1 2021-09-12 2 2021-09-19 3
Then I can take the date range in total (37 days), and subtract the Sundays (6), and the 1st and 3rd Saturdays from each month (3), to end at 28.
Tried this query
DECLARE @sd DATETIME = '2021-08-15' DECLARE @ed DATETIME = '2021-09-20' --find first saturday WHILE DATEPART(dw, @sd)<>7 BEGIN SET @sd = DATEADD(dd,1,@sd) END --get next saturdays ;WITH Saturdays AS ( --initial value SELECT @sd AS MyDate, 1 AS SatNo UNION ALL --recursive part SELECT DATEADD(dd,7,MyDate) AS MyDate, CASE WHEN SatNo + 1 =6 THEN 1 ELSE SatNo+1 END AS SatNo FROM Saturdays WHERE DATEADD(dd,7,MyDate)<=@ed ) SELECT * FROM Saturdays WHERE SatNo IN (1,3) OPTION(MAXRECURSION 0)
it does not work properly.
Also Tried this solution Get number of weekends between two dates in SQL for calculate week days, but I want only 1st and 3 Saturday and all Sundays
Advertisement
Answer
Get a calendar table; it makes this type of business problem a breeze. Here’s a simpler one:
CREATE TABLE dbo.Calendar ( TheDate date PRIMARY KEY, WeekdayName AS (CONVERT(varchar(8), DATENAME(WEEKDAY, TheDate))), WeekdayInstanceInMonth tinyint ); ;WITH x(d) AS -- populate with 2020 -> 2029 ( SELECT CONVERT(date, '20200101') UNION ALL SELECT DATEADD(DAY, 1, d) FROM x WHERE d < '20291231' ) INSERT dbo.Calendar(TheDate) SELECT d FROM x OPTION (MAXRECURSION 0); ;WITH c AS ( SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY YEAR(TheDate), MONTH(TheDate), WeekdayName ORDER BY TheDate) FROM dbo.Calendar ) UPDATE c SET WeekdayInstanceInMonth = rn;
Now your query is easy:
DECLARE @start date = '20210815', @end date = '20210920'; SELECT Dates = TheDate, [Saturday Number] = WeekdayInstanceInMonth FROM dbo.Calendar WHERE TheDate >= @start AND TheDate <= @end AND WeekdayName = 'Saturday' AND WeekdayInstanceInMonth IN (1,3); SELECT Dates = TheDate, [Sunday Number] = WeekdayInstanceInMonth FROM dbo.Calendar WHERE TheDate >= @start AND TheDate <= @end AND WeekdayName = 'Sunday';
Results (db<>fiddle example here):
Dates Saturday Number ---------- --------------- 2021-08-21 3 2021-09-04 1 2021-09-18 3 Dates Sunday Number ---------- --------------- 2021-08-15 3 2021-08-22 4 2021-08-29 5 2021-09-05 1 2021-09-12 2 2021-09-19 3
And to get just the number 28:
DECLARE @start date = '20210815', @end date = '20210920'; SELECT DATEDIFF(DAY, @start, @end) + 1 - (SELECT COUNT(*) FROM dbo.Calendar WHERE TheDate >= @start AND TheDate <= @end AND WeekdayName = 'Saturday' AND WeekdayInstanceInMonth IN (1,3)) - (SELECT COUNT(*) FROM dbo.Calendar WHERE TheDate >= @start AND TheDate <= @end AND WeekdayName = 'Sunday');