[Solved] SQL query to get time spent on specific statuses based on single datetime column [closed]

by


with SQL Server you can use those very usefull windowed functions LEAD and FIRST_VALUE :

select *
    ,[duration(sec)] = DATEDIFF(SECOND
                                ,ticketTime
                                ,LEAD(ticketTime,1,ticketTime)over(partition by ticketNumber order by ticketTime)
                                )

    ,[cumulative duration(sec)] = DATEDIFF( SECOND
                            , FIRST_VALUE(ticketTime)over(partition by ticketNumber order by ticketTime)
                            , ticketTime)
from (values
     (1,cast('20211101 10:00:01' as datetime))
    ,(1,'20211101 10:00:33')
    ,(1,'20211101 10:01:59')
 )T(ticketNumber,ticketTime)
ticketNumber ticketTime duration(sec) cumulative duration(sec)
1 2021-11-01 10:00:01.000 32 0
1 2021-11-01 10:00:33.000 86 32
1 2021-11-01 10:01:59.000 0 118

solved SQL query to get time spent on specific statuses based on single datetime column [closed]