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) |
Based on your new sample data and Thomas's suggestions, the following might be what you're lokking for:
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:
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 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
|