NetTalk Central

Author Topic: Best approach to use OVER claus (mssql 2012) for calculating running totals  (Read 4023 times)

Niels Larsen

  • Sr. Member
  • ****
  • Posts: 430
    • View Profile
    • Email
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 11244
    • View Profile
row, or column?

Niels Larsen

  • Sr. Member
  • ****
  • Posts: 430
    • View Profile
    • Email
Column - ofcause. I'm right / left invalid - and obviously row / column too

Bruce

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 11244
    • View Profile
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

  • Sr. Member
  • ****
  • Posts: 430
    • View Profile
    • Email
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 11244
    • View Profile
>> 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