I need a T-SQL query for calculating time duration for column containing multiple datetime entries against order number and order state.
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) + … )
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;