Calculating time with datetime values

I have two tables where one has tasks, other has actions. In the task table I have tasks that ha ID, start date (when the task was created) and end date (when the task was closed). In the action table I have actions that were done in the task table so it has ID, task_ID, action_ID and time of the action. I would like to calculate the time how long it took to close the task (end date – start date) but since there is a possibility to pause the task then I would like to exclude the paused time. For example:

Action table:

ID      TASK_ID   ACTION_ID          TIME  
 1       2593         1              2020-03-25 07:05:48.000           
 2       2593         11             2020-03-25 11:05:48.000     
 3       2593         22             2020-03-25 20:05:48.000     
 4       2593         11             2020-03-26 07:05:48.000     
 5       2593         22             2020-03-27 07:04:31.000
 6       2593         2              2020-03-27 15:04:31.000    

Task ID refers to the tasks table. Action ID 1 means task was opened, 11 means task was paused, 22 means pause was cancelled and 2 means task was completed/closed. So in the example the task was paused two times and I would like to calculate the time from the start (ID 1) to the end (ID 2) but not include the paused times (times between ID 11 and 22).

What is the best way to do this?

Thank you.

Answer

Assuming the values are properly interleaved, you can use lead() to get the next value. Then, you just want to add the times for actions 1 and 22.

select t.task_id,
       sum(datediff(second, time, next_time)) as total_time
from (select t.*, lead(time) over (partition by task_id order by time) as next_time
      from t
      where action_id in (1, 11, 22, 2)
     ) t
where action_id in (1, 22)
group by task_id;

Here is a db<>fiddle.