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.

Answer 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