Find the cycle time of a ticket number for different intervals

Question

TicketNo    ActionDate                  OldStatus    NewStatus      CycleTime/Sec  
1001        2014-02-14 10:17:05.000     Assigned     InProgress     -
1001        2014-03-05 02:03:44.000     InProgress   Reply          1611999
1001        2014-03-11 10:00:14.000     Reply        Resolved       546990
1002        2015-03-20 04:44:14.000     InProgress   Reply          -
1002        2015-03-21 05:40:02.000     Reply        Resolved       89748

I have to calculate cycle time for every change in status of the ticket.

In the example above, i am trying to calculate the seconds from action date when the ticket is routed from old status to new status.

I tried using ranking function but did not get the output in wanted.

select * ,row_number() over (partition by a.ticketno, a.oldstatus order by a. actiondate ) rn

from Ticketing a

I would really appreciate if anyone can suggest some ideas how to solve this calculation.

Please add you best try to the question. – PM 77-1 Feb 14 '20 at 16:20 — PM 77-1, Feb 14 '20 at 16:20

score 2 · Answer 1 · answered Feb 14 '20 at 19:12

I took Sean's excellent solution and tweaked it to avoid a sort. I'm using a temp table so that I can exclude the temp variable creation/population from the execution plan I'm about to post.

-- Temp table with sample data
IF OBJECT_ID('tempdb..#Something') IS NOT NULL DROP TABLE #Something;
CREATE TABLE #Something
(    TicketNo INT
    , ActionDate DATETIME
    , OldStatus  VARCHAR(50)
    , NewStatus  VARCHAR(50)
);
INSERT #Something VALUES
  (1001, '2014-02-14 10:17:05.000', 'Assigned', 'InProgress')
, (1001, '2014-03-05 02:03:44.000', 'InProgress', 'Reply')
, (1001, '2014-03-11 10:00:14.000', 'Reply', 'Resolved')
, (1002, '2015-03-20 04:44:14.000', 'InProgress', 'Reply')
, (1002, '2015-03-21 05:40:02.000', 'Reply', 'Resolved')

-- TOP (1) Solution
SELECT s.TicketNo, s.ActionDate, s.OldStatus, s.NewStatus,
       CycleTimeSeconds = DATEDIFF(SECOND, MyLag.ActionDate, s.ActionDate)
FROM   #Something AS s
OUTER APPLY
(
    SELECT TOP (1) ActionDate
    FROM     #Something s2 
    WHERE    s2.TicketNo   = s.TicketNo 
    AND      s2.ActionDate < s.ActionDate
    ORDER BY s2.ActionDate DESC
) AS MyLag;

-- Using MAX instead of TOP (1) to avoid a DESC sort operation
SELECT s.TicketNo, s.ActionDate, s.OldStatus, s.NewStatus,
       CycleTimeSeconds = DATEDIFF(SECOND, MyLag.ActionDate, s.ActionDate)
FROM #Something AS s
CROSS APPLY
(
    SELECT ActionDate = MAX(ActionDate)
    FROM   #Something s2 
    WHERE  s2.TicketNo   = s.TicketNo 
    AND    s2.ActionDate < s.ActionDate 
) AS MyLag;

Since the subquery is only evaluating one column we can leverage an Aggregate function without a GROUP BY. Because I'm using an Aggregate (MAX) I'll always get a row back which is why I changed the OUTER APPLY to CROSS APPLY. Nothing wrong with OUTER APPLY but changing it removes scalar operator from this execution plan - no performance gain, just a cleaner execution plan.

There is a very big additional benefit to this approach: It's not only avoiding a sort, we're avoiding a DESCending sort. If there were an index on ActionDate the optimizer could leverage it to avoid a DESCending sort by performing a *ordered-backward scan. Adding a UNIQUE constraint on ActionDate to the original temp variable (something I don't suggest but it works for this example) run a TOP (1) query sorted by ActionDate DESC.

declare @Something table
(
    TicketNo int
    , ActionDate datetime UNIQUE
    , OldStatus varchar(50)
    , NewStatus varchar(50)
)

insert @Something values
  (1001, '2014-02-14 10:17:05.000', 'Assigned', 'InProgress')
, (1001, '2014-03-05 02:03:44.000', 'InProgress', 'Reply')
, (1001, '2014-03-11 10:00:14.000', 'Reply', 'Resolved')
, (1002, '2015-03-20 04:44:14.000', 'InProgress', 'Reply')
, (1002, '2015-03-21 05:40:02.000', 'Reply', 'Resolved')

SELECT TOP (1) ActionDate
FROM     @Something AS s2 
ORDER BY s2.ActionDate DESC;

Note the backward scan:

Backward scans kill parallelism. Itzik Ben-Gan discusses that here: Avoiding a Sort with Descending Order.

One difference between ordered-forward and ordered-backward scans is that the former can potentially use parallelism, whereas the latter doesn’t currently have an implementation with parallelism in the storage engine

Here APPLY is allowing us a sort-free operation with parallel processing in the optimizer's back pocket if needed. Just more evidence that APPLY is awesome... not that any further evidence was needed ;)

Well done as always Alan. If I had put any cycles into it I would have come to the same (or similar) solution. Pretty sure that LAG makes this obsolete anyway. So if the OP was using a supported version we wouldn't be having this discussion. :) — Sean Lange, Feb 14 '20 at 19:23

Sean Lange · Accepted Answer · 2020-02-14T16:56:10.517

1

The easiest way to do this is using LAG. You can read more about the LAG function here. https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15

Here is a fully functioning example.

declare @Something table
(
    TicketNo int
    , ActionDate datetime
    , OldStatus varchar(50)
    , NewStatus varchar(50)
)

insert @Something values
(1001, '2014-02-14 10:17:05.000', 'Assigned', 'InProgress')
, (1001, '2014-03-05 02:03:44.000', 'InProgress', 'Reply')
, (1001, '2014-03-11 10:00:14.000', 'Reply', 'Resolved')
, (1002, '2015-03-20 04:44:14.000', 'InProgress', 'Reply')
, (1002, '2015-03-21 05:40:02.000', 'Reply', 'Resolved')

select s.*
    , CycleTimeSeconds = datediff(second, lag(ActionDate, 1) over(partition by TicketNo order by ActionDate), s.ActionDate)
from @Something s

--EDIT--

Here is a version that will work with Sql Server 2008 (You really should consider upgrading since that version is no longer supported).

select s.*
    , CycleTimeSeconds = datediff(second, MyLag.ActionDate, s.ActionDate)
from @Something s
outer apply 
(
    select top 1 ActionDate 
    from @Something s2 
    where s2.TicketNo = s.TicketNo 
        and s2.ActionDate < s.ActionDate 
    order by ActionDate desc
) MyLag

edited Feb 14 '20 at 16:56

answered Feb 14 '20 at 16:29

Sean Lange

33,028
3
25
40

. . I don't know if the SQL Server 2008 tag was on the question when you answered it, but it does not support `lag()`.. – Gordon Linoff Feb 14 '20 at 16:50
@GordonLinoff oh dear...I didn't notice it was tagged 2008. I foolishly mostly assume that people have upgraded since that version is no longer supported. – Sean Lange Feb 14 '20 at 16:51
1

Updated with a version that works in sql server 2008. – Sean Lange Feb 14 '20 at 16:56
1

Hey @SeanLange - I just added a tweaked version of your answer - I think you'll like. – Alan Burstein Feb 14 '20 at 19:17
Thanks guys for solution. – droptable007 Feb 18 '20 at 19:16

Find the cycle time of a ticket number for different intervals

2 Answers2