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

I like that idea.

Also thinking of creating two ALLOCATE transactions.

ALLO (Allocate Open)
ALLC (Allocate Closed) after allocation has been shipped.

This way I can still see the original allocation and allocation
ship transactions when running a movement report, but only
use ALLO to determine allocations still in our possession.

Thanks again for comments

On Wed, 2013-09-25 at 22:27 +0800, DDT wrote:
> By the way, you can try to save the current totals to another table.
> update it through triggers when the inventory transactions changed.
> it may lead to better performance on a large set of inventory
> transactions for query current totals
>
> -------------
>
>
>
>
> 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
> > > .
> > >
> >
> >
> >
> >
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> .
>