SP to calc shipments vs receipts - Mailing list pgsql-general

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@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> > .
> >
>
>
>
>