Customer Statement: updating a running total per row retuned

[Former Member]

How can i maintain a running balance on a customer statement: using the table below as an example, have tried cursors but not getting the desired results.

Acct_no Tran    Amount  Date
200 D   20  October 20
200 C   200 October 23
200 C   400 October 29
200 D   100 November 1

I have a current balance of say $100, when transaction is Debit ( Tran = D), i want to subtract and when its a Credit (tran = C) i want to add. i want to produce a statement from this table as a resultset showing running total, acct number,transaction amount and the date EXAMPLE

October 20 (Date),$20  (Transaction Amount), $80 (running total $100 - $20), $200 (Account Number)
October 23 (Date),$200 (Transaction Amount),$280 (running total $80 + $200), $200 (Account Number)   
October 29 (Date),$400 (Transaction Amount),$680 (running total $280 + $400), $400 (Account Number) 
November 1 (Date),$100 (Transaction Amount),$580 (running total $680 - $200), $200 (Account Number)

[VolkerBarth · Accepted solution]

Based on your new sample data and Thomas's suggestions, the following might be what you're lokking for:

select ACCT_NO, DESCC, DESC1,
  -- build amount with sign based on transaction type
  case TRANS_TYPE WHEN 'D' then -1 else 1 end * DESC1 as AMOUNT,
  -- use sum over window to build running sum
  sum(AMOUNT) over (partition by ACCT_NO order by COUNTER) as RUNNING_TOTAL,
  TRANS_TYPE, DATEE, COUNTER  
from TBL_TRANSACTIONS
-- order by ACCT_NO and then chronogically by PK
order by ACCT_NO,COUNTER

returns:

ACCT_NO,DESCC,DESC1,AMOUNT,RUNNING_TOTAL,TRANS_TYPE,DATEE,COUNTER
210034,Purchase,40,-40,000000,-40,000000,D,21-10-2011,1
210034,Agent Commission,1.50,-1,500000,-41.500000,D,21-10-2011,2
210034,Paymnet,20,-20,000000,-61.500000,D,24-10-2011,3
210034,Purchase,50,-50,000000,-111.500000,D,26-10-2011,4
210034,Account Payment,70,70,000000,-41.500000,C,25-10-2011,5
210034,Refund,7,7,000000,-34.500000,C,31-10-2011,6
210034,Monthly Service Fees,7,-7,000000,-41.500000,D,31-10-2011,7

So the running total is:

-40.000000  
-41.500000  
-61.500000  
-111.500000  
-41.500000  
-34.500000  
-41.500000  

---

EDIT:

It should be noted that the above WINDOW definition is a short form and makes use of an implicit RANGE definition that uses a window between the first row of the partition and the current row - exactly what is needed for a running total. So IMHO it does what one would expect but that might not be always true for such complex SQL language elements, and therefore an explicit addition of the RANGE clause would be worthwhile - cf. the docs:

If no bounds are defined for a window, the default window bounds are set as follows:
If the window specification contains an ORDER BY clause, it is equivalent to specifying RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

[Former Member · Accepted solution]

Problem solved Thanks a lot

[Breck_Carter]

See also: http://sqlanywhere.blogspot.com/2011/11/olap-window-for-running-balance.html

[Former Member]

Thats excellent Breck. Thanks

[thomas_duemesnil]

I would generate a Column

( IF Debit THEN DebAmount*-1 ELSE Credit ENDIF ) as Value

This Column can be use with a Window Function to count a running total.

sum( Value ) over ( order by date )

Here is a better Sample

select BookingNumber, Amount, sum( amount ) over ( order by BookingNumber ) as RunningTotal 
from (
select '1' as BookingNumber, 100 as Amount from dummy
union all
select '2', -50 from dummy
union all
select '3', 10 from dummy
union all
select '4', -51 from dummy
) as Temp

This will give you the following result set

BookingNumber,Amount,RunningTotal
'1'          ,100   ,100
'2'          ,-50   , 50
'3'          ,10    , 60
'4'          ,-51   ,  9

[Former Member]

Hi Thomas,

Am not sure i understand

Regards

[VolkerBarth]

I'm sure Thomas's sample really shows a possible solution.

Just to add: We clearly could give a sample even more specific to your needs if you would supply a fitting table schema and some insert statements with test data...

[Former Member]

here is a sample table

CREATE TABLE TBL_TRANSACTIONS (
    "ACCT_NO" DECIMAL(14,0) NULL,
    "DESCC" CHAR(90) NULL,
    "DESC1" CHAR(45) NULL,
    "DATEE" DATE NOT NULL,
    "TRANS_TYPE" CHAR(1),
    "COUNTER" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    PRIMARY KEY ( "COUNTER" ASC, "DATEE" ASC )
) IN "SYSTEM";

INSERT INTO  TBL_TRANSACTIONS (ACCT_NO,DESCC,DESC1,DATEE,TRANS_TYPE)
VALUES  (210034,'Purchase','40','21-10-2011','D');
INSERT INTO  TBL_TRANSACTIONS (ACCT_NO,DESCC,DESC1,DATEE,TRANS_TYPE)
VALUES  (210034,'Agent Commission','1.50','21-10-2011','D');
INSERT INTO  TBL_TRANSACTIONS (ACCT_NO,DESCC,DESC1,DATEE,TRANS_TYPE)
VALUES  (210034,'Paymnet','20','24-10-2011','D');
INSERT INTO  TBL_TRANSACTIONS (ACCT_NO,DESCC,DESC1,DATEE,TRANS_TYPE)
VALUES  (210034,'Purchase','50','26-10-2011','D');
INSERT INTO  TBL_TRANSACTIONS (ACCT_NO,DESCC,DESC1,DATEE,TRANS_TYPE)
VALUES  (210034,'Account Payment','70','25-10-2011','C');
INSERT INTO  TBL_TRANSACTIONS (ACCT_NO,DESCC,DESC1,DATEE,TRANS_TYPE)
VALUES  (210034,'Refund','7','31-10-2011','C');
INSERT INTO  TBL_TRANSACTIONS (ACCT_NO,DESCC,DESC1,DATEE,TRANS_TYPE)
VALUES  (210034,'Monthly Service Fees','7','31-10-2011','D');