Message-ID: <526F2EC0.2030300@turnkey.bz>
Date: Mon, 28 Oct 2013 21:42:56 -0600
From: "M. D." <lists@turnkey.bz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: sum of until (running balance) and sum of over date range in the
 same query
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Hi everyone,

I've been working and thinking of a way to get this data that I need, 
but I can't find a way to get it in one query.

these are simplified tables and the query below that I've tried to make:


CREATE TABLE dept (
   dept_id character(22) NOT NULL,
   name character varying(30) NOT NULL,
   "number" character varying(5)
)

CREATE TABLE subdept
(
   subdept_id character(22) NOT NULL,
   name character varying(30) NOT NULL,
   "number" character varying(5),
   dept_id character(22) NOT NULL
)

CREATE TABLE item
(
   item_id character(22) NOT NULL,
   version integer NOT NULL,
   description character varying(40) NOT NULL,
   dept_id character(22),
   subdept_id character(22),
   expense_acct character(22),
   income_acct character(22),
   asset_acct character(22),
   sell_size character varying(8) NOT NULL,
   purch_size character varying(8) 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
)

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;


select
year*100+month as yearmonth,
(select number from item_plu where item_plu.item_id = 
view_item_change.item_id) as itmNumber,
description,
sum(view_item_change.sold_qty /   item_size.qty)::numeric(18,2) as qty,  
-- qty sold during grouped time
sum(view_item_change.sold_cost)::numeric(18,2) as cost,  -- total cost 
of those sold - COGS
sum(view_item_change.sold_price)::numeric(18,2) as sales,  -- total sold 
value
sum(amount) -- amount sold over the grouped period
from ((view_item_change
join item on view_item_change.item_id = item.item_id)
join item_size on item_size.item_id = view_item_change.item_id and 
item_size.name = item.sell_size)
join dept on item.dept_id = dept.dept_id
join subdept on item.subdept_id = subdept.subdept_id
where view_item_change.change_date >= '2012-01-01'
and item.dept_id = (select dept_id from dept where name = 'Oil')
group by year,month,  view_item_change.item_id, description
order by year,month, itmNumber


Note the comments in the query.  The on_hand column has an entry for 
each day that an item qty changes, so to get a current On Hand, I do a 
sum(amount) where change_date <= 'date'.

What I want is a result set grouped by year/quarter/month/week by item, 
showing on hand at end of that time period and the sum of the amount 
sold during that time.  Is it possible to get this data in one query?  
The complication is that the sold qty is over the group, while On Hand 
is a running balance.

Thanks,
Mark


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql