是否存在可在当月缺失记录时插入上月对应记录的SQL INSERT写法

表结构与示例数据 #

现有表TableA包含ID、Amount、Date3个字段，示例数据如下：

ID   Amount  Date
-------------------------
1     100    02/02/2020
2     100    02/02/2020

1     100    03/10/2020  Insert Feb record with March date
2     100    03/10/2020  Insert Feb record with March date
3     100    03/10/2020
4     100    03/10/2020

1     100    04/03/2020  
4     100    04/03/2020
2     100    04/03/2020  Insert MAR record with APR date
3     100    04/03/2020  Insert MAR record with APR date

业务规则 #

• 动态变量@ProcessingDate为月度处理的指定日期
• 当@ProcessingDate所在自然月内，某ID无对应表记录时，需将该ID上一自然月的同金额记录，以@ProcessingDate为Date字段值插入TableA

历史失败方案 #

方案1：NOT EXISTS 子句实现 #

原代码：

DECLARE @ProcessingDate DateTime
SET @ProcessingDate = '04/03/2020'

SELECT DISTINCT
    t1.ID, Amount, ProcessingDate
FROM 
    TABLE1 t1
WHERE 
    NOT EXISTS (SELET *
                FROM TABLE1 t2
                WHERE t2.ID = t1.ID  
                  AND t2.ProcessingDate = @ExpenseProcessingDate)

问题：未限定数据的月度范围，会选中所有历史旧行，无法实现“仅匹配上一月有效记录”的要求。

方案2：LAG 窗口函数实现 #

原代码：

DECLARE @ProcessingDate DateTime
SET @ProcessingDate = '04/03/2020'

SELECT
    *,
    LAG(@ProcessingDate) OVER (Partition BY ID, Amount ORDER BY ProcessingDate) AS PrevEvent
FROM 
    Table1 A
WHERE 
    NOT EXISTS (SELECT * FROM TABLE1 A1
                WHERE A1.ID = A.ID 
                  AND A1.ProcessingDate = @ProcessingDate)

问题：未限定上月数据筛选条件，同样会返回全量历史记录。

正确INSERT实现语句 #

DECLARE @ProcessingDate DateTime
SET @ProcessingDate = '04/03/2020'

-- 划分当月、上一自然月的时间边界
DECLARE @CurrentMonthStart DATE = DATEFROMPARTS(YEAR(@ProcessingDate), MONTH(@ProcessingDate), 1)
DECLARE @PrevMonthStart DATE = DATEADD(MONTH, -1, @CurrentMonthStart)
DECLARE @PrevMonthEnd DATE = EOMONTH(@PrevMonthStart)

-- 插入补全记录
INSERT INTO TableA (ID, Amount, Date)
SELECT 
    prev.ID, 
    prev.Amount, 
    @ProcessingDate AS Date
FROM 
    -- 提取上一自然月的ID+金额唯一组合，避免冗余数据
    (SELECT DISTINCT ID, Amount 
     FROM TableA 
     WHERE Date BETWEEN @PrevMonthStart AND @PrevMonthEnd) prev
WHERE 
    -- 筛选当月无对应记录的ID
    NOT EXISTS (
        SELECT 1 
        FROM TableA curr 
        WHERE curr.ID = prev.ID 
          AND curr.Date >= @CurrentMonthStart 
          AND curr.Date < DATEADD(MONTH, 1, @CurrentMonthStart)
    )

实现要点 #

• 明确划分自然月时间边界，避免跨月数据误匹配
• 先提取上月唯一的ID+Amount组合，从源头过滤冗余数据
• 仅对当月无记录的ID执行插入逻辑，不会拉取全量历史数据

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