How can I filter-out rows based on values in preceding and following rows?

Question

I'm looking for a way to filter out the data within a range in the MS SQL query. In this case "Test2" is my starting point and "Test4" is my ending point. I want to filter out "Test1" as long it is between "Test2" and "Test4" rows. If it's outside then I want to keep it. And also I want to keep "Test3" row.

Actual Result:

DateTime	Message
2022-09-09 00: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

Expected Result:

DateTime	Message
2022-09-09 00:00	Test1
2022-09-09 01:00	Test2
2022-09-09 04:00	Test3
2022-09-09 05:00	Test4

2nd example

Actual Result:

DateTime	Message
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

Expected Result:

DateTime	Message
2022-09-08 11:00	Test3
2022-09-08 12:00	Test1
2022-09-09 01:00	Test2
2022-09-09 04:00	Test3
2022-09-09 05:00	Test4

3rd example

Actual Result:

DateTime	Message
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
2022-09-09 06:00	Test1

Expected Result:

DateTime	Message
2022-09-08 11:00	Test3
2022-09-08 12:00	Test1
2022-09-09 01:00	Test2
2022-09-09 04:00	Test3
2022-09-09 05:00	Test4
2022-09-09 06:00	Test1

Any help wil be appreciated.

SOLVED:

I managed to solve the problem thanks to the query provided by @Ahmed. Thanks to everyone that took the time to solve this.

Thank you!

Answer 1

You may try the following:

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

This query will filter out all 'Test1' values between 'Test2' and 'Test4', but not between 'Test4' and 'Test2' and that's according to your requirement; "'Test2' is my starting point and 'Test4' is my ending point".

See a demo.

Answer 2

select  DateTime
       ,last_group as Message
from    (
        select *
               ,case when Message = 'Test1' and start_end = 1 then null else Message end as last_group 
        from   (
                select * 
                       ,count(case when Message = 'Test2' then 1 when Message = 'Test4' then 1 end) over(order by DateTime) as start_end
                from t
               ) t
         ) t
where    last_group is not null

DateTime	Message
2022-09-09 00: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
2022-09-09 06:00:00.000	Test1

Fiddle

Answer 3

I tried this query I can able to execute it:,

WITH cte AS (
    SELECT
        datetime,
        message
    FROM
        mytable
    WHERE
        datetime >= ( SELECT TOP 1 datetime d1 FROM mytable WHERE message = 'Test2' ) 
        AND
        datetime <= ( SELECT TOP 1 datetime d2 FROM mytable WHERE message = 'Test4' )
        AND
        message = 'Test1'
)
SELECT
    mt.datetime,
    mt.message
FROM
    mytable mt
    LEFT OUTER JOIN cte ON
        mt.datetime = cte.datetime
        AND
        mt.message = cte.message
WHERE
    cte.datetime IS NULL
    AND
    cte.message IS NULL

(I am new to stack overflow; I would like to receive feedback if any errors in the code or if it isn't the right way).