SQL Server中如何实现日程变更后覆盖旧日程，避免关联日历表数据重复

在使用SQL Server处理日程变更表时，通过UNPIVOT操作将按天存储的日程转为行数据后，关联日历表会出现重复数据——同一员工的多次日程变更会从各自的变更日期开始生成重叠的日程记录，需要实现新日程从变更日期起覆盖旧日程，避免重复。

原实现直接关联日历表时未限制每个日程的生效截止日期，导致旧日程在新变更生效后仍会被匹配，以下是修正后的解决方案：

IF OBJECT_ID('tempdb..#ScheduleChange') Is Not Null Drop Table #ScheduleChange
Create table #ScheduleChange(ChangeDate Date, EmployeeId NVARCHAR(50), Monday NVARCHAR(50), Tuesday NVARCHAR(50), Wednesday NVARCHAR(50), Thursday NVARCHAR(50), Friday NVARCHAR(50), Saturday NVARCHAR(50), Sunday NVARCHAR(50))
INSERT INTO #ScheduleChange VALUES
('2024-01-02','100', '11:00 a.m.-7:00 p.m.', '11:00 a.m.-7:00 p.m.','11:00 a.m.-7:00 p.m.','11:00 a.m.-7:00 p.m.','11:00 a.m.-7:00 p.m.',NULL,'10:00 a.m.-2:00 p.m.'),
('2024-01-14','100','9:00 a.m-5:00 p.m','9:00 a.m-5:00 p.m','9:00 a.m-5:00 p.m','9:00 a.m-5:00 p.m','9:00 a.m-5:00 p.m',NULL,'10:00 a.m.-2:00 p.m.'),
('2024-01-04', '200','9:00 a.m.-5:00 p.m.','9:00 a.m.-5:00 p.m.','9:00 a.m.-5:00 p.m.','9:00 a.m.-5:00 p.m.','9:00 a.m.-5:00 p.m.','9:00 a.m.-1:00 p.m.',NULL),
('2024-01-10','200','10:00 a.m.-6:00 p.m.','10:00 a.m.-6:00 p.m.','10:00 a.m.-6:00 p.m.','10:00 a.m.-6:00 p.m.','10:00 a.m.-6:00 p.m.','10:00 a.m.-2:00 p.m.',NULL)


IF OBJECT_ID('tempdb..#Calendar') Is Not Null Drop Table #Calendar
Create table #Calendar(Date Date, WeekDayName NVARCHAR(15))
Declare @Start Date
Set @Start = '20240101'

WHILE @Start <= Cast(Getdate()-1 As Date)
BEGIN
  INSERT #Calendar
  Select @Start,
  DATENAME(DW,@Start)

 Set @Start =Dateadd(day,1,@Start)
End

;WITH
schedules
AS
(
    SELECT 
        ChangeDate, 
        EmployeeId, 
        Schedule, 
        WeekDayName,
        -- 获取同一员工同一工作日的下一次变更日期，无后续变更则设为远未来日期
        LEAD(ChangeDate) OVER (PARTITION BY EmployeeId, WeekDayName ORDER BY ChangeDate) AS NextChangeDate
    FROM #ScheduleChange
    unpivot
    (
      Schedule
      for WeekDayName in (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)
    ) unpiv
)
SELECT 
    s.EmployeeId,
    c.Date,
    s.WeekDayName,
    s.Schedule
FROM schedules s
JOIN #Calendar c 
    ON c.WeekDayName = s.WeekDayName
    -- 限制日历日期在当前日程的生效区间内
    AND c.Date >= s.ChangeDate
    AND (c.Date < s.NextChangeDate OR s.NextChangeDate IS NULL)
ORDER BY s.EmployeeId, c.Date

关键逻辑说明 #

• LEAD窗口函数：按员工ID和工作日分组、按变更日期排序，获取每条日程记录的下一次变更日期，以此确定当前日程的生效截止时间。
• 关联条件优化：新增c.Date < s.NextChangeDate OR s.NextChangeDate IS NULL判断，确保每个日历日期仅匹配当前生效的最新日程，旧日程在新变更生效后不再被选中。
• 结果整理：输出字段聚焦员工、日历日期、工作日和有效日程，按员工和日期排序，清晰展示每日生效的唯一日程。

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