CODEWITHSUNDEEP
delphidelphi-7firebirdfirebird2.5cumulative-sum
Cristian Trein
Cristian Trein

Reputation: 49

How to calculate cash flow using calc fields?

I'm programming a simple cash flow app, using Delphi 7, firebird 2.5 with InterBase components. The table in question was created like this:

create table movimentos
(
id  d_inteiro not null,
date_start d_data not null,
date_end    d_data,
value   d_valor,
user    d_inteiro,
tipo_movimento d_inteiro not null, -- Indicates if it is Debit or Credit
primary key(id),
foreign key(user) references usuarios(id),
foreign key(tipo_movimento) references tipoMovimento(id)
)

I added a calculated field in the query inside my DM and connected a DBGrid to it. What it basically has to do is to show my balance, using sum when the entry is credit( especified in the db) and sub when debit( duh).

For this, I have to get the value of the LAST balance row, sum(or sub) the value of the new entry and put this new ammount in the new insertion.

Well, I know how to calculate values in the same row and put it in another column, but cannot find anything about getting previous row value and using it in the next.

What would be the simpler way to accomplish that?

Edited: Complementing my question. What I would like to do is somehting like this(simplifying the table a little bit):

Id   date_start   value   balance  
1    03/05/15     +200     200  
2    03/08/15     +1000   1200  
3    03/09/15     -300     900

Now, if I added a new entry for date 03/06/15 all the rows would have to be recalculated, like this: 

Id   date_start   value   balance  
1    03/05/15     +200     200
4    03/06/15     +500     700
2    03/08/15     +1000   1700  
3    03/09/15     -300    1400

How to create, calculate and show this BALANCE column is basically what i'm looking for.
Thanks Again.

Upvotes: 0

Views: 967

Answers (1)

Amir Rahimi Farahani
Amir Rahimi Farahani

Reputation: 1590

InterBase components do not support InternalCalc fields, which are suitable for such client-side calculations. Although it is better to do that server-side.

In Firebird you can use context variables to calculate running totals:

SELECT m.*,
  CAST(rdb$get_context('USER_TRANSACTION', 'BALANCE') AS d_valor) AS BALANCE,
  rdb$set_context('USER_TRANSACTION', 'BALANCE', COALESCE(CAST(rdb$get_context('USER_TRANSACTION', 'BALANCE') AS d_valor), 0) + m.value) AS DUMMY
FROM movimentos m
ORDER BY date_start

Another solutions would be doing your desired calculations inside a stored procedure using a FOR SELECT and iterating over data.

BALANCE = 0;

FOR SELECT id, data_start, value
FROM movimentos
ORDER BY date_start
INTO :ID, :DATA_START, :VALUE
DO
BEGIN
  BALANCE = BALANCE + VALUE;
  SUSPEND;
END

To apply Debit/Credit use IIF to convert tipo_movimento to +1/-1, if it is not:

value * IIF(tipo_movimento = ?, +1, -1)

Upvotes: 1

Related Questions