SQL Server query for calculating time duration for column containing multiple datetime entries against order number and order state

I need a T-SQL query for calculating time duration for column containing multiple datetime entries against order number and order state.

Example:

I need the query to find out total time duration for each order number. Please note there can be multiple entries against order numbers.

For multiple Order number Entries (e.g.C8P0070), Order by event_time_t DESC & Then..

(Duration = (RESUME – HOLD) + (RESUME – HOLD) + … )

Expected Result:

Answer

Assuming the “resume” and “hold” have no duplicates, you can use lead() to get the next value and then aggregate:

select order_number_s,
       sum(datediff(second, event_time_t, next_event_time_t)) as total_duration
from (select t.*,
             lead(event_time_t) over (partition by order_number_s order by event_time_t) as next_event_time_t
      from t
      where order_state_s in ('HOLD', 'RESUME')
     ) t
where order_state_s = 'HOLD'
group by order_number_s;