Recipe7.15.Changing Values in a Running Total


Recipe 7.15. Changing Values in a Running Total

Problem

You want to modify the values in a running total depending on the values in another column. Consider a scenario where you want to display the transaction history of a credit card account along with the current balance after each transaction. The following view, V, will be used in this example:

  create view V (id,amt,trx) as select 1, 100, 'PR' from t1 union all select 2, 100, 'PR' from t1 union all select 3, 50,  'PY' from t1 union all select 4, 100, 'PR' from t1 union all select 5, 200, 'PY' from t1 union all select 6, 50,  'PY' from t1 select * from V ID         AMT  TR --  ----------  --  1         100  PR  2         100  PR  3          50  PY  4         100  PR  5         200  PY  6          50  PY 

The ID column uniquely identifies each transaction. The AMT column represents the amount of money involved in each transaction (either a purchase or a payment). The TRX column defines the type of transaction; a payment is "PY" and a purchase is "PR." If the value for TRX is PY, you want the current value for AMT subtracted from the running total; if the value for TRX is PR, you want the current value for AMT added to the running total. Ultimately you want to return the following result set:

 TRX_TYPE        AMT    BALANCE -------- ---------- ---------- PURCHASE        100        100 PURCHASE        100        200 PAYMENT          50        150 PURCHASE        100        250 PAYMENT         200         50 PAYMENT          50          0 

Solution

DB2 and Oracle

Use the window function SUM OVER to create the running total along with a CASE expression to determine the type of transaction:

  1 select case when trx = 'PY'  2             then 'PAYMENT'  3             else 'PURCHASE'  4         end trx_type,  5         amt,  6         sum(  7          case when trx = 'PY'  8             then -amt else amt  9          end 10        ) over (order by id,amt) as balance 11 from V 

MySQL, PostgreSQL, and SQL Server

Use a scalar subquery to create the running total along with a CASE expression to determine the type of transaction:

  1 select case when v1.trx = 'PY'  2             then 'PAYMENT'  3             else 'PURCHASE'  4         end as trx_type,  5         v1.amt,  6        (select sum(  7                 case when v2.trx = 'PY'  8                      then -v2.amt else v2.amt  9                 end 10                ) 11           from V v2 12          where v2.id <= v1.id) as balance 13  from V v1 

Discussion

The CASE expression determines whether the current AMT is added or deducted from the running total. If the transaction is a payment, the AMT is changed to a negative value, thus reducing the amount of the running total. The result of the CASE expression is seen below:

  select case when trx = 'PY'             then 'PAYMENT'             else 'PURCHASE'        end trx_type,        case when trx = 'PY'             then -amt else amt        end as amt   from V TRX_TYPE       AMT -------- --------- PURCHASE       100 PURCHASE       100 PAYMENT        -50 PURCHASE       100 PAYMENT       -200 PAYMENT        -50 

After evaluating the transaction type, the values for AMT are then added to or subtracted from the running total. For an explanation on how the window function, SUM OVER, or the scalar subquery creates the running total see recipe "Calculating a Running Total."




SQL Cookbook
SQL Cookbook (Cookbooks (OReilly))
ISBN: 0596009763
EAN: 2147483647
Year: 2005
Pages: 235

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net