NOT IN/ NOT EXISTS || I am trying to get the IDs missing in a specific date. The table now tracks date and ID if manifested only

Question

I have a table with Date, ID and Volume such as in below:

Date	ID	Volume
23/11/22	999	10
23/11/22	888	10
23/11/22	777	10
22/11/22	888	10
22/11/22	777	10

I would like the ID to appear in the ID column in 22/11/22 even though it manifested 0 volume and to create a flag such as below telling Day over Day (DoD) which ones are "Not Active":

Date	ID	Volume	Status
23/11/22	999	10	Active
23/11/22	888	10	Active
23/11/22	777	10	Active
22/11/22	999	0	Inactive
22/11/22	888	10	Active
22/11/22	777	10	Active

I attempted the following but return 0 rows and I do not know what to do next..

` select distinct csmp.date csmp.mcid as id ,count(*) as volume

from volume csmp

where id NOT IN
       (select csmp.mcid
        from calc.swa_manifested_packages csmp               
        GROUP BY csmp.mc
        )

GROUP BY
csmp.mcid`

thank you all!

score 0 · Answer 1 · answered Nov 23 '22 at 13:30

0

try

select 
    a.Date
    ,b.id
    ,coalesce(c.volume,0) volume
    ,coalesce(c.status,'Inactive') volume
from 
(Select distinct Date from volume) a
cross join (Select distinct id from volume) b
left join volume c
on a.date=b.date
and b.id=c.id

Basically we create all combinations of ID and DATE, and left join them on the volume table.

answered Nov 23 '22 at 13:30

Søren Kongstad

1,405
9
14

Hi, however I do not have the c.status column, I would need to create. Would I just use a case when there? – cheerful Nov 24 '22 at 10:26

NOT IN/ NOT EXISTS || I am trying to get the IDs missing in a specific date. The table now tracks date and ID if manifested only

1 Answers1