Re: SP to calc shipments vs receipts

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(at)hogranch(dot)com> wrote:

> 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<http://www.postgresql.org/mailpref/pgsql-general>
>

--
Best Wishes,
Chris Travers

Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more.shtml