Fill in missing dates with data value from previous populated date for group

Question

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?

Answer 1

Use LEAD() to get the next row within the TicketId partition. Then join to a Calendar table to get all the dates between.

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)
;

All kinds of ways to get a Calendar table...

Answer 2

This is a quick way of doing (I have not tested for performance or scalablity)

-- create Calendar table

-- 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 your test table

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  )

--- Query to get desired output

;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

Answer 3

The below SQL goes back 60 days and look for missing rates and will populate it with the previous days exchange rate

USD to CAD 1.35
ExchangeRates : Exchange rate example 1.35
REFRATEDEF : CANADAEXCHANGE
PRICESOURCES : Field used to define which REFRATEDEF needs to be updated

WITH MISSING_DAYS AS
(
select distinct missing_date2.ReferenceDate  pricedate , ExchangeRates.refrateik ,ExchangeRatesDef.REFRATE refratename,
last_value( (
Select ExchangeRates2.interest from ExchangeRates ExchangeRates2 where trunc(ExchangeRates2.pricedate)  =  trunc(missing_date2.ReferenceDate) and ExchangeRates2.refrateik = ExchangeRates.refrateik
) ignore nulls) over ( partition by ExchangeRates.refrateik order by ExchangeRates.refrateik , missing_date2.ReferenceDate ) Yield ,

decode( (
Select ExchangeRates2.interest from ExchangeRates ExchangeRates2 where trunc(ExchangeRates2.pricedate)  =  trunc(missing_date2.ReferenceDate) and ExchangeRates2.refrateik = ExchangeRates.refrateik
),null, 'Y', 'N') as MissingRate
from
( SELECT SYSDATE- 60 + LEVEL ReferenceDate FROM DUAL CONNECT BY LEVEL <= 60 order by 1 ) missing_date2,
ExchangeRates ,
ExchangeRatesDef,
PRICESOURCES
WHERE upper(PRICESOURCES.prisrc) ='UPDATERATE'
AND ExchangeRatesDef.prisrcik = PRICESOURCES.prisrcik
AND ExchangeRatesDef.refrateik = ExchangeRates.refrateik
ORDER BY refrateik, pricedate
)
SELECT * FROM MISSING_DAYS WHERE MISSING_DAYS.MissingRate = 'Y' AND  NVL( MISSING_DAYS.YIELD, 999999999 ) <> 999999999