CODEWITHSUNDEEP
sqlsql-servert-sqldateleft-join
nowYouSeeMe
nowYouSeeMe

Reputation: 999

Join back missing date matches

I have the following table:

| variable | varDate    | match |
|----------|------------|-------|
| var1     | 15/02/2020 | 98    |
| var1     | 16/02/2020 | 99    |
| var1     | 17/02/2020 | 97    |
| var2     | 15/02/2020 | 95    |

I would like to populate var2, for the dates 16/02/2020 and 17/02/2020 (those that are missing, and if null, populate with the value 100).

I have a table that contains the last three days in a table, that looks like so:

| date       |
|------------|
| 15/02/2020 |
| 16/02/2020 |
| 17/02/2020 |

My attempt was to use a left join on varDate = date, however this didn't produce the result I expected like below:

| variable | date       | match |
|----------|------------|-------|
| var1     | 15/02/2020 | 98    |
| var1     | 16/02/2020 | 99    |
| var1     | 17/02/2020 | 97    |
| var2     | 15/02/2020 | 95    |
| var2     | 16/02/2020 | 100   |
| var2     | 17/02/2020 | 100   |

Upvotes: 0

Views: 74

Answers (1)

GMB
GMB

Reputation: 222432

Consider:

select
    v.variable,
    d.date,
    coalesce(t.match, 100) match
from (select distinct variable from mytable) v
cross join mydates d 
left join mytable t 
    on  t.varDate = d.date
    and t.variable = v.variable

This works by first cross joining the distinct values of variable available in the table with the table that references the dates (I called it mydates). This generates all possible combinations of dates and variables.

Then, we bring in the original table (I assumed mytable) with a left join. Finally, we use coalesce() to assign a default value for missing matches.

Ideally, you should have a reference table for the variables as well, which would avoid the need for the distinct subquery:

select
    v.variable,
    d.date,
    coalesce(t.match, 100) match
from myvariables v
cross join mydates d 
left join mytable t 
    on  t.varDate = d.date
    and t.variable = v.variable

Upvotes: 2

Related Questions