Re: 回复： [GENERAL] SPto calc shipments vs receipts - Mailing list pgsql-general

Support at it's finest.
Thinking maybe ALLOCATED transactions zero out
when the allocated qty is shipped, but I would like to save
the original allocated qty..maybe add another field in my
transaction table to save the allocated transaction qty.

Also don't see any problem with deleting the ALLOCATED
transaction record..normally I don't like deleting any transaction
records, but at this moment don't see the harm.

my table.

  id serial NOT NULL,
  trans_date character varying(20),
  trans_time character varying(20),
  trans_type character varying(8),
  trans_user character varying(10),
  trans_qty real,
  trans_reference character varying(40),
  trans_comment character varying(80),
  part_no character varying(40),
  part_desc character varying(40),
  part_owner_id character varying(20),
  building character varying(4),
  isle character varying(2),
  rack character varying(2),
  shelf character varying(2),
  matrix character varying(2),
  CONSTRAINT ss_item_tran_key PRIMARY KEY (id)

You'all have me thinking. Thanks for taking time to
educate me.


On Tue, 2013-09-24 at 14:22 +0800, DDT wrote:
> hello, is the output calculated by following rule?
>
> on_hand SUM(receipt) - SUM(shipment) - SUM(allocated)
> available SUM(receipt) - SUM(shipment)
>
> sql can be:
> sum(case when trans_type='REC' then trans_qty when trans_type IN
> ('SHP', 'ALL') then -trans_qty else 0) as on_hand
> sum(case when trans_type='REC' then trans_qty when trans_type = 'SHP'
> then -trans_qty else 0) as on_hand
>
> but i'm courise about if something is allocated and then it shipped,
> will you delete the record or allocation?
>
>
>
>
> On 9/23/2013 10:13 PM, Bret Stern wrote:
> > I have an inventory transaction table with several fields,
> > specifically:
> > part_no
> > trans_type
> > trans_qty
> >
> > part_no | trans_type | trans_qty
> > abc REC 5000 (receipt)
> > abc REC 400 (receipt)
> > abc SHP 1000 (shipment)
> > abc ALL 1000 (allocated)
> >
> > Looking for the best way to show following totals with SQL
> >
> > on_hand | allocated | available
> > 3400 1000                 4400
>
> select part_no,
>              sum(cast when trans_type='REC' then trans_qty else 0) as
> "on_hand",
>              sum(cast when trans_type='ALL' then trans_qty else 0) as
> "allocated",
>              sum(cast when trans_type='SHP' then trans_qty else 0) as
> "allocated"
>      from inventory_transaction_table
>      group by part_no;
>
>
> except, your example output doesn't correlate with your sample input
> according to any rules I can see.
>
>
> --
> john r pierce                                      37N 122W
> somewhere on the middle of the left coast
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> .
>