Hi Folks,
I have one that I need your advise on how to construct.
I have the need to create a series of dates starting from a Sunday - one week
apart.
(select ((date_trunc('week', '2012-02-12'::date)::date) +(i+6)) as week_date
from
generate_series(0,84,7) i) as foo
that gives a table of sunday dates starting from '2012-02-12'::date
The dates will be used in the column headings ( I don't know in advance what
those dates will be - because the start sunday can change).
I have written functions to return sum()'s of a field for the week starting
from the sundays returned from the above dates.
getqtyordered(itemsite_id, foo.week_date) -- returns qty order for the week
getqtyalloc(itemsite_id, foo.week_date) -- qty_alloc for the week
... I actually have several of these types of functions.
And of course there is other information retrieved.
What I need to output is the following:
Name date 1 date2 date3 date4 ...
qty order 10 8 20 15
qty_alloc 6 0 3 50
What I can't figure out is how to get the dates to become my field/column
name.
BELOW IS MY ACTUAL FUNCTION:
CREATE OR REPLACE FUNCTION xchromasun._chromasun_totals(date) RETURNS SETOF weekly_mpr AS
$BODY$
DECLARE
_passed_date alias for $1;_start_date date;
_warehous_id integer;
_firm_planned_accum numeric;
_running_avail numeric;
_start_qoh numeric;
_itemsite_id integer;
_olditemsite_id integer;
rec record;
myrecord weekly_mpr;
counter integer;
BEGIN
_running_avail :=0;
_firm_planned_accum := 0;
_olditemsite_id := 0;
_itemsite_id := 0;
counter := 0;
--find Sunday
select the_date from
(select (_passed_date::date) - num as the_date, extract(dow from
((_passed_date::date) - num)) as weekday from (select generate_series(0,6) as num) as t) as myans where weekday = 0
into _start_date;
for rec in select itemsite_id,item_number, item_descrip1, itemsite_qtyonhand,
itemsite_safetystock,foo.week_date,itemsite_leadtime,warehous_code,
coalesce(vend_name,'NOT ON FILE') as "vendor", coalesce(vend_number, 'NONE')
as "vend_number",
xchromasun._chromasun_getqtyordered(itemsite_id, foo.week_date) as
"qty_ordered",
xchromasun._chromasun_getqtyallocated(itemsite_id, foo.week_date) as
"qty_alloc",
xchromasun._chromasun_getqtypr(itemsite_id, foo.week_date) as "purch_req",
xchromasun._chromasun_getqtyplanneddemand(itemsite_id, foo.week_date) as
"planned_demand",
qtyavailable(itemsite_id, foo.week_date) as "qty_avail",
(select xchromasun._chromasun_getqtyfirmed(itemsite_id, foo.week_date)) as
"firm_planned_orders",
(xchromasun._chromasun_getqtyplanned(itemsite_id, foo.week_date) - (select
xchromasun._chromasun_getqtyfirmed(itemsite_id, foo.week_date))) as
"planned_orders"
from public.itemsite
left join whsinfo on (warehous_id = itemsite_warehous_id)
left join item on (item_id = itemsite_item_id)
left join itemsrc on (itemsrc_item_id = item_id)
left outer join vendinfo on (vend_id = itemsrc_vend_id),
(select ((date_trunc('week', _start_date::date)::date) +(i+6)) as week_date
from
generate_series(0,84,7) i) as foo
where itemsite_item_id in (select item_id from item) and item_type = 'P'
order by item_number asc,vend_number,week_date
loopcounter := counter +1;
_olditemsite_id :=rec.itemsite_id;
IF _itemsite_id <> _olditemsite_id THEN _itemsite_id := rec.itemsite_id; _running_avail := rec.itemsite_qtyonhand;END
IF;
_firm_planned_accum = _firm_planned_accum + rec.firm_planned_orders ;
_running_avail = _running_avail - rec.planned_demand + rec.qty_ordered -
rec.qty_alloc ;
myrecord.counter := counter::integer;
myrecord.warehous_code := rec.warehous_code;
myrecord.week_of := rec.week_date;
myrecord.qty_ordered := rec.qty_ordered;
myrecord.firm_planned_orders := rec.firm_planned_orders;
myrecord.planned_orders := rec.planned_orders;
myrecord.item_number := rec.item_number;
myrecord.item_descrip1 := rec.item_descrip1;
myrecord.itemsite_qtyonhand := rec.itemsite_qtyonhand;
myrecord.itemsite_safetystock := rec.itemsite_safetystock;
myrecord.qty_alloc := rec.qty_alloc;
myrecord.qty_avail := rec.qty_avail;
myrecord.planned_qoh := _running_avail;
myrecord.firm_avail := _firm_planned_accum;
myrecord.lead_time := rec.itemsite_leadtime;
myrecord.vend_number := rec.vend_number;
myrecord.vendor := rec.vendor;
myrecord.purch_req := rec.purch_req;
myrecord.planned_demand := -rec.planned_demand;
return next myrecord;
end loop;
return;
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000;
ALTER FUNCTION xchromasun._chromasun_totals(date) OWNER TO postgres;
