NetTalk Central

NetTalk Web Server => Web Server - Ask For Help => Topic started by: Niels Larsen on July 06, 2015, 04:57:18 AM

Title: Best approach to use OVER claus (mssql 2012) for calculating running totals
Post by: Niels Larsen on July 06, 2015, 04:57:18 AM

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

Title: Re: Best approach to use OVER claus (mssql 2012) for calculating running totals
Post by: Bruce on July 07, 2015, 03:10:43 AM

row, or column?

Title: Re: Best approach to use OVER claus (mssql 2012) for calculating running totals
Post by: Niels Larsen on July 07, 2015, 05:43:40 AM

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

Title: Re: Best approach to use OVER claus (mssql 2012) for calculating running totals
Post by: Bruce on July 07, 2015, 11:48:25 PM

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

Title: Re: Best approach to use OVER claus (mssql 2012) for calculating running totals
Post by: Niels Larsen on July 08, 2015, 12:04:27 AM

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

Title: Re: Best approach to use OVER claus (mssql 2012) for calculating running totals
Post by: Bruce on July 08, 2015, 04:17:59 AM

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