MSSQL按外部生成的日期范围统计两列日期的工单开闭数量

需求说明 #

现有工单表work_order，字段如下：

• 工单ID
• opening_date：开单日期
• end_date：结单日期，未完成工单的end_date为NULL值
  样例数据：

ID  opening_date    end_date 
1   2021-01-04      2021-01-14
2   2021-01-04      2021-01-26
3   2021-01-14      2021-02-15
4   2021-02-01      NULL
5   2021-02-04      2021-02-26
6   2021-02-10      2021-02-15

要求按日/周/月维度统计每个周期的新开单数量、结单数量，无数据的周期统计值显示为0。

实现思路 #

核心是先生成连续的时间维度序列作为主表，再左联工单表分别统计开单、结单数量，避免因开单/结单日期不重合导致数据缺失。

代码实现 #

1. 按日统计 #

-- 递归生成连续日期序列，可根据实际业务调整起止日期
WITH date_series AS (
    SELECT CAST('2021-01-01' AS DATE) AS stat_date
    UNION ALL
    SELECT DATEADD(DAY, 1, stat_date)
    FROM date_series
    WHERE stat_date < '2021-02-28'
)
SELECT 
    stat_date AS [date],
    -- 统计当日新开单数量
    COUNT(CASE WHEN wo.opening_date = ds.stat_date THEN 1 END) AS openings,
    -- 统计当日结单数量，排除未完成工单
    COUNT(CASE WHEN wo.end_date = ds.stat_date THEN 1 END) AS endings
FROM date_series ds
LEFT JOIN work_order wo 
    ON wo.opening_date = ds.stat_date 
    OR wo.end_date = ds.stat_date
GROUP BY ds.stat_date
ORDER BY ds.stat_date
OPTION (MAXRECURSION 0); -- 关闭递归层数限制，避免日期范围过大报错

运行结果与给出的按日预期结果完全一致。

2. 按月统计 #

WITH date_series AS (
    SELECT CAST('2021-01-01' AS DATE) AS stat_date
    UNION ALL
    SELECT DATEADD(DAY, 1, stat_date)
    FROM date_series
    WHERE stat_date < '2021-02-28'
),
daily_stat AS (
    SELECT 
        stat_date,
        COUNT(CASE WHEN wo.opening_date = ds.stat_date THEN 1 END) AS openings,
        COUNT(CASE WHEN wo.end_date = ds.stat_date THEN 1 END) AS endings
    FROM date_series ds
    LEFT JOIN work_order wo 
        ON wo.opening_date = ds.stat_date 
        OR wo.end_date = ds.stat_date
    GROUP BY ds.stat_date
)
SELECT 
    FORMAT(stat_date, 'yyyy-MM') AS [Month],
    SUM(openings) AS openings,
    SUM(endings) AS endings
FROM daily_stat
GROUP BY FORMAT(stat_date, 'yyyy-MM')
ORDER BY [Month]
OPTION (MAXRECURSION 0);

运行结果与给出的按月预期结果完全一致。

3. 按周统计 #

只需要修改分组维度即可，示例按自然周统计（每周一为周起始）：

WITH date_series AS (
    SELECT CAST('2021-01-01' AS DATE) AS stat_date
    UNION ALL
    SELECT DATEADD(DAY, 1, stat_date)
    FROM date_series
    WHERE stat_date < '2021-02-28'
),
daily_stat AS (
    SELECT 
        stat_date,
        COUNT(CASE WHEN wo.opening_date = ds.stat_date THEN 1 END) AS openings,
        COUNT(CASE WHEN wo.end_date = ds.stat_date THEN 1 END) AS endings
    FROM date_series ds
    LEFT JOIN work_order wo 
        ON wo.opening_date = ds.stat_date 
        OR wo.end_date = ds.stat_date
    GROUP BY ds.stat_date
)
SELECT 
    DATEPART(YEAR, stat_date) AS 年,
    DATEPART(WEEK, stat_date, 2) AS 周数, -- 2代表周起始为周一
    MIN(stat_date) AS 周起始日期,
    MAX(stat_date) AS 周结束日期,
    SUM(openings) AS openings,
    SUM(endings) AS endings
FROM daily_stat
GROUP BY DATEPART(YEAR, stat_date), DATEPART(WEEK, stat_date, 2)
ORDER BY 年, 周数
OPTION (MAXRECURSION 0);

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