Thread: SP to calc shipments vs receipts
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 Thinking of writing a stored procedure that has the aggregate queries, and returns the values defined above for this example. Is this a recommended way? B Stern
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
=?gb18030?B?u9i4tKO6IFtHRU5FUkFMXSBTUCB0byBjYWxjIHNo?= =?gb18030?B?aXBtZW50cyB2cyByZWNlaXB0cw==?=
From
"DDT"
Date:
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
.
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?
> 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
.
First, regarding the stored procedure recommendation, it depends on what you are trying to do. The decision to go with a stored procedure vs a view depends on how this fits into the rest of your application.
Here is what I would do for the SQL though:
WITH base_agg AS (
select part_no,
sum(cast when trans_type='REC' then trans_qty else 0) as "received",
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 "shipped"
from inventory_transaction_table
group by part_no
)
SELECT shipped, allocated, received - allocated - shipped as on_hand from base_agg;
On Mon, Sep 23, 2013 at 11:01 PM, John R Pierce <pierce@hogranch.com> wrote:
On 9/23/2013 10:13 PM, Bret Stern wrote:select part_no,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
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
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
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
> .
>
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
> > .
> >
>
>
>
>
=?gb18030?B?u9i4tKO6W0dFTkVSQUxdIFNQIHRvIGNhbGMgc2hp?= =?gb18030?B?cG1lbnRzIHZzIHJlY2VpcHRz?=
From
"DDT"
Date:
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
.
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
-------------
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
.
On Wed, Sep 25, 2013 at 7:27 AM, DDT <410845160@qq.com> 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
If you are going to do this, my recommendation is to store periodic summaries (i.e. for sum through date) and then aggregate rolling forward. This vastly simplifies querying and data validation if you are only appending data.
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
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
> .
>
=?gb18030?B?UmU6IFtHRU5FUkFMXSC72Li0o7pbR0VORVJBTF0g?= =?gb18030?B?U1AgdG8gY2FsYyBzaGlwbWVudHMgdnMgcmVjZWlw?= =?gb18030?B?dHM=?=
From
"DDT"
Date:
thanks.
------------------ Original ------------------
From: "Chris Travers";<chris.travers@gmail.com>;
Date: Wed, Sep 25, 2013 10:46 PM
To: "DDT"<410845160@qq.com>;
Cc: "bret_stern"<bret_stern@machinemanagement.com>; "pgsql-general"<pgsql-general@postgresql.org>;
Subject: Re: [GENERAL] 回复：[GENERAL] SP to calc shipments vs receipts
On Wed, Sep 25, 2013 at 7:27 AM, DDT <410845160@qq.com> 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
If you are going to do this, my recommendation is to store periodic summaries (i.e. for sum through date) and then aggregate rolling forward. This vastly simplifies querying and data validation if you are only appending data.
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
On Wed, 2013-09-25 at 07:46 -0700, Chris Travers wrote: > > > > On Wed, Sep 25, 2013 at 7:27 AM, DDT <410845160@qq.com> 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 > > > If you are going to do this, my recommendation is to store periodic > summaries (i.e. for sum through date) and then aggregate rolling > forward. This vastly simplifies querying and data validation if you > are only appending data. > -- > Best Wishes, > Chris Travers > > > Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor > lock-in. > http://www.efficito.com/learn_more.shtml Thanks Chris for the suggestions. With all the input from this forum, my project is looking pretty sweet. B Stern