关联Date Table统计月度内活跃Status A日志条目数量

我有一张包含Status（状态）、Start（开始日期）和End（结束日期）字段的日志表，数据如下：

我希望将该日志表与日期表（Date Table）关联，统计出日期表各月份的月初至月末期间，处于活跃状态的Status A条目数量。

活跃状态的判定规则如下：

• 起止日期完全处于当月内
• 开始日期早于当月1日且结束日期在当月内
• 开始日期在当月内且结束日期晚于当月月末
• 开始日期早于当月且无结束日期

预期返回的结果表格式如下：

以此类推，需展示每个年月对应的统计数据。

可以通过以下SQL语句实现需求，核心是先从日期表提取唯一的年月维度，再关联日志表匹配活跃状态规则：

-- 从日期表提取各年月的月初、月末信息
WITH DateDim AS (
    SELECT
        YEAR(Date) AS year,
        MONTH(Date) AS Month,
        DATEFROMPARTS(YEAR(Date), MONTH(Date), 1) AS MonthStart,
        EOMONTH(Date) AS MonthEnd
    FROM DateTable
    GROUP BY YEAR(Date), MONTH(Date)
)
SELECT
    dd.year,
    RIGHT('0' + CAST(dd.Month AS VARCHAR(2)), 2) AS Month,
    'A' AS Status,
    COUNT(DISTINCT lt.ID) AS [Count of Active] -- 用日志表唯一ID避免重复统计
FROM DateDim dd
LEFT JOIN LogTable lt ON lt.Status = 'A'
    AND (
        -- 规则1：起止日期完全在当月内
        (lt.Start >= dd.MonthStart AND lt.End <= dd.MonthEnd)
        -- 规则2：开始早于当月1日，结束在当月内
        OR (lt.Start < dd.MonthStart AND lt.End BETWEEN dd.MonthStart AND dd.MonthEnd)
        -- 规则3：开始在当月内，结束晚于当月月末
        OR (lt.Start BETWEEN dd.MonthStart AND dd.MonthEnd AND lt.End > dd.MonthEnd)
        -- 规则4：开始早于当月，无结束日期
        OR (lt.Start < dd.MonthStart AND lt.End IS NULL)
    )
GROUP BY dd.year, dd.Month
ORDER BY dd.year, dd.Month;

关键说明 #

1. 日期维度处理：通过DateDim公共表表达式从日期表中提取每个年月的月初和月末日期，作为统计的时间基准。
2. 活跃状态匹配：通过4条OR条件覆盖所有给定的活跃判定规则，确保符合条件的日志条目被正确关联。
3. 去重统计：使用COUNT(DISTINCT lt.ID)避免同一日志条目因日期表包含当月多天数据而被重复统计；若日志表无唯一ID，可替换为COUNT(DISTINCT lt.Start, lt.End, lt.Status)。
4. 月份格式化：用RIGHT('0' + CAST(dd.Month AS VARCHAR(2)), 2)将月份转为两位数字格式，与预期结果一致。

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