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