I have a table and I want to make a query which shows the inventory of a product on each day of a period, not only the date when inventory movement was done.
My solution is:
SELECT gs::date, COALESCE(moves,0), btb_goods
FROM generate_series('2017-10-01','2017-11-30',INTERVAL '1 DAY') AS gs
LEFT JOIN (SELECT COALESCE(SUM(btb_menny),0) as moves, btb_goods, btb_date FROM mytable GROUP BY btb_goods, btb_date) AS mozg
ON (date(mozg.btb_date) = gs) ORDER BY 1 ASC;
It works. The result is:
2017-11-05 | 0 | |
2017-11-06 | 0 | |
2017-11-07 | 0 | |
2017-11-08 | 54.1 | sugar |
2017-11-09 | 0 | |
2017-11-10 | 0 | |
2017-11-11 | 0 |
A want the result is rolling over from the date of inventory occurence, like this:
2017-11-05 | 0 | |
2017-11-06 | 0 | |
2017-11-07 | 0 | |
2017-11-08 | 54.1 | sugar |
2017-11-09 | 54.1 | sugar |
2017-11-10 | 54.1 | sugar |
2017-11-11 | 54.1 |
A think, I should write somehow WHERE mozg.btb_date ‹ ...
But i do not how to implement this.
Thnx in advance.
This assumes a WHOLE lot, and I wouldn't be shocked if it didn't work with your real dataset, but it should work against your sample:
with cte as (
SELECT
gs::date, COALESCE(moves,0) as moves, btb_goods
FROM generate_series('2017-10-01','2017-11-30',INTERVAL '1 DAY') AS gs
LEFT JOIN (
SELECT COALESCE(SUM(btb_menny),0) as moves, btb_goods, btb_date
FROM mytable
GROUP BY btb_goods, btb_date) AS mozg
ON (date(mozg.btb_date) = gs)
)
select
gs, sum (moves) over (order by gs) as moves,
max (btb_goods) over (order by gs) as btb_goods
from cte
order by gs
That is missing (I think) is what do you do when you encounter another record with a non-null value, especially on the text (but also on the number). How do you want that handled?
In other words, what is the desired result when your initial query returns this:
11/1/2017 0
11/2/2017 12 jelly
11/3/2017 0
11/4/2017 15 jam
11/5/2017 0