sum of until (running balance) and sum of over date range in the same query

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