sql - 如何使用sql获取2个日期之间的第1个和第3个周六以及所有周日

Question

给定一个日期范围，我想返回该范围内的所有星期六和星期日，并满足以下条件:

• 仅当周六的顺序位置是所在月份的第一个或第三个周六(不在整个范围内)时才包含周六。
• 包括所有星期日，以及该星期日在该月份内的序号。

例如，如果开始日期是 2021 年 8 月 15 日，结束日期是 2021 年 9 月 20 日，则输出将为:

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

然后我可以获取总共(37 天)的日期范围，并减去星期日 (6) 以及每个月的第一个和第三个星期六 (3)，以 28 结束。

尝试了此查询

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)

它无法正常工作。

也尝试过这个解决方案 Get number of weekends between two dates in SQL用于计算工作日，但我只想要第 1 个和第 3 个星期六以及所有星期日

Answer 1

获取calendar table ;它使此类业务问题变得轻而易举。这是一个更简单的:

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;

现在您的查询很简单:

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

结果(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

并获取数字 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');