SQL Server查询：计算排除非工作日的日期区间并生成标识值

需求说明 #

针对dbo.activity表中的每条记录，计算open date到close date之间的有效工作日（排除周末、银行假日，非工作日数据存储在dbo.dates表），若有效工作日数小于3天则返回1，否则返回0，最终输出便于报表汇总的0/1序列。

实现SQL语句 #

WITH DateRange AS (
    -- 生成每条activity记录对应的日期区间序列
    SELECT 
        a.[open date],
        a.[close date],
        DATEADD(day, n - 1, a.[open date]) AS current_date
    FROM 
        dbo.activity a
    CROSS JOIN 
        -- 生成足够多的数字序列，覆盖最大日期区间长度（这里设为1000，可按需调整）
        (SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n 
         FROM sys.all_columns) nums
    WHERE 
        DATEADD(day, n - 1, a.[open date]) <= a.[close date]
)
SELECT 
    dr.[open date],
    dr.[close date],
    -- 判断有效工作日数是否小于3，返回1或0
    CASE 
        WHEN COUNT(CASE WHEN d.[non-working day] IS NULL THEN 1 END) < 3 THEN 1
        ELSE 0
    END AS is_less_than_3_workdays
FROM 
    DateRange dr
LEFT JOIN 
    dbo.dates d ON dr.current_date = d.date
GROUP BY 
    dr.[open date], dr.[close date]
ORDER BY 
    dr.[open date];

代码说明 #

1. DateRange CTE：通过CROSS JOIN生成数字序列，动态构建每条activity记录的日期区间内的所有日期，确保覆盖open date到close date的每一天。
2. 排除非工作日：通过LEFT JOIN关联dbo.dates表，筛选出不在非工作日列表中的日期（即d.[non-working day] IS NULL的记录）。
3. 统计与判断：用COUNT统计有效工作日数量，再通过CASE WHEN判断是否小于3，返回对应的0/1标记。
4. 日期格式兼容：若你的SQL Server实例日期格式与示例中的dd/mm/yyyy不匹配，可在转换日期时使用TRY_CONVERT(date, [open date], 103)确保正确解析（103对应英国/欧洲日期格式）。

示例数据输出 #

结合你提供的测试数据，执行上述查询后会得到如下结果：

内容的提问来源于stack exchange，提问作者Matt