Best approach to use OVER claus (mssql 2012) for calculating running totals

[Niels Larsen]

Hi

I have a table with customer ledger entries and want a row for calculating running totals.
Do I have to use a Memory table or is there an easier way.
Is there someone who has experience with this?

Regards Niels

[Bruce]

row, or column?

[Niels Larsen]

Column - ofcause. I'm right / left invalid - and obviously row / column too

[Bruce]

ok, cool.

You can of course add columns to your browse which are not in the table. You might use a local-variable as a placeholder - or indeed you might just do and in-row calculation. So _displaying_ a calculated value is really easy.

The slightly harder bit is the whole mechanism for calculating running totals. You need to add code for that - and the approach you take will depend on how you want to do it.

Personally I have a function that I call when the first record is loaded, which gets the "balance brought forward" - then you keep adding / subtracting to that as the browse populates. I'd do all of this in the ValidateRecord embed point in the browse.

If you need more come to the webinar on Thursday and we can go through it.

cheers
Bruce

[Niels Larsen]

Thanks Bruce.

I see. But what i the user press "Last" - how do you then get all the records calculated?

I'm on vacation but I'll try to sneak out of the caravan and attend on Thursday.

Regards NIels

[Bruce]

>> I see. But what i the user press "Last" - how do you then get all the records calculated?

that's why you need a "BroughtForward" function which calculates the value up to the row before the first row in the browse.

cheers
Bruce