Given the following table
PAYMENT_Date TRANSACTION_TYPE PAYMENT_AMT
1/1/2012 P 184366
1/1/2012 R -5841
1/2/2012 P 941
1/3/2012 P 901
1/3/2012 R 5841
and the following query:
select payment_date, transaction_type, payment_amt,
SUM(payment_amt) OVER(ORDER BY payment_date, transaction_type
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS RUNNING_BALANCE
from TABLE;
I get these results:
PAYMENT_Date TRANSACTION_TYPE PAYMENT_AMT RUNNING_BALANCE
1/1/2012 P 184366 0
1/1/2012 R -5841 -184366
1/2/2012 P 941 -178525
1/3/2012 P 901 -179466
1/3/2012 R 5841 -180367
EXPECTED:
PAYMENT_Date TRANSACTION_TYPE PAYMENT_AMT RUNNING_BALANCE
1/1/2012 P 184366 0
1/1/2012 R -5841 184366
1/2/2012 P 941 178525
1/3/2012 P 901 179466
1/3/2012 R 5841 180367
Why does RUNNING_BALANCE come back as a negative number? How can I make it not, besides the obvious abs()?
I think you need to change:
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`
to:
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
Test in SQLfiddle:
SELECT payment_date, transaction_type, payment_amt,
COALESCE( SUM(payment_amt)
OVER( ORDER BY payment_date, transaction_type
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
, 0) AS RUNNING_BALANCE
FROM T;