date:20120224

[SQL] crosstab help

2012-02-24 Thread John Fabiani

I have a simple table
item_number  week_of   planned_qoh
--  --   --
5   2012-02-05   30
5   2012-02-12   40
5   2012-02-19   50


where 
item_number text
week_of date
planned_qoh integer

I have a function that returns the table as above:

chromasun._chromasun_totals(now()::date)

I want to see

5   2012-02-05   2012-02-122012-02-19
30  40 50

This is what I have tried (although, I have tired many others)

select * from crosstab('select item_number::text as row_name, 
to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as buckvalue 
from xchromasun._chromasun_totals(now()::date)')
as ct(item_number text, week_of date,  planned_qoh integer)

I get 
ERROR:  return and sql tuple descriptions are incompatible

What am I doing wrong?

Johnf

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

Re: [SQL] crosstab help

2012-02-24 Thread Andreas Gaab

Hi,

the return type of the crosstab must be defined correctly, according to the 
number of expected columns.

Try following (untested):

select * from crosstab(
'select item_number::text as row_name, to_char(week_of,''MM-DD-YY'')::date as 
bucket, planned_qoh::integer as buckvalue from 
xchromasun._chromasun_totals(now()::date)')
as ct(item_number text, week_of_1 date, week_of_2 date, week_of_3 date)

Regards,
Andreas



-Ursprüngliche Nachricht-
Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im 
Auftrag von John Fabiani
Gesendet: Freitag, 24. Februar 2012 09:11
An: pgsql-sql@postgresql.org
Betreff: [SQL] crosstab help

I have a simple table
item_number  week_of   planned_qoh
--  --   --
5   2012-02-05   30
5   2012-02-12   40
5   2012-02-19   50


where
item_number text
week_of date
planned_qoh integer

I have a function that returns the table as above:

chromasun._chromasun_totals(now()::date)

I want to see

5   2012-02-05   2012-02-122012-02-19
30  40 50

This is what I have tried (although, I have tired many others)

select * from crosstab('select item_number::text as row_name,
to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as buckvalue from 
xchromasun._chromasun_totals(now()::date)')
as ct(item_number text, week_of date,  planned_qoh integer)

I get
ERROR:  return and sql tuple descriptions are incompatible

What am I doing wrong?

Johnf

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

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

Re: [SQL] crosstab help

2012-02-24 Thread John Fabiani

That worked!  However, I need the actual date to be the column heading?   And 
of course the dates change depending on the date passed to the function:
xchromasun._chromasun_totals(now()::date)

So how do I get the actual dates as the column header?
johnf
On Friday, February 24, 2012 09:27:38 AM Andreas Gaab wrote:
> Hi,
> 
> the return type of the crosstab must be defined correctly, according to the
> number of expected columns.
> 
> Try following (untested):
> 
> select * from crosstab(
> 'select item_number::text as row_name, to_char(week_of,''MM-DD-YY'')::date
> as bucket, planned_qoh::integer as buckvalue from
> xchromasun._chromasun_totals(now()::date)') as ct(item_number text,
> week_of_1 date, week_of_2 date, week_of_3 date)
> 
> Regards,
> Andreas
> 
> 
> 
> -Ursprüngliche Nachricht-
> Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
> Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:11
> An: pgsql-sql@postgresql.org
> Betreff: [SQL] crosstab help
> 
> I have a simple table
> item_number  week_of   planned_qoh
> --  --   --
> 5 2012-02-05   30
> 5 2012-02-12   40
> 5 2012-02-19   50
> 
> 
> where
> item_number text
> week_of date
> planned_qoh integer
> 
> I have a function that returns the table as above:
> 
> chromasun._chromasun_totals(now()::date)
> 
> I want to see
> 
> 5   2012-02-05   2012-02-122012-02-19
> 30  40 50
> 
> This is what I have tried (although, I have tired many others)
> 
> select * from crosstab('select item_number::text as row_name,
> to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as buckvalue
> from xchromasun._chromasun_totals(now()::date)') as ct(item_number text,
> week_of date,  planned_qoh integer)
> 
> I get
> ERROR:  return and sql tuple descriptions are incompatible
> 
> What am I doing wrong?
> 
> Johnf
> 
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes
> to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

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

Re: [SQL] crosstab help

2012-02-24 Thread Andreas Gaab

As far as I know you must define the numbers (and types) of columns and column 
headers individually for each query or define some custom function...

Andreas

-Ursprüngliche Nachricht-
Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im 
Auftrag von John Fabiani
Gesendet: Freitag, 24. Februar 2012 09:39
An: pgsql-sql@postgresql.org
Betreff: Re: [SQL] crosstab help

That worked!  However, I need the actual date to be the column heading?   And 
of course the dates change depending on the date passed to the function:
xchromasun._chromasun_totals(now()::date)

So how do I get the actual dates as the column header?
johnf
On Friday, February 24, 2012 09:27:38 AM Andreas Gaab wrote:
> Hi,
> 
> the return type of the crosstab must be defined correctly, according 
> to the number of expected columns.
> 
> Try following (untested):
> 
> select * from crosstab(
> 'select item_number::text as row_name, 
> to_char(week_of,''MM-DD-YY'')::date
> as bucket, planned_qoh::integer as buckvalue from
> xchromasun._chromasun_totals(now()::date)') as ct(item_number text,
> week_of_1 date, week_of_2 date, week_of_3 date)
> 
> Regards,
> Andreas
> 
> 
> 
> -Ursprüngliche Nachricht-
> Von: pgsql-sql-ow...@postgresql.org 
> [mailto:pgsql-sql-ow...@postgresql.org]
> Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:11
> An: pgsql-sql@postgresql.org
> Betreff: [SQL] crosstab help
> 
> I have a simple table
> item_number  week_of   planned_qoh
> --  --   --
> 5 2012-02-05   30
> 5 2012-02-12   40
> 5 2012-02-19   50
> 
> 
> where
> item_number text
> week_of date
> planned_qoh integer
> 
> I have a function that returns the table as above:
> 
> chromasun._chromasun_totals(now()::date)
> 
> I want to see
> 
> 5   2012-02-05   2012-02-122012-02-19
> 30  40 50
> 
> This is what I have tried (although, I have tired many others)
> 
> select * from crosstab('select item_number::text as row_name,
> to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as 
> buckvalue from xchromasun._chromasun_totals(now()::date)') as 
> ct(item_number text, week_of date,  planned_qoh integer)
> 
> I get
> ERROR:  return and sql tuple descriptions are incompatible
> 
> What am I doing wrong?
> 
> Johnf
> 
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make 
> changes to your subscription: 
> http://www.postgresql.org/mailpref/pgsql-sql

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

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

Re: [SQL] crosstab help

2012-02-24 Thread John Fabiani

Thanks for the insight!
johnf
On Friday, February 24, 2012 09:48:03 AM Andreas Gaab wrote:
> As far as I know you must define the numbers (and types) of columns and
> column headers individually for each query or define some custom
> function...
> 
> Andreas
> 
> -Ursprüngliche Nachricht-
> Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
> Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:39
> An: pgsql-sql@postgresql.org
> Betreff: Re: [SQL] crosstab help
> 
> That worked!  However, I need the actual date to be the column heading?  
> And of course the dates change depending on the date passed to the
> function: xchromasun._chromasun_totals(now()::date)
> 
> So how do I get the actual dates as the column header?
> johnf
> 
> On Friday, February 24, 2012 09:27:38 AM Andreas Gaab wrote:
> > Hi,
> > 
> > the return type of the crosstab must be defined correctly, according
> > to the number of expected columns.
> > 
> > Try following (untested):
> > 
> > select * from crosstab(
> > 'select item_number::text as row_name,
> > to_char(week_of,''MM-DD-YY'')::date
> > as bucket, planned_qoh::integer as buckvalue from
> > xchromasun._chromasun_totals(now()::date)') as ct(item_number text,
> > week_of_1 date, week_of_2 date, week_of_3 date)
> > 
> > Regards,
> > Andreas
> > 
> > 
> > 
> > -Ursprüngliche Nachricht-
> > Von: pgsql-sql-ow...@postgresql.org
> > [mailto:pgsql-sql-ow...@postgresql.org]
> > Im Auftrag von John Fabiani Gesendet: Freitag, 24. Februar 2012 09:11
> > An: pgsql-sql@postgresql.org
> > Betreff: [SQL] crosstab help
> > 
> > I have a simple table
> > item_number  week_of   planned_qoh
> > --  --   --
> > 5   2012-02-05   30
> > 5   2012-02-12   40
> > 5   2012-02-19   50
> > 
> > 
> > where
> > item_number text
> > week_of date
> > planned_qoh integer
> > 
> > I have a function that returns the table as above:
> > 
> > chromasun._chromasun_totals(now()::date)
> > 
> > I want to see
> > 
> > 5   2012-02-05   2012-02-122012-02-19
> > 
> > 30  40  
> >   50
> > 
> > This is what I have tried (although, I have tired many others)
> > 
> > select * from crosstab('select item_number::text as row_name,
> > to_char(week_of,''MM-DD-YY'') as bucket, planned_qoh::integer as
> > buckvalue from xchromasun._chromasun_totals(now()::date)') as
> > ct(item_number text, week_of date,  planned_qoh integer)
> > 
> > I get
> > ERROR:  return and sql tuple descriptions are incompatible
> > 
> > What am I doing wrong?
> > 
> > Johnf
> > 
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make
> > changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
> 
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes
> to your subscription: http://www.postgresql.org/mailpref/pgsql-sql

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

[SQL] crosstab maybe by case statement

2012-02-24 Thread John Fabiani

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:

Namedate 1 date2   date3date4 ... 
qty order 10820  15
qty_alloc 6  03 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

loop
 counter := 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;





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

[SQL] crosstab help

Re: [SQL] crosstab help

Re: [SQL] crosstab help

Re: [SQL] crosstab help

Re: [SQL] crosstab help

[SQL] crosstab maybe by case statement

6 matches

Site Navigation

Mail list logo

Footer information