I have this working query, but I need to add all months to my result, no matter if the items sold during that month:
select * from (
select
to_char(max(change_date), 'YYYY-MON')::varchar(8) as yyyymmm,
max(change_date) as yearmonth,
sum(vic.sold_qty / item_size.qty)::numeric(18,2) as sold_qty, -- sold monthly
sum(sum(on_hand)) OVER (PARTITION BY vic.item_id order by year,month) as on_hand --running balance
from (((view_item_change vic
left join item on vic.item_id = item.item_id)
left join item_size on item_size.item_id = vic.item_id and item_size.name = item.sell_size)
left join item_plu on vic.item_id = item_plu.item_id and item_plu.seq_num = 0)
where 1 = 1 -- cannot limit date here as its used to show running balance.
and vic.change_date < current_date - date_part('day',current_date)::integer --show only till end of last month
and item.item_id = (select item_id from item_plu where number = '51515')
group by vic.item_id, year, month
) as t
where yearmonth > current_date - date_part('day',current_date)::integer - 540 -- 18 months
which gives me something like this:
"2013-JUN";"2013-06-29";0.00;7.0000
"2013-JUL";"2013-07-22";0.00;6.0000
"2013-AUG";"2013-08-28";2.00;4.0000
"2013-SEP";"2013-09-02";0.00;4.0000
"2013-OCT";"2013-10-28";0.00;4.0000
"2013-NOV";"2013-11-15";0.00;4.0000
"2013-DEC";"2013-12-16";0.00;6.0000
"2014-FEB";"2014-02-10";1.00;5.0000
"2014-APR";"2014-04-09";0.00;5.0000
But I also want to show the months 2014-JAN
and 2014-MAR
so that my chart will be better time scaled.
I know how to do the generate_series(start_date, end_date, '1 month')
intervals, but I can't quite see how I can join this series to the result set above.
I accepted first answer, but after 2 weeks of testing, found a problem. The left join to the series and then adding a coalesce to show 0's and not nulls for the empty months causes a problem wit the running balance.
Resulting query:
SELECT yyyymmm, yyyymmm,
coalesce(sold_qty,0) sold_qty, coalesce(on_hand,0) on_hand
FROM (
SELECT date_trunc('month', month_series)::date as yyyymmm
FROM generate_series(current_date - date_part('day',current_date)::integer - 540
,current_date- date_part('day',current_date)::integer
, interval '1 month') month_series
) month_series
LEFT JOIN (
select * from (
select
date_trunc('month', max(change_date))::date as yyyymmm,
max(change_date) as yearmonth,
sum(vic.sold_qty / item_size.qty)::numeric(18,2) as sold_qty,
sum(sum(on_hand)) OVER (PARTITION BY vic.item_id order by year,month) as on_hand
from (((view_item_change vic
left join item on vic.item_id = item.item_id)
left join item_size on item_size.item_id = vic.item_id and item_size.name = item.sell_size)
left join item_plu on vic.item_id = item_plu.item_id and item_plu.seq_num = 0)
where 1 = 1 -- cannot limit date here as its used to show running balance.
--vic.change_date >= current_date - date_part('day',current_date)::integer - 730 -- only get results for last
--show only till end of last month
and vic.change_date <= current_date - date_part('day',current_date)::integer
and item.item_id = (select item_id from item_plu where number = '19M7077')
group by vic.item_id, year, month
) as a
where yyyymmm > current_date - date_part('day',current_date)::integer - 540 -- 18 months
) q USING (yyyymmm)
order by 1
Results I get:
"2013-07-01";"2013-07-01";0;0
"2013-08-01";"2013-08-01";0;0
"2013-09-01";"2013-09-01";1.00;53.0000
"2013-10-01";"2013-10-01";0;0
"2013-11-01";"2013-11-01";0;0
"2013-12-01";"2013-12-01";0.00;53.0000
"2014-01-01";"2014-01-01";0.00;52.0000
"2014-02-01";"2014-02-01";0;0
"2014-03-01";"2014-03-01";0;0
"2014-04-01";"2014-04-01";0;0
But I want:
"2013-07-01";"2013-07-01";0;53.0000
"2013-08-01";"2013-08-01";0;53.0000
"2013-09-01";"2013-09-01";1.00;53.0000
"2013-10-01";"2013-10-01";0;53.0000
"2013-11-01";"2013-11-01";0;0;53.0000
"2013-12-01";"2013-12-01";0.00;53.0000
"2014-01-01";"2014-01-01";0.00;52.0000
"2014-02-01";"2014-02-01";0;0;52.0000
"2014-03-01";"2014-03-01";0;0;52.0000
"2014-04-01";"2014-04-01";0;0;52.0000
Table definitions
CREATE TABLE item
(
item_id character(22) NOT NULL,
version integer NOT NULL,
created_by character varying(16) NOT NULL,
updated_by character varying(16),
inactive_by character varying(16),
created_on date NOT NULL,
updated_on date,
inactive_on date,
external_id numeric(14,0),
description character varying(40) NOT NULL,
dept_id character(22),
subdept_id character(22),
sell_size character varying(8) NOT NULL,
purch_size character varying(8) NOT NULL
);
CREATE TABLE item_change
(
item_id character(22) NOT NULL,
size_name character varying(8) NOT NULL,
store_id character(22) NOT NULL,
change_date date NOT NULL,
on_hand numeric(18,4) NOT NULL, -- sum column / item_id = total on_hand
total_cost numeric(18,4) NOT NULL,
on_order numeric(18,4) NOT NULL,
sold_qty numeric(18,4) NOT NULL,
sold_cost numeric(18,4) NOT NULL,
sold_price numeric(18,4) NOT NULL,
recv_qty numeric(18,4) NOT NULL,
recv_cost numeric(18,4) NOT NULL,
adj_qty numeric(18,4) NOT NULL,
adj_cost numeric(18,4) NOT NULL
);
CREATE TABLE item_size
(
item_id character(22) NOT NULL,
seq_num integer NOT NULL,
name character varying(8) NOT NULL,
qty numeric(18,4) NOT NULL,
weight numeric(18,4) NOT NULL,
CONSTRAINT item_size_pkey PRIMARY KEY (item_id, seq_num),
CONSTRAINT item_size_c0 FOREIGN KEY (item_id)
REFERENCES item (item_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT item_size_c1 UNIQUE (item_id, name)
);
CREATE TABLE item_plu
(
item_id character(22) NOT NULL,
seq_num integer NOT NULL,
"number" character varying(18) NOT NULL,
size_name character varying(8)
);
CREATE OR REPLACE VIEW view_item_change AS
SELECT date_part('year'::text, item_change.change_date) AS year,
date_part('month'::text, item_change.change_date) AS month,
date_part('week'::text, item_change.change_date) AS week,
date_part('quarter'::text, item_change.change_date) AS quarter,
date_part('dow'::text, item_change.change_date) AS dow,
item_change.item_id,
item_change.size_name,
item_change.store_id,
item_change.change_date,
item_change.on_hand,
item_change.total_cost,
item_change.on_order,
item_change.sold_qty,
item_change.sold_cost,
item_change.sold_price,
item_change.recv_qty,
item_change.recv_cost,
item_change.adj_qty,
item_change.adj_cost
FROM item_change;
There is only a single row per month because I also do a group by year, month. As you see, the view has the year, quarter, month, week, dow columns for easier reporting.
To get working data for this, if required, I can do it, but it would require manually creating it. I have simplified the tables and left out all constraints and some columns.
Basic solution
Generate a complete list of months and
LEFT JOIN
the rest to it:Related answers with more explanation:
Advanced solution for your case
Your query is more complicated than I first understood. You need the running sum over all rows of the selected item, then you want to trim rows older than a minimum date, and fill in missing months with the pre-calculated sum of the previous month.
I achieve this now with
LEFT JOIN LATERAL
.SQL Fiddle with a minimum test case.
Major points:
I removed your VIEW from the query completely. Much cost for no gain.
Since you select a single
item_id
, you don't need toGROUP BY item_id
orPARTITION BY item_id
.Use short table aliases and make all reference unambiguous - especially when posting in a public forum.
Parentheses in your joins were just noise. Joins are executed left-to-right anyway by default.
Simplified date bounds (since I operate with timestamps):
equivalent, but simpler & faster than:
I now fill in missing months after all calculations with
generate_series()
calls per row.It must be
LEFT JOIN LATERAL ... ON TRUE
, not the short form of aJOIN LATERAL
to catch the corner case of the last row. Detailed explanation:Important side notes:
character(22)
is a terrible data type for a primary key (or any column). Details:Ideally this would be an
int
orbigint
column, or possibly aUUID
.Also, storing money amounts as
money
type orinteger
(representing Cents) performs much better overall.In the long run, performance is bound to deteriorate, since you have to include all rows from the very beginning in your calculation. You should cut off old rows and materialize the balance of
on_hold
on a yearly basis or something.