Skip to content
Advertisement

Find the difference between 1 column depending on date

When I run this:

SELECT NAME FROM T1
WHERE _LOAD_DATETIME::date = '2022-01-31'

I see 62 rows

but when I do

SELECT NAME FROM T1
WHERE _LOAD_DATETIME::date = '2022-02-01'

I see 59

I want to see what NAME’s are missing when it ran for _LOAD_DATETIME::date = ‘2022-02-01’

I thought this would work but it doesn’t:

SELECT NAME FROM table
WHERE _LOAD_DATETIME::date = '2022-02-01' 
AND NOT EXISTS (
SELECT NAME FROM 
table
WHERE _LOAD_DATETIME::date = '2022-01-31')

Advertisement

Answer

You have to use MINUS for your purposes:

SELECT NAME FROM T1
WHERE _LOAD_DATETIME::date = '2022-01-31'
MINUS
SELECT NAME FROM T1
WHERE _LOAD_DATETIME::date = '2022-02-01'

If we are talking about PostgreSQL, you have to use EXCEPT instead of MINUS.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement