15

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?

3 Answers 3

16

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...

4

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

enter image description here

1
  • Thanks for this! The estimated execution plan shows a result set of 25 billion rows, so we're going to renegotiate the reporting requirement (which is currently to report on every day for every ticket for the past year). I'm hoping we can show the last DepartmentId for each ticket and show the detail of DepartmentId by day for a single selected ticket upon request. Commented May 17, 2016 at 18:32
-1

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
2
  • This seems to be an answer to a different question than the one I asked. Commented May 16, 2023 at 21:57
  • I saw 'Fill in missing dates with data value' .But its fine , i will delete and create a new topic Commented May 18, 2023 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.