Função de janela para obter pares de registros e calcular a diferença de tempo

Você precisa de algo como

-- get previous status, use -1 value if no previous record (because NULL value needs more complex condition in cte2)
WITH cte1 AS (SELECT Event_Type, 
                     Event_Timestamp, 
                     Is_Active, 
                     COALESCE(LAG(Is_Active) OVER (PARTITION BY Event_Type 
                                                   ORDER BY Event_Timestamp ASC), -1) prev_event
              FROM datatable),
     -- remove records where status not altered
     cte2 AS (SELECT Event_Type, 
                     Event_Timestamp, 
                     Is_Active
              FROM cte1
              WHERE Is_Active != prev_event)
-- get data we need
SELECT Event_Type, 
       Event_Timestamp Begin_Timestamp, 
       LEAD(Event_Timestamp) OVER (PARTITION BY Event_Type 
                                   ORDER BY Event_Timestamp ASC) End_Timestamp, 
       LEAD(Event_Timestamp) OVER (PARTITION BY Event_Type 
                                   ORDER BY Event_Timestamp ASC) - Event_Timestamp Duration
FROM cte2
WHERE Is_Active = 1

Claro que você pode combiná-lo na consulta usando um CTE que calcula todos os valores da janela. O longo caminho acima é usado para explicar o algoritmo.

Dados os dados

SELECT x.Event_Type, x.Event_Timestamp, x.Is_Active
INTO #Data
FROM (VALUES 
  ('A', CONVERT(DATETIME, '2010-10-01 00:00:00'), 1),
  ('B', CONVERT(DATETIME, '2010-10-01 00:00:01'), 1),
  ('A', CONVERT(DATETIME, '2010-10-01 00:00:02'), 0),
  ('D', CONVERT(DATETIME, '2010-10-01 00:00:03'), 1),
  ('B', CONVERT(DATETIME, '2010-10-01 00:00:04'), 0),
  ('C', CONVERT(DATETIME, '2010-10-01 00:00:05'), 1),
  ('A', CONVERT(DATETIME, '2010-10-01 00:00:06'), 1),
  ('A', CONVERT(DATETIME, '2010-10-01 00:00:07'), 1),
  ('A', CONVERT(DATETIME, '2010-10-01 00:00:08'), 0)
) x (Event_Type, Event_Timestamp, Is_Active);

Seu resultado usará alguns estágios para serem calculados (cada um em um CTE):

WITH cte_First
AS
(
  SELECT d.Event_Type,
         d.Event_Timestamp,
         d.Is_Active,
         -- Get the active state of the previous row ordered by the timestamp and partitioned by the event type
         Lag(d.Is_Active, 1, NULL) OVER(PARTITION BY d.Event_Type ORDER BY d.Event_Timestamp) AS Prev_Active
  FROM #Data d
), cte_activity
AS
(
  SELECT Event_Type,
         Event_Timestamp,
         Is_Active,
         Prev_Active,
         -- check if the event is a start, an end, or can just be ignored because of the same value
         CASE 
           WHEN Is_Active = Prev_Active THEN 0 -- same value
           WHEN Is_Active = 1 THEN 1 -- start
           WHEN Is_Active = 0 THEN 2 -- end
         END AS start_definition
  FROM cte_First
), cte_start_end
AS
(
  SELECT Event_Type,
         Event_Timestamp,
         Is_Active,
         Prev_Active,
         start_definition,
         -- get the event timestamp from the next row 
         -- (because we only have the start and the end events this will be the end timestamp
         -- for the start row and null for the end row)
         LEAD(Event_Timestamp, 1, NULL) OVER(PARTITION BY Event_Type ORDER BY Event_Timestamp) AS End_Timestamp
  FROM cte_activity
  WHERE start_definition > 0
)
SELECT Event_Type,
       Event_Timestamp,
       End_Timestamp,
       -- calculate the difference in seconds
       DATEDIFF(SECOND, Event_Timestamp, End_Timestamp) AS Duration_Seconds
FROM cte_start_end
-- include only the start rows
WHERE start_definition = 1
ORDER BY Event_Timestamp

Se seus dados também contiverem mais de um evento final, você deverá ajustar o reconhecimento do evento final na instrução case. Isso produzirá o resultado que você esperava em seu post:

Event_Type Event_Timestamp         End_Timestamp           Duration_Seconds
---------- ----------------------- ----------------------- ----------------
A          2010-10-01 00:00:00.000 2010-10-01 00:00:02.000 2
B          2010-10-01 00:00:01.000 2010-10-01 00:00:04.000 3
D          2010-10-01 00:00:03.000 NULL                    NULL
C          2010-10-01 00:00:05.000 NULL                    NULL
A          2010-10-01 00:00:06.000 2010-10-01 00:00:08.000 2

(5 rows affected)

create table #temp(Event_Type varchar(50), Event_Timestamp  datetime, Is_Active bit)
insert into #temp values
 ('A','2010-10-01 00:00:00',1)
,('B','2010-10-01 00:00:01',1)
,('A','2010-10-01 00:00:02',0)
,('D','2010-10-01 00:00:03',1)
,('B','2010-10-01 00:00:04',0)
,('C','2010-10-01 00:00:05',1)
,('A','2010-10-01 00:00:06',1)
,('A','2010-10-01 00:00:07',1)
,('A','2010-10-01 00:00:08',0)
,('A','2010-10-01 00:00:09',1);

;

WITH CTE
AS (
    SELECT 
        a.Event_Type
        ,A.Event_Timestamp AS Begin_Timestamp
        ,b.Event_Timestamp AS End_Timestamp
        ,datediff(second, a.Event_Timestamp, b.Event_Timestamp) Duration
    FROM #temp A
    OUTER APPLY (
        SELECT TOP 1 
            b.Event_Type
            ,b.Event_Timestamp
        FROM #temp B
        WHERE a.Event_Type = b.Event_Type
            AND b.Is_Active = 0
            AND b.Event_Timestamp > a.Event_Timestamp
        ORDER BY b.Event_Timestamp
        ) b
    WHERE a.is_active = 1
    )
SELECT Event_Type
,Begin_Timestamp
,End_Timestamp
,Duration
FROM cte A
WHERE NOT EXISTS (
        SELECT 1
        FROM cte B
        WHERE a.Event_Type = b.Event_Type
            AND b.Begin_Timestamp < a.Begin_Timestamp
            AND a.Begin_Timestamp BETWEEN b.Begin_Timestamp
                AND b.end_Timestamp
        )


Drop TABLE #temp

Não há necessidade de nenhum Window Function.

Você pode testar meu script com dados de amostra diferentes e também me fornecer um cenário para teste de desempenho.

Na verdade, você deve fornecer Real Table Schema.