0

I've got one table called "Units_Sold" which has these fields: year, month, week, country, units

and another table called "Dates" which has these other fields: year, week, month

The dates table has 1 entry per week/month/year The Units Sold Table has more than 1 record per week, month, year as it is multiple units sales on a given week

What I'd like is to get the missing units sales weeks BY COUNTRY, this is, all the weeks in which there are no unit sales

I'm not sure how to approach this, if I join the 2 tables I get the weeks in which unit sales is present, that's not an issue, how can I get the inverse by country? that is, all weeks for each country in which there were no sales at all...

0

1 Answer 1

0

Typically using a NOT EXISTS condition is the most efficient way. But you also need to generate the combination of all possible countries and dates through a cross join before finding those that don't exist.

Without a lookup table, this would be something like:

with countries as (
  select distinct country
  from units_sold
)
select c.country, d.year, d.month, d.week
from countries c
  cross join dates d
where not exists (select *
                  from units_sold u
                  where (u.year, u.month, u.week) = (d.year, d.month, d.week))
order by c.country, d.year, d.month, d.week;

If you do have a lookup table with the countries, you can remove the CTE (with countries ...) and replace it with the real table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.