Picture help desk tickets that gets transfered between departments. We want to know what the department is at the end of the day for each ticket for each day that the ticket is open. The table contains the last department for each ticket for each day it is open on which there is a change in the department (including a row for the date the ticket was initially opened and the date it was closed). The data table looks like this:
CREATE TABLE TicketAssigment ( TicketId INT NOT NULL, AssignedDate DATE NOT NULL, DepartmentId INT NOT NULL);
What I need is to fill in any missing dates for each TicketId, using the DepartmentId from the previous TicketAssigment row ordered by Date.
If I have TicketAssigment rows like this:
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
I want this output:
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
This looks like it might be close to what I need, but I haven't had the patience to let it finish, and the estimated plan cost has 6 digits:
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;
I suspect there is a way to do this using LAG and a window frame, but I haven't quite figured it out. What is a more efficient way of meeting the requirement?