按月末日期自动统计符合双日期条件的行数（含MTD）

需求说明 #

需要编写自动化SQL查询，统计满足以下条件的记录数：

• date1 ≤ 指定月末日期（或当月MTD的当前日期）
• date2 > 该日期（或date2为NULL）

要求自动覆盖过去12个月的月末统计，以及查询执行时的当月累计（MTD）数据，替代手动编写多月份查询再用UNION ALL拼接的繁琐操作。现有数据库无直接存储的月末日期字段，但有日期维度表可用。

手动查询的痛点示例 #

原手动写法需要为每个月单独编写查询，再拼接：

-- 9月统计
Select count(column1) as "September Count"
From table a
 Left Outer Join table b on a.pk=b.pk
Where a.date1 <= '2023-09-30 00:00:00'
 AND (b.date2 > '2023-09-30 00:00:00' OR date2 is null)

UNION ALL

-- 8月统计
Select count(a.column1) as "August Count"
From table a
Left Outer Join table2 b on a.pk=b.pk
Where a.date1 <= '2023-08-31 00:00:00'
 AND (b.date2 > '2023-08-31 00:00:00' OR b.date2 is null)

解决方案 #

方案一：用日期函数生成月末日期（无需维度表） #

适合没有日期维度表，或不想依赖维度表的场景，用递归CTE生成需要统计的日期范围。

1. 行式展示（每月一行，含MTD）

这种格式清晰展示每个统计周期的明细数据：

WITH MonthEnds AS (
    -- 先加入当月MTD的统计截止日期（当前系统日期）
    SELECT 
        CAST(GETDATE() AS DATE) AS report_date,
        'Current MTD' AS month_label
    UNION ALL
    -- 生成过去12个月的月末日期
    SELECT 
        DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - n, 0)) AS report_date,
        DATENAME(MONTH, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - n, 0)) + ' ' + CAST(YEAR(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - n, 0)) AS VARCHAR) AS month_label
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS nums(n)
)
SELECT 
    me.month_label,
    a.StatusDescr AS Status,
    COUNT(DISTINCT a.CaseNbr) AS record_count
FROM MonthEnds me
CROSS JOIN CRM a
INNER JOIN Event b ON a.AcctID = b.AcctID
LEFT JOIN Meeting c ON a.AcctID = c.AcctID
LEFT JOIN Party e ON a.AcctID = e.AcctID
LEFT JOIN Service g ON a.AcctID = g.AcctID
LEFT JOIN Closure h ON a.AcctID = h.AcctID
INNER JOIN [User] f ON b.Event_User = f.Name
WHERE 
    -- 核心日期条件，自动适配每个统计周期
    b.eventdate <= me.report_date
    AND (h.closedate > me.report_date OR h.closedate IS NULL)
    -- 原有业务过滤条件
    AND a.AcctType IN ('SMMS', 'SGHOV', 'SMXD')
    AND a.LocID IN ('219', '200', '260')
    AND (b.EventTYPE IN ('1252', '1225') OR b.EventCd = 'SMRESP')
    AND b.DeletedFlag = 'No'
    AND h.CurrentFlag = 'yes'
    AND a.statusdescr = 'open'
GROUP BY me.month_label, a.StatusDescr
ORDER BY 
    -- 排序：MTD在前，其余月份从近到远
    CASE WHEN me.month_label = 'Current MTD' THEN 0 ELSE 1 END,
    me.report_date DESC;

2. 列式展示（每月一列，适合对比）

用PIVOT将行式结果转为列式，方便各月数据对比：

WITH MonthEnds AS (
    -- 生成当月（含MTD）及过去12个月的日期和列名
    SELECT 
        CAST(GETDATE() AS DATE) AS report_date,
        'Current_MTD' AS month_col
    UNION ALL
    SELECT 
        DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - n, 0)) AS report_date,
        DATENAME(MONTH, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - n, 0)) + '_' + CAST(YEAR(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - n, 0)) AS VARCHAR) AS month_col
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS nums(n)
),
RawStats AS (
    SELECT 
        a.StatusDescr,
        me.month_col,
        COUNT(DISTINCT a.CaseNbr) AS record_count
    FROM MonthEnds me
    CROSS JOIN CRM a
    INNER JOIN Event b ON a.AcctID = b.AcctID
    LEFT JOIN Meeting c ON a.AcctID = c.AcctID
    LEFT JOIN Party e ON a.AcctID = e.AcctID
    LEFT JOIN Service g ON a.AcctID = g.AcctID
    LEFT JOIN Closure h ON a.AcctID = h.AcctID
    INNER JOIN [User] f ON b.Event_User = f.Name
    WHERE 
        b.eventdate <= me.report_date
        AND (h.closedate > me.report_date OR h.closedate IS NULL)
        AND a.AcctType IN ('SMMS', 'SGHOV', 'SMXD')
        AND a.LocID IN ('219', '200', '260')
        AND (b.EventTYPE IN ('1252', '1225') OR b.EventCd = 'SMRESP')
        AND b.DeletedFlag = 'No'
        AND h.CurrentFlag = 'yes'
        AND a.statusdescr = 'open'
    GROUP BY a.StatusDescr, me.month_col
)
SELECT *
FROM RawStats
PIVOT (
    SUM(record_count) FOR month_col IN (
        [Current_MTD],
        [September_2023], [August_2023], [July_2023],
        [June_2023], [May_2023], [April_2023],
        [March_2023], [February_2023], [January_2023],
        [December_2022], [November_2022], [October_2022]
    )
) AS PivotResult;

注：如果需要完全动态的列名（适配不同月份），可以用动态SQL拼接PIVOT的列列表，具体写法因数据库方言（SQL Server/MySQL/PostgreSQL）而异。

方案二：利用现有日期维度表关联 #

如果数据库已有日期维度表（比如date_dim，包含date、month_end_date、is_month_end、month_label等字段），直接关联维度表即可：

SELECT 
    dd.month_label,
    a.StatusDescr AS Status,
    COUNT(DISTINCT a.CaseNbr) AS record_count
FROM date_dim dd
CROSS JOIN CRM a
INNER JOIN Event b ON a.AcctID = b.AcctID
LEFT JOIN Meeting c ON a.AcctID = c.AcctID
LEFT JOIN Party e ON a.AcctID = e.AcctID
LEFT JOIN Service g ON a.AcctID = g.AcctID
LEFT JOIN Closure h ON a.AcctID = h.AcctID
INNER JOIN [User] f ON b.Event_User = f.Name
WHERE 
    -- 筛选统计范围：当月MTD + 过去12个月末
    (dd.date = CAST(GETDATE() AS DATE))
    OR (dd.is_month_end = 1 AND dd.month_end_date >= DATEADD(MONTH, -12, GETDATE()))
    -- 核心日期条件
    AND b.eventdate <= CASE WHEN dd.date = CAST(GETDATE() AS DATE) THEN dd.date ELSE dd.month_end_date END
    AND (h.closedate > CASE WHEN dd.date = CAST(GETDATE() AS DATE) THEN dd.date ELSE dd.month_end_date END OR h.closedate IS NULL)
    -- 原有业务过滤条件
    AND a.AcctType IN ('SMMS', 'SGHOV', 'SMXD')
    AND a.LocID IN ('219', '200', '260')
    AND (b.EventTYPE IN ('1252', '1225') OR b.EventCd = 'SMRESP')
    AND b.DeletedFlag = 'No'
    AND h.CurrentFlag = 'yes'
    AND a.statusdescr = 'open'
GROUP BY dd.month_label, a.StatusDescr
ORDER BY dd.date DESC;

注：根据实际日期维度表的字段调整筛选条件，比如用is_current_month标记当月，或year_month字段筛选过去12个月。

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