Select amount and current balance for the last 18 months using generate_series

Question

I have this working request, but I need to add all the months to my result, regardless of whether the products were sold during this 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 schedule is better scaled.

I know how to span generate_series(start_date, end_date, '1 month') , but I cannot figure out how I can join this series in the result set above.

I accepted the first answer, but after two weeks of testing I found a problem. Left-joining a series and then adding coalescence to display 0 rather than zero for empty months causes a problem with the current balance.

Result 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 

The results that 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 one row per month, because I also do a group by year, month. As you can see, the view has the columns year, quarter, month, week, dow for ease of reporting.

To get working data for this, if necessary, I can do it, but for this I will need it manually. I simplified the tables and left all the restrictions and some columns.

Answer 1

Basic solution

Create a complete list of months and LEFT JOIN rest:

 SELECT * FROM ( SELECT to_char(m, 'YYYY-MON') AS yyyymmm FROM generate_series(<start_date>, <end_date>, interval '1 month') m ) m LEFT JOIN ( <your query here> ) q USING (yyyymmm); 

Related answers with lots of explanation:

• Attach the count request to the series_ generation in postgres, and also get the Null values ​​as "0" and "
• Best way to count records at arbitrary time intervals in Rails + Postgres

Advanced solution for your case.

Your request is more complicated than I understood. You need the current amount for all lines of the selected item, then you want to trim lines older than the minimum date and fill in the blanks with the previously calculated amount of the previous month.

I am achieving this now with the LEFT JOIN LATERAL .

 SELECT COALESCE(m.yearmonth, c.yearmonth)::date, sold_qty, on_hand FROM ( SELECT yearmonth , COALESCE(sold_qty, 0) AS sold_qty , sum(on_hand_mon) OVER (ORDER BY yearmonth) AS on_hand , lead(yearmonth) OVER (ORDER BY yearmonth) - interval '1 month' AS nextmonth FROM ( SELECT date_trunc('month', c.change_date) AS yearmonth , sum(c.sold_qty / s.qty)::numeric(18,2) AS sold_qty , sum(c.on_hand) AS on_hand_mon FROM item_change c LEFT JOIN item i USING (item_id) LEFT JOIN item_size s ON s.item_id = i.item_id AND s.name = i.sell_size LEFT JOIN item_plu p ON p.item_id = i.item_id AND p.seq_num = 0 WHERE c.change_date < date_trunc('month', now()) - interval '1 day' AND c.item_id = (SELECT item_id FROM item_plu WHERE number = '51515') GROUP BY 1 ) sub ) c LEFT JOIN LATERAL generate_series(c.yearmonth , c.nextmonth , interval '1 month') m(yearmonth) ON TRUE WHERE c.yearmonth > date_trunc('year', now()) - interval '540 days' ORDER BY COALESCE(m.yearmonth, c.yearmonth); 

SQL Fiddle with a minimal test case.

Highlights:

• I completely removed your view from the request. Great value without benefits.

• Since you select a single item_id , you do not need GROUP BY item_id or PARTITION BY item_id .

• Use short table aliases and make all links unambiguous - especially when posting on an open forum.

• The brackets in your connections were just noise. By default, all connections are made from left to right.

• Simplified date restrictions (since I use timestamps):

   date_trunc('year', current_date) - interval '540 days' date_trunc('month', current_date) - interval '1 day' 

  but simpler and faster than:

   current_date - date_part('day',current_date)::integer - 540 current_date - date_part('day',current_date)::integer 

• Now I fill in the missing months after all the calculations using generate_series() calls in a row.

• It must be LEFT JOIN LATERAL ... ON TRUE , not a short form of JOIN LATERAL to catch the angular case of the last line. Detailed explanation:

  • Find the most common elements in an array using a group .

Important notes:

character(22) is a terrible data type for a primary key (or any column). Details:

• Any disadvantages of using the "text" data type to store strings?

Ideally, this would be an int or bigint or perhaps a UUID .

In addition, storing money in the form of money or integer (representing Cents) is much better.

In the long run, performance will deteriorate, since you must include all the lines from the very beginning in your calculations. You have to cut off the old lines and materialize the on_hold balance on an annual basis or something like that.