By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table table_name([DateTime] DATETIME, Message VARCHAR(50));
Insert Into table_name Values('2022-09-08 11:00','Test3'),
('2022-09-08 12:00','Test1'),
('2022-09-09 01:00','Test2'),
('2022-09-09 02:00','Test1'),
('2022-09-09 03:00','Test1'),
('2022-09-09 04:00','Test3'),
('2022-09-09 05:00','Test4');
7 rows affected
WITH CTE AS
(
SELECT D.[DateTime], D.Message, SUM(D.F) OVER (ORDER BY [DateTime]) FLAG
FROM
(
SELECT *,CASE WHEN Message ='Test2' OR Message='Test4' THEN 1 ELSE 0 END AS F
FROM table_name
) D
)
SELECT [DateTime], Message FROM CTE T
WHERE (
NOT EXISTS(SELECT 1 FROM CTE D WHERE D.MESSAGE='Test2' AND D.FLAG=T.FLAG)
AND NOT EXISTS(SELECT 1 FROM CTE D WHERE D.MESSAGE='Test4' AND D.FLAG=T.FLAG+1)
AND T.MESSAGE='Test1'
)
OR T.MESSAGE <> 'Test1'
OR T.FLAG = 0
| DateTime | Message |
|---|---|
| 2022-09-08 11:00:00.000 | Test3 |
| 2022-09-08 12:00:00.000 | Test1 |
| 2022-09-09 01:00:00.000 | Test2 |
| 2022-09-09 04:00:00.000 | Test3 |
| 2022-09-09 05:00:00.000 | Test4 |