SP to calc shipments vs receipts

Think I'll just do an UPDATE which changes the ALLOCATED
transaction to a SHIP transaction and uses the current
Ship Date/Time

On Tue, 2013-09-24 at 07:38 -0700, Bret Stern wrote:
> 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(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> > .
> >
>
>
>
>