SQL Server 2019

CREATE TABLE [IncidentActions](
    [Id] [INT] ,
    [IncidentId] [INT] NOT NULL,
    [ActionDate] [DATETIMEOFFSET](7) NOT NULL,
    [Description] [NVARCHAR](MAX) NOT NULL,
    [StatusId] [INT] NOT NULL
);

INSERT INTO [IncidentActions] VALUES
( 51, 18, N'2020-03-10T13:39:27.8621563+00:00', N'This is a demo of the app ops incident management portal', 1 ), 
( 52, 18, N'2020-03-10T13:41:42.4306254+00:00', N'Superfast update we''re on it', 1 ), 
( 53, 18, N'2020-03-10T13:42:19.0766735+00:00', N'Found a workaround', 1 ), 
( 55, 18, N'2020-03-10T13:44:05.7958553+00:00', N'Suspending for now', 2 ), 
( 56, 18, N'2020-03-10T13:44:49.732564+00:00', N'No longer suspended', 1 ), 
( 57, 18, N'2020-03-10T13:45:09.8056202+00:00', N'All sorted', 3 ), 
( 58, 19, N'2020-03-11T14:47:05.6968653+00:00', N'This is just a test', 1 ), 
( 59, 19, N'2020-03-11T14:51:20.4522014+00:00', N'Found workaround and root cause, not yet fixed', 1 ), 
( 60, 19, N'2020-03-11T14:52:34.857061+00:00', N'Networking issues, updates suspended', 2 ), 
( 61, 19, N'2020-03-11T14:54:48.2262037+00:00', N'Network issue resolved, full functionality restored', 3 ), 
( 62, 20, N'2020-03-12T10:49:11.5595048+00:00', N'There is an ongoing issue', 1 ), 
( 63, 20, N'2020-03-12T11:29:37.9376805+00:00', N'This incident is ongoing....', 1 )
;
CREATE TABLE [IncidentStatuses](
    [Id] [int] NOT NULL,
    [Description] [nvarchar](500) NOT NULL
)
;
INSERT INTO [IncidentStatuses] VALUES
( 1, N'OPEN' ), ( 2, N'SUSPENDED' ), ( 3, N'CLOSED' )

15 rows affected

WITH allActions
AS (SELECT IncidentId,
           ActionDate,
           IncidentStatuses.Description,
           ROW_NUMBER() OVER (PARTITION BY IncidentId ORDER BY ActionDate) AS rowNum
    FROM IncidentActions
    INNER JOIN dbo.IncidentStatuses ON IncidentStatuses.Id = IncidentActions.StatusId
    )
,actionPeriods
AS (SELECT firstAction.IncidentId,
          firstAction.Description StartStatus,
          secondAction.Description EndStatus,
          DATEDIFF(SECOND, firstAction.ActionDate, secondAction.ActionDate) SecondsElapsed
    FROM allActions firstAction
    INNER JOIN allActions secondAction ON firstAction.rowNum +1 = secondAction.rowNum --the next action
                                AND firstAction.IncidentId = secondAction.IncidentId --for the same incident
   )
SELECT 
    actionPeriods.IncidentId,
    SUM(CASE WHEN actionPeriods.StartStatus = 'OPEN' THEN actionPeriods.SecondsElapsed ELSE 0 END) SecondsActive,
    SUM(CASE WHEN actionPeriods.StartStatus <> 'OPEN' THEN actionPeriods.SecondsElapsed ELSE 0 END) SecondsInactive,
    SUM(actionPeriods.SecondsElapsed) SecondsElapsed
FROM actionPeriods
 GROUP BY actionPeriods.IncidentId

IncidentId	SecondsActive	SecondsInactive	SecondsElapsed
18	298	44	342
19	329	134	463
20	2426	0	2426

WITH allPeriods AS
 (
   SELECT IncidentId,
      Lead(ActionDate)     Over (PARTITION BY IncidentId ORDER BY ActionDate DESC) AS ActionDate,
      Lead(st.Description) Over (PARTITION BY IncidentId ORDER BY ActionDate DESC) AS StartStatus,
      st.Description AS EndStatus,

CASE -- return NOW if the last row is "open" 
         WHEN Row_Number() Over (PARTITION BY IncidentId ORDER BY ActionDate DESC) = 1
          AND StatusId = 1
         THEN getdate() 
         ELSE ActionDate
      END AS nextDate
   FROM IncidentActions AS act
   JOIN dbo.IncidentStatuses AS st
     ON st.Id = act.StatusId
 ),
elapsed AS
 ( SELECT *,
      DATEDIFF(SECOND, ActionDate, nextDate) AS SecondsElapsed
   FROM allPeriods
 )
SELECT
    IncidentId,
    Sum(CASE WHEN StartStatus =  'OPEN' THEN SecondsElapsed ELSE 0 END) SecondsActive,
    Sum(CASE WHEN StartStatus <> 'OPEN' THEN SecondsElapsed ELSE 0 END) SecondsInactive,
    Sum(SecondsElapsed) SecondsElapsed
FROM elapsed
WHERE ActionDate IS NOT NULL
GROUP BY IncidentId

IncidentId	SecondsActive	SecondsInactive	SecondsElapsed
18	298	44	342
19	329	134	463
20	10366	0	10366