sqlsql-servercorrelated-subqueryin-subquery

SQL to filter the data from inner query based on 2 conditions


I have the following table named TableB:

Datetime Partid
2022-12-24T15:20:00.782Z Part-A
2022-12-24T15:21:00.782Z Part-B
2022-12-24T15:22:00.782Z Part-B
2022-12-24T15:23:00.782Z Part-A
2022-12-24T15:24:00.782Z Part-B
2022-12-24T15:25:00.782Z Part-A

I have another table named TableA:

Datetime Partid Customer ID
2022-12-24T15:20:00.782Z Part-A CUSTOMER-A
2022-12-24T15:20:00.782Z Part-B CUSTOMER-B
2022-12-24T15:20:00.782Z Part-B CUSTOMER-C
2022-12-24T15:20:00.782Z Part-A CUSTOMER-D
2022-12-24T15:21:00.782Z Part-D CUSTOMER-E
2022-12-24T15:21:00.782Z Part-A CUSTOMER-F
2022-12-24T15:21:00.782Z Part-B CUSTOMER-G
2022-12-24T15:21:00.782Z Part-B CUSTOMER-H
2022-12-24T15:22:00.782Z Part-B CUSTOMER-I
2022-12-24T15:22:00.782Z Part-E CUSTOMER-J
2022-12-24T15:22:00.782Z Part-F CUSTOMER-K
2022-12-24T15:22:00.782Z Part-D CUSTOMER-L
2022-12-24T15:22:00.782Z Part-A CUSTOMER-M
2022-12-24T15:23:00.782Z Part-B CUSTOMER-N
2022-12-24T15:23:00.782Z Part-C CUSTOMER-O
2022-12-24T15:23:00.782Z Part-A CUSTOMER-P
2022-12-24T15:23:00.782Z Part-B CUSTOMER-Q
2022-12-24T15:23:00.782Z Part-E CUSTOMER-R

I would like to exclude the data present in TableA based on Partid AND Datetime in TableB.

I want the list of CustomerID from TableA based on ONLY those Part-A & Part-B Partid's which ONLY fall between DateTime 2022-12-24T15:20:00.782Z and 2022-12-22T15:22:00.782Z in TableB.

The desired output is:

Datetime Partid Customer ID
2022-12-24T15:21:00.782Z Part-D CUSTOMER-E
2022-12-24T15:22:00.782Z Part-E CUSTOMER-J
2022-12-24T15:22:00.782Z Part-F CUSTOMER-K
2022-12-24T15:22:00.782Z Part-D CUSTOMER-L
2022-12-24T15:23:00.782Z Part-B CUSTOMER-N
2022-12-24T15:23:00.782Z Part-C CUSTOMER-O
2022-12-24T15:23:00.782Z Part-A CUSTOMER-P
2022-12-24T15:23:00.782Z Part-B CUSTOMER-Q
2022-12-24T15:23:00.782Z Part-E CUSTOMER-R

Solution

  • WITH TableCTE AS (
    SELECT DISTINCT a.*,b.Partid as 'BId'
    FROM #TableA a
    LEFT JOIN #TableB b ON a.Partid = b.Partid AND a.Datetime=b.Datetime
    )
    
    Select * from TableCTE where Bid is NULL