Re: SP to calc shipments vs receipts

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