SQL同表基于上月数据更新下月对应行的存储过程实现方法

基础信息 #

• 操作目标表：Employee_invoice，表结构字段：
  • JobSite：工地名称/标识
  • Name：员工姓名
  • InvoiceDate：账单日期
  • InvoiceAmount：账单金额
  • Crew_size：班组人数
• 实现逻辑：以JobSite+Name为唯一匹配维度，每月1日自动将上月对应维度的有效字段值，同步到当月初始值为0的对应记录上，支持通过参数控制需要同步的字段范围。

存储过程代码 #

CREATE OR ALTER PROCEDURE sp_SyncEmployeeInvoiceMonthly
    @Tabletype TINYINT -- 参数取值：1=仅同步InvoiceAmount 2=仅同步Crew_size 3=同时同步两个字段
AS
BEGIN
    SET NOCOUNT ON;
    -- 自动计算上月、当月时间边界，无需手动修改日期
    DECLARE @LastMonthStart DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)
    DECLARE @LastMonthEnd DATE = EOMONTH(@LastMonthStart)
    DECLARE @CurrentMonthStart DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

    -- 参数合法性校验
    IF @Tabletype NOT IN (1,2,3)
    BEGIN
        RAISERROR('@Tabletype参数非法，仅允许传入1、2、3', 16, 1)
        RETURN
    END

    -- 跨月同步更新逻辑
    UPDATE curr
    SET
        InvoiceAmount = CASE WHEN @Tabletype IN (1,3) THEN last.InvoiceAmount ELSE curr.InvoiceAmount END,
        Crew_size = CASE WHEN @Tabletype IN (2,3) THEN last.Crew_size ELSE curr.Crew_size END
    FROM Employee_invoice curr
    -- 关联上月同维度的有效数据（过滤上月值为0的无效记录）
    INNER JOIN (
        SELECT JobSite, Name, InvoiceAmount, Crew_size
        FROM Employee_invoice
        WHERE InvoiceDate BETWEEN @LastMonthStart AND @LastMonthEnd
          AND InvoiceAmount <> 0 
          AND Crew_size <> 0
    ) last ON curr.JobSite = last.JobSite AND curr.Name = last.Name
    WHERE curr.InvoiceDate >= @CurrentMonthStart
      -- 仅更新当月初始值为0的记录，避免覆盖人工录入的非零有效数据
      AND (
            (@Tabletype IN (1,3) AND curr.InvoiceAmount = 0)
         OR (@Tabletype IN (2,3) AND curr.Crew_size = 0)
      )
END
GO

定时运行配置 #

直接在数据库代理中新建定时作业：

• 调度规则：设置为每月1日00:00自动执行
• 执行语句：根据需要同步的字段传入对应参数，比如需要同时同步两个字段时执行：

  EXEC sp_SyncEmployeeInvoiceMonthly @Tabletype = 3
  

注意：存储过程不会自动生成当月不存在的维度记录，仅对当月已经存在、对应字段初始值为0的记录做更新，不会覆盖当月已经人工录入的非零值。

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