I have a table with following structure. I am using SQL Server 2016

Please note: these status entries will be always in chronological order but one can override the normal flow and jump to any status directly. For example, from Accept we can even jump to Delivered status.

What I am trying to achieve is a result which shows us the time differences between 2 states

For example - expected output is

EntryId   Time_Approve    Time_Process    Time_Dispatch Time_Delivered
7         0               15              23             8

One thing to note in the sample is regarding Approve Status, which is not available in physical table. if its there only consider them in calculations.

I tried using Lag and Lead functions but couldn't achieve the desired result. Please help

CodePudding user response：

First you use conditional aggregation to convert the 4 rows into 4 columns, then use COALESCE to find the time of previous state (it must be done in reverse order). Finally you use DATEDIFF:

WITH cte AS (
    SELECT StatusEntryID
         , t1 = MIN(CASE WHEN Status = 'Accept'     THEN StatusDate END)
         , t2 = MIN(CASE WHEN Status = 'Processed'  THEN StatusDate END)
         , t3 = MIN(CASE WHEN Status = 'Dispatched' THEN StatusDate END)
         , t4 = MIN(CASE WHEN Status = 'Delivered'  THEN StatusDate END)
    FROM t
    GROUP BY StatusEntryID
)
SELECT StatusEntryID
     , Time_Accept    = 0
     , Time_Process   = DATEDIFF(SECOND, t1, t2)
     , Time_Dispatch  = DATEDIFF(SECOND, COALESCE(t2, t1), t3)
     , Time_Delivered = DATEDIFF(SECOND, COALESCE(t3, t2, t1), t4)
FROM cte

All values are in seconds. Converting to hour/minute/seconds is trivial.