How to make projections with future dates using Redshift

Question

I currently have a table called quantities with the following data:

+------+----------+----------+
| item | end_date | quantity |
+------+----------+----------+
| 1    | 26/11/17 | 100      |
+------+----------+----------+
| 2    | 28/11/17 | 300      |
+------+----------+----------+
| 3    | 30/11/17 | 500      |
+------+----------+----------+

I want to query it so I can get this result:

+--------+-------+
| date   | total |
+--------+-------+
| 26-Nov | 900   |
+--------+-------+
| 27-Nov | 800   |
+--------+-------+
| 28-Nov | 800   |
+--------+-------+
| 29-Nov | 500   |
+--------+-------+
| 30-Nov | 500   |
+--------+-------+

How would this query look like? Many thanks!

Please note:

The above output is the result of suming the "quantity" field of each item when today's date is =< end_date. See below that today is the 26th of november.

+--------+-----+-----+-----+-------+
| date   | 1   | 2   | 3   | total |
+--------+-----+-----+-----+-------+
| 26-Nov | 100 | 300 | 500 | 900   |
+--------+-----+-----+-----+-------+
| 27-Nov | -   | 300 | 500 | 800   |
+--------+-----+-----+-----+-------+
| 28-Nov | -   | 300 | 500 | 800   |
+--------+-----+-----+-----+-------+
| 29-Nov | -   | -   | 500 | 500   |
+--------+-----+-----+-----+-------+
| 30-Nov | -   | -   | 500 | 500   |
+--------+-----+-----+-----+-------+

I'd need the query to always give an output with the following date range:

• start_date=current_date
• end_date=the latest end_date from the list of items

I am using PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1499

Answer 1

t=# create table so (item serial, end_date date, quantity int);
CREATE TABLE
t=# set datestyle TO DMY;
SET
t=# insert into so(end_date,quantity) values('26-11-2017',100),('28-11-2017', 300), ('30-11-2017', 500);
INSERT 0 3

then, we create a view with a date series that goes into the future starting from today by using a table with a sequential id column:

CREATE VIEW future_dates AS
SELECT (getdate()::date -1 + id)::date as future_dates
FROM app_data.table_with_sequential_id
ORDER BY  future_dates

N.b. We need to create the above view because generate_series is not fully supported in Redshift.

and lastly select itself:

t=# select gs::date,  sum(quantity) over (order by gs desc rows unbounded preceding)
from future_dates gs
left outer join so on so.end_date = gs
order by gs ;
     gs     | sum
------------+-----
 2017-11-26 | 900
 2017-11-27 | 800
 2017-11-28 | 800
 2017-11-29 | 500
 2017-11-30 | 500
(5 rows)