CODEWITHSUNDEEP
sqlpresto
Alex Madrzyk
Alex Madrzyk

Reputation: 11

How to fill in missing dates and keep running sum of values using SQL SELECT?

There's a table with values for certain dates:

   date    |    value
-------------------------
2019-01-01 |     50
2019-01-03 |     100
2019-01-06 |     150
2019-01-08 |     20

But what I'm hoping to do is create a time series with a running sum from the first to the last date so:

   date    |    value
-------------------------
2019-01-01 |     50    
2019-01-02 |     50
2019-01-03 |     150   (+100)
2019-01-04 |     150
2019-01-05 |     150
2019-01-06 |     300   (+150)
2019-01-07 |     300   
2019-01-08 |     320   (+20)

The only constraint is that all tables are read only, so I can only query them and not modify them.

Does anyone know if this might be possible?

Upvotes: 0

Views: 498

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You can generate the dates using arrays and unnest(). The rest is a left join and cumulative sum:

select dates.dte,
       sum(t.value) over (order by dates.dte)
from (values (sequence(from_iso86001_date('2019-01-01'),
                       from_iso86001_date('2019-01-08'),
                       interval '1' day
                      )
             )
     ) v(date_array) cross join
     unnest(v.date_array) dates(dte) left join
     t
     on t.date = dates.dte;

Upvotes: 1

Related Questions