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)

asked 03 Nov '11, 03:26

Takudzwa's gravatar image

Takudzwa
818813
accept rate: 0%

edited 03 Nov '11, 05:06

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k243561


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.

permanent link

answered 04 Nov '11, 10:04

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 12 Sep '16, 15:27

Problem solved Thanks a lot

(04 Nov '11, 10:17) Takudzwa

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
permanent link

answered 03 Nov '11, 05:05

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k243561
accept rate: 17%

edited 03 Nov '11, 06:08

Hi Thomas,

Am not sure i understand

Regards

(03 Nov '11, 06:01) Takudzwa
Replies hidden
1

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...

(03 Nov '11, 08:02) Volker Barth

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');
(04 Nov '11, 09:13) Takudzwa
permanent link

answered 07 Nov '11, 17:31

Breck%20Carter's gravatar image

Breck Carter
26.3k430600866
accept rate: 21%

Thats excellent Breck. Thanks

(08 Nov '11, 01:18) Takudzwa
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×16

question asked: 03 Nov '11, 03:26

question was seen: 2,275 times

last updated: 12 Sep '16, 15:27