使用組之前填充日期的數據值填充缺失的日期

圖片在部門之間轉移的幫助台工單。我們想知道每張票在一天結束時的部門是開放的每一天。該表包含每個工單打開的每一天的最後一個部門，其中部門發生了變化（包括工單最初打開日期和關閉日期的行）。數據表如下所示：

CREATE TABLE TicketAssigment (
   TicketId     INT NOT NULL,
   AssignedDate DATE NOT NULL,
   DepartmentId INT NOT NULL);

我需要使用按日期排序的上一個 TicketAssigment 行中的 DepartmentId 填寫每個 TicketId 的任何缺失日期。

如果我有這樣的 TicketAssigment 行：

1, '1/1/2016', 123 -- Opened
1, '1,4,2016', 456 -- Transferred and closed
2, '1/1/2016', 25  -- Opened
2, '1/2/2016', 52  -- Transferred
2, '1/4/2016', 25  -- Transferred and closed

我想要這個輸出：

1, '1/1/2016', 123
1, '1/2/2016', 123
1, '1/3/2016', 123
1, '1/4/2016', 456
2, '1/1/2016', 25
2, '1/2/2016', 52
2, '1/3/2016', 52
2, '1/4/2016', 25

這看起來可能接近我需要的，但我沒有耐心讓它完成，估計的計劃成本有 6 位數：

SELECT  l.TicketId, c.Date, MIN(l.DepartmentId)
FROM    dbo.Calendar c 
       OUTER APPLY (SELECT TOP 1 TicketId, DepartmentId FROM TicketAssigment WHERE AssignedDate <= c.Date ORDER BY AssignedDate DESC) l
WHERE   c.Date <= (SELECT MAX(AssignedDate) FROM TicketAssigment)
GROUP   BY l.TicketId, c.Date
ORDER   BY l.TicketId, c.Date;

我懷疑有一種方法可以使用 LAG 和窗框來做到這一點，但我還沒有完全弄清楚。滿足要求的更有效方法是什麼？

用於LEAD()獲取 TicketId 分區中的下一行。然後加入日曆表以獲取其間的所有日期。

WITH TAwithnext AS
(SELECT *, LEAD(AssignmentDate) OVER (PARTITION BY TicketID ORDER BY AssignmentDate) AS NextAssignmentDate
FROM TicketAssignment
)
SELECT t.TicketID, c.Date, t.DepartmentID
FROM dbo.Calendar c
JOIN TAwithnext t
   ON c.Date BETWEEN t.AssignmentDate AND ISNULL(DATEADD(day,-1,t.NextAssignmentDate),t.AssignmentDate)
;

各種獲取日曆表的方法…

這是一種快速的方法（我沒有測試過性能或可擴展性）

– 創建日曆表

-- borrowed from @Aaron's post http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3 
CREATE TABLE dbo.Calendar(d DATE PRIMARY KEY);

INSERT dbo.Calendar(d) SELECT TOP (365)
DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, '20160101')
FROM [master].dbo.spt_values
WHERE [type] = N'P' ORDER BY number;

— 創建你的測試表

CREATE TABLE dbo.TicketAssigment (
   TicketId     INT NOT NULL,
   AssignedDate DATE NOT NULL,
   DepartmentId INT NOT NULL);

--  truncate table dbo.TicketAssigment;

insert into dbo.TicketAssigment values (1   ,   '1-1-2016'  ,   123 )
insert into dbo.TicketAssigment values (1   ,   '1-4-2016'  ,   456 )
insert into dbo.TicketAssigment values (2   ,   '1-1-2016'  ,   25  )
insert into dbo.TicketAssigment values (2   ,   '1-2-2016'  ,   52  )
insert into dbo.TicketAssigment values (2   ,   '1-4-2016'  ,   25  )

— 查詢以獲得所需的輸出

;with Cte as
(
 select TicketID, 
        min(AssignedDate) minAD, -- This is the min date
        max(AssignedDate) maxAD  -- This is the max date
 from TicketAssigment
 group by TicketID
)
select Cte.TicketID,
      c.d as AssignedDate,

      ( -- Get DeptID
      select top(1) T.departmentID
      from dbo.TicketAssigment as T
      where T.TicketID = cte.TicketID and
            T.AssignedDate <= c.d
      order by T.AssignedDate desc
      ) as DepartmentID
from Cte
 left outer join dbo.Calendar as c
     on c.d between Cte.minAD and Cte.maxAD
   order by Cte.TicketID

引用自：https://dba.stackexchange.com/questions/138672