SQL Server中基于Manager表更新Customer表FROM字段的正确实现

问题概述 #

使用SQL Server，初始Customer表为空，将Manager表数据插入Customer表后，执行UPDATE语句得到不符合预期的结果，需要调整语句以实现目标日期区间逻辑，且实际业务日期不局限于月末月初。

初始数据 #

Manager表结构及数据 #

___________________________
|         Manager         |
|=========================|
| ID   Cust     RefDate   |
|-------------------------|
|  1     A    2023-01-31  |
|  2     B    2023-02-28  |
|  3     C    2023-03-31  |
|  4     A    2023-04-30  |
|  5     B    2023-05-31  |
|  6     A       NULL     |
|________________________ |

插入后Customer表初始状态 #

_______________________________________
|             Customer                |
|=====================================|
| ID   Cust     FROM          TO      |
|------------------------------------ |
|  1     A      NULL      2023-01-31  |
|  2     B      NULL      2023-02-28  |
|  3     C      NULL      2023-03-31  |
|  4     A      NULL      2023-04-30  |
|  5     B      NULL      2023-05-31  |  
|  6     A      NULL         NULL     | 
|_____________________________________|

原错误语句 #

;WITH From_Table AS
( 
        select CustomerName as CustomerName, 
            [SalesManager], 
            lag([To], 1)  OVER(ORDER BY [CustomerKey] ASC) as TargetDate --, [To]
        FROM Customer
)

UPDATE Customer
SET [Customer].[From] = DATEADD(day, 1, Max_From)

FROM
(
    SELECT dd.CustomerName, dd.SalesManager, dd.[To], dd.[From], MAX(ff.TargetDate) as Max_From
    FROM From_Table AS ff
    INNER JOIN Customer AS dd
    ON ff.CustomerName = dd.CustomerName AND ff.SalesManager = dd.SalesManager
    GROUP BY dd.CustomerName, dd.SalesManager, dd.[To], dd.[From]
    --HAVING dd.[To] IS NULL AND dd.[From] IS NULL
) AS final
INNER JOIN Customer fd
ON fd.CustomerName = final.CustomerName AND fd.SalesManager = final.SalesManager
WHERE fd.[From] IS NULL

错误结果 #

_______________________________________
|             Customer                |
|=====================================|
| ID   Cust     FROM          TO      |
|------------------------------------ |
|  1     A   2023-04-01   2023-01-31  |
|  2     B   2023-05-01   2023-02-28  |
|  3     C   2023-03-01   2023-03-31  |
|  4     A   2023-04-01   2023-04-30  |
|  5     B   2023-05-01   2023-05-31  |
|  6     A   2023-04-01      NULL     |
|_____________________________________|

期望结果 #

_______________________________________
|             Customer                |
|=====================================|
| ID   Cust     FROM          TO      |
|------------------------------------ |
|  1     A      NULL      2023-01-31  |
|  2     B   2023-02-01   2023-02-28  |
|  3     C   2023-03-01   2023-03-31  |
|  4     A   2023-04-01   2023-04-30  |
|  5     B   2023-05-01   2023-05-31  |  
|  6     A   2023-06-01      NULL     |
|_____________________________________|

修正后的语句 #

;WITH CustomerWithPrevTo AS (
    SELECT 
        ID,
        [From],
        [To],
        -- 按ID全局排序，获取上一条记录的To日期
        LAG([To]) OVER (ORDER BY ID ASC) AS PrevTo
    FROM Customer
)
UPDATE CustomerWithPrevTo
SET [From] = DATEADD(day, 1, PrevTo)
WHERE [From] IS NULL;

修正说明 #

1. 原语句问题：错误地按客户分组关联并取最大日期，导致跨客户获取了错误的前置日期，逻辑偏离了按全局记录顺序生成日期区间的需求。
2. 修正逻辑：
   • 使用LAG([To]) OVER (ORDER BY ID ASC)按记录ID的顺序，获取每条记录的上一条记录的To日期
   • 将当前记录的From设置为上一条记录To日期加1天
   • 第一条记录的LAG返回NULL，因此From保持初始的NULL，符合期望
3. 兼容性：该逻辑不依赖日期是否为月末/月初，适用于任意业务日期场景

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