SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU6) (KB4563110) - 15.0.4053.23 (X64) 
 Jul 25 2020 11:26:55 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE my_table (
  EventCode INTEGER,
  TechCode INTEGER,
  TaskCode VARCHAR(1),
  Time DATETIME
);

INSERT INTO my_table
  (EventCode, TechCode, TaskCode, Time)
VALUES
  ('2021003', '42', 'D', '2021-08-07 13:30'),
  ('2021003', '42', 'E', '2021-08-07 13:30'),
  ('2021003', '42', 'C', '2021-08-07 13:40'),
  ('2021004', '43', 'D', '2021-08-07 13:35'),
  ('2021004', '43', 'A', '2021-08-07 13:37'),
  ('2021004', '43', 'X', '2021-08-07 13:38'),
  ('2021004', '43', 'D', '2021-08-07 13:50'),
  ('2021004', '43', 'E', '2021-08-07 13:50'),
  ('2021004', '43', 'C', '2021-08-07 14:00');
9 rows affected

WITH StartPoints AS (
    SELECT *,
        IsStart = CASE WHEN TaskCode IN ('D','A','E')
             AND LAG(TaskCode, 1, 'C') OVER (PARTITION BY EventCode, TechCode
                 ORDER BY Time) IN ('C', 'X') THEN 1 END
    FROM my_table t
),
Groupings AS (
    SELECT *,
        GroupId = COUNT(IsStart) OVER (PARTITION BY EventCode, TechCode
                  ORDER BY Time ROWS UNBOUNDED PRECEDING)
    FROM StartPoints t
)
SELECT
    EventCode,
    TechCode,
    MIN(Time) Start,
    MAX(Time) Stop
FROM Groupings t
GROUP BY
    EventCode, TechCode, GroupId;


EventCode
TechCode
Start
Stop


2021003
42
2021-08-07 13:30:00.000
2021-08-07 13:40:00.000

2021004
43
2021-08-07 13:35:00.000
2021-08-07 13:38:00.000

2021004
43
2021-08-07 13:50:00.000
2021-08-07 14:00:00.000


Warning: Null value is eliminated by an aggregate or other SET operation.

EventCode	TechCode	Start	Stop
2021003	42	2021-08-07 13:30:00.000	2021-08-07 13:40:00.000
2021004	43	2021-08-07 13:35:00.000	2021-08-07 13:38:00.000
2021004	43	2021-08-07 13:50:00.000	2021-08-07 14:00:00.000