Conditional grouping by date

Question

I have a bit of a problem with this.

I have two tables itemsandstocks

items
id | name 
1  | item_1    
2  | item_2    

stocks
id | item_id | quantity | expired_on
1  |    1    |    5     |  2015-11-12
2  |    1    |    5     |  2015-11-13
3  |    2    |    5     |  2015-11-12
4  |    2    |    5     |  2015-11-14

I want to be able to get a large table, grouped by date, and for each date, group by item_id and show the amount of the quantity that has not expired.

result
date        | item_id | unexpired 
2015-11-11  |    1    |    10     
2015-11-11  |    2    |    10     
2015-11-12  |    1    |    5     
2015-11-12  |    2    |    5     
2015-11-13  |    1    |    0     
2015-11-13  |    2    |    5     
2015-11-14  |    1    |    0     
2015-11-14  |    2    |    0

I can get the result if it is only one day

SELECT 
  items.id, SUM(stocks.quantity) as unexpired
FROM 
  items LEFT OUTER JOIN stocks 
  ON items.id = stocks.item_id
WHERE 
  stocks.expired_on > '2015-11-11'
GROUP BY
  items.id, stocks.quantity

I searched around, found something called DatePart, but that doesn't seem like what I need.

Answer 1

Using convenient pressing from booleanto integer, which gives 0, 1 or null, summarize the remaining only

select
    to_char(d, 'YYYY-MM-DD') as date,
    item_id, 
    sum(quantity * (expired_on > d)::int) as unexpired
from
    stocks
    cross join 
    generate_series(
        '2015-11-11'::date, '2015-11-14', '1 day'
    ) d(d)
group by 1, 2
order by 1, 2
;
    date    | item_id | unexpired 
------------+---------+-----------
 2015-11-11 |       1 |        10
 2015-11-11 |       2 |        10
 2015-11-12 |       1 |         5
 2015-11-12 |       2 |         5
 2015-11-13 |       1 |         0
 2015-11-13 |       2 |         5
 2015-11-14 |       1 |         0
 2015-11-14 |       2 |         0

cross joinin generate_seriescontains all dates in a given range.

Data used above:

create table stocks (
    id int,
    item_id int,
    quantity int,
    expired_on date
);
insert into stocks (id,item_id,quantity,expired_on) values
(1,1,5,'2015-11-12'),
(2,1,5,'2015-11-13'),
(3,2,5,'2015-11-12'),
(4,2,5,'2015-11-14');

Answer 2

, -, . a left join . - unexpired:

select d.dte, i.item_id,
       sum(quantity) over (partition by i.item_id
                           order by d.dte desc
                           rows between unbounded preceding and 1 preceding
                          ) as unexpired
from (select generate_series(min(expired_on) - interval '1 day', max(expired_on), interval '1 day') as dte
       from stocks
      ) d cross join
      items i left join
      stocks s
      on d.dte = s.expired_on and i.item_id = s.item_id;