Compare two rows in SQL Server-CodePudding

Question: I need to select only record with 'O' and also check the 'Actual Time' if record is 'D' then do not consider the record - 'O' output, see my expected output below (new to SQL)

SELECT DISTINCT Record, Actual Time  
FROM app.abc  
WHERE id = 100

Record      Actual Time
-----------------------------------
D           2022-06-13 02:52:00.000
O           2022-06-13 02:52:00.000
O           2022-06-13 05:11:00.000

Expected output:

Record   Actual Time
---------------------------------
O        2022-06-13 05:11:00.000

CodePudding user response：

Okay,

;WITH [X] AS (
SELECT DISTINCT [Record], [Actual Time]  FROM [app].[abc]  WHERE [id]=100 
)
SELECT
            O.[Record],
            O.[Actual Time]
    FROM
            [X] [O]
        LEFT JOIN
            (
            SELECT
                        [Actual Time]
                FROM
                        [X]
                WHERE
                        [Record] = 'D'         
            ) [D]
                ON D.[Actual Time] != O.[Actual Time]
    WHERE
            O.[Record] = 'O';

CodePudding user response：

You want to find O records without a matching D record. That is pretty much the definition of an anti-join. You can do:

select a.*
from abc a
left join abc b on b.actual_time = a.actual_time and b.record = 'D'
where a.record = 'O' and b.record is null

Result:

record  actual_time
------  ---------------------------
O       2022-06-13 05:11:00.0000000

See running example at db<>fiddle.