如何在SQL Server表中插入虚拟行填充缺失月度数据至上月末

实现逻辑 #

• 按acct、type分组统计每个分组的最新end_date，筛选出最新日期早于截止日期（2021-06-30）的待补全分组，同分组下amt值固定不变
• 用递归CTE生成待补全的连续月份序列，每个月自动计算当月首日作为begin_date、当月最后一天作为end_date
• 可直接通过UNION ALL拼接原数据查询，也可以将生成的虚拟行直接插入原表

代码实现 #

1. 原数据+虚拟行拼接查询（不修改原表） #

-- 定义截止日期，动态取上月末可替换为 EOMONTH(DATEADD(MONTH, -1, GETDATE()))
DECLARE @EndLimit DATE = '2021-06-30';

WITH GroupLatest AS (
    -- 统计每个分组的最新结束日期
    SELECT 
        acct,
        type,
        amt,
        MAX(end_date) AS latest_end
    FROM 你的表名
    GROUP BY acct, type, amt
    HAVING MAX(end_date) < @EndLimit
),
MonthSeries AS (
    -- 递归生成需要补的所有月份
    SELECT 
        acct,
        type,
        amt,
        DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(latest_end), MONTH(latest_end), 1)) AS begin_date,
        EOMONTH(DATEADD(MONTH, 1, latest_end)) AS end_date
    FROM GroupLatest
    
    UNION ALL
    
    SELECT 
        acct,
        type,
        amt,
        DATEADD(MONTH, 1, begin_date) AS begin_date,
        EOMONTH(DATEADD(MONTH, 1, begin_date)) AS end_date
    FROM MonthSeries
    WHERE end_date < @EndLimit
)
-- 拼接原数据和虚拟行
SELECT acct, type, amt, begin_date, end_date FROM 你的表名
UNION ALL
SELECT acct, type, amt, begin_date, end_date FROM MonthSeries
ORDER BY acct, type, begin_date

2. 虚拟行插入原表 #

DECLARE @EndLimit DATE = '2021-06-30';

WITH GroupLatest AS (
    SELECT 
        acct,
        type,
        amt,
        MAX(end_date) AS latest_end
    FROM 你的表名
    GROUP BY acct, type, amt
    HAVING MAX(end_date) < @EndLimit
),
MonthSeries AS (
    SELECT 
        acct,
        type,
        amt,
        DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(latest_end), MONTH(latest_end), 1)) AS begin_date,
        EOMONTH(DATEADD(MONTH, 1, latest_end)) AS end_date
    FROM GroupLatest
    
    UNION ALL
    
    SELECT 
        acct,
        type,
        amt,
        DATEADD(MONTH, 1, begin_date) AS begin_date,
        EOMONTH(DATEADD(MONTH, 1, begin_date)) AS end_date
    FROM MonthSeries
    WHERE end_date < @EndLimit
)
INSERT INTO 你的表名 (acct, type, amt, begin_date, end_date)
SELECT acct, type, amt, begin_date, end_date FROM MonthSeries

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