Adding missing dates with values as 0 using sql

Question

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

There is probably a better way but when I really had to do it in SQL I used to create a table with dates in and do an outer join on it. — idstam
– idstam, Commented Sep 19, 2022 at 12:21

Slava Rozhnev · Accepted Answer · 2022-09-19 12:50:10Z

2

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

answered Sep 19, 2022 at 12:50

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding missing dates with values as 0 using sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related