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:

Expected Result:

Another example

Actual Result:

Expected Result:

Any help wil be appreciated.

Thank you!

CodePudding user response：

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).

CodePudding user response：

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

Fiddle