How to get 1st and 3rd Saturday and all Sunday between 2 dates using sql

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

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');