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

Question

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?

Gordon Linoff · Accepted Answer · 2019-12-06 20:21:24Z

1

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;

answered Dec 6, 2019 at 20:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

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

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