基于SQL Management Studio 18按日历日期统计案件开/关及净量

针对你的需求，核心是先生成连续的日历日期序列，再关联原表数据统计每日的开立、关闭数量，最后计算累计在案的净量。以下是适配SQL Server（SSMS 18）的完整解决方案：

假设前提 #

• 你的案件表名为 Cases，请根据实际表名替换
• 日期字段的时间部分不影响统计，需转换为纯日期格式

完整SQL代码 #

WITH DateRange AS (
    -- 确定统计的日期范围：从最早开立日期到最晚关闭日期（未关闭案件用当前日期兜底）
    SELECT 
        MIN(CONVERT(DATE, [Date Opened])) AS CalendarDate,
        ISNULL(MAX(CONVERT(DATE, [Date Closed])), GETDATE()) AS EndDate
    FROM Cases
    UNION ALL
    -- 递归生成连续日期
    SELECT DATEADD(DAY, 1, CalendarDate), EndDate
    FROM DateRange
    WHERE CalendarDate < EndDate
),
DailyOpenStats AS (
    -- 统计每日开立案件数
    SELECT 
        CONVERT(DATE, [Date Opened]) AS CalendarDate,
        COUNT(Value) AS 开立数量
    FROM Cases
    GROUP BY CONVERT(DATE, [Date Opened])
),
DailyCloseStats AS (
    -- 统计每日关闭案件数（排除未关闭的NULL记录）
    SELECT 
        CONVERT(DATE, [Date Closed]) AS CalendarDate,
        COUNT(Value) AS 关闭数量
    FROM Cases
    WHERE [Date Closed] IS NOT NULL
    GROUP BY CONVERT(DATE, [Date Closed])
)
-- 关联所有日期，计算累计净量
SELECT 
    dr.CalendarDate AS 日历日期,
    ISNULL(dos.开立数量, 0) AS 开立数量,
    ISNULL(dcs.关闭数量, 0) AS 关闭数量,
    -- 累计计算在案净量：从起始日到当前日的（开立数-关闭数）之和
    SUM(ISNULL(dos_sub.开立数量, 0) - ISNULL(dcs_sub.关闭数量, 0)) OVER (ORDER BY dr.CalendarDate) AS 净量
FROM DateRange dr
LEFT JOIN DailyOpenStats dos ON dr.CalendarDate = dos.CalendarDate
LEFT JOIN DailyCloseStats dcs ON dr.CalendarDate = dcs.CalendarDate
-- 子查询用于累计计算
LEFT JOIN DailyOpenStats dos_sub ON dr.CalendarDate >= dos_sub.CalendarDate
LEFT JOIN DailyCloseStats dcs_sub ON dr.CalendarDate >= dcs_sub.CalendarDate
GROUP BY dr.CalendarDate, dos.开立数量, dcs.关闭数量
ORDER BY dr.CalendarDate
OPTION (MAXRECURSION 0); -- 解除递归次数限制，支持超过100天的日期范围

代码说明 #

1. 日期序列生成：通过递归CTE DateRange 生成从最早开立日期到最晚关闭日期的所有连续日历日期，未关闭案件用当前日期作为结束范围。
2. 每日统计：分别用两个CTE统计每日的开立、关闭案件数，自动过滤无数据的日期。
3. 累计净量计算：使用窗口函数 SUM() OVER (ORDER BY) 计算从起始日到当日的累计净变化，得到实时在案数量。
4. 兼容处理：用 ISNULL() 替换NULL值为0，确保无数据日期的统计结果符合预期。

适配SQL Server 2022+ 版本 #

如果使用SQL Server 2022及以上版本，可改用 GENERATE_SERIES 生成日期序列，性能更优：

WITH DateRange AS (
    SELECT 
        DATEADD(DAY, value, MIN(CONVERT(DATE, [Date Opened]))) AS CalendarDate
    FROM Cases
    CROSS APPLY GENERATE_SERIES(
        0, 
        DATEDIFF(DAY, MIN(CONVERT(DATE, [Date Opened])), ISNULL(MAX(CONVERT(DATE, [Date Closed])), GETDATE()))
    )
    GROUP BY DATEADD(DAY, value, MIN(CONVERT(DATE, [Date Opened])))
),
-- 后续统计逻辑同上方代码
DailyOpenStats AS (...)

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