Adding missing dates with values as 0 using sql

Karan_009 0
Viewed 32

Input Data:

+---+---------------+-------------+
|   | checkout_date | item_demand |
+---+---------------+-------------+
| 0 | 2022-08-02    |           1 |
| 1 | 2022-08-05    |           2 |
| 2 | 2022-08-07    |           1 |
| 3 | 2022-08-08    |           1 |
| 4 | 2022-08-09    |           1 |
| 5 | 2022-08-12    |           2 |
+---+---------------+-------------+

Output Data :

+---+---------------+-------------+
|   | checkout_date | item_demand |
+---+---------------+-------------+
| 0 | 2022-08-02    |           1 |
| 1 | 2022-08-03    |           0 |
| 1 | 2022-08-04    |           0 |
| 1 | 2022-08-05    |           2 |
| 1 | 2022-08-06    |           0 |
| 2 | 2022-08-07    |           1 |
| 3 | 2022-08-08    |           1 |
| 4 | 2022-08-09    |           1 |
| 1 | 2022-08-10    |           0 |
| 1 | 2022-08-11    |           0 |
| 5 | 2022-08-12    |           2 |
+---+---------------+-------------+

Filling the Dates with 0 in item demand, how to achieve this, I'm new to SQL

date sql postgresql
1 Answers

It's not so clear how calculated first column, but except this you can use generate_series with left join real data:

with dates as (
  select generate_series(
      (select min(checkout_date) from t),
      (select max(checkout_date) from t),
      '1 day'
  ) date
) 
select 
    date,
    coalesce(item_demand, 0) item_demand
from dates left join t on date = checkout_date

online sql editor

Slava Rozhnev0
Related