Running totals within Cursors, Can I replace with something better?

Question

Running totals within Cursors, Can I replace with something better?

Paul Giles-Randall

Mr or Mrs. 500

Points: 542
More actions
July 25, 2016 at 7:54 am

#312013

Firstly, this is a system that I have inherited.
I have a cursor that pulls out a set of ids and amounts. The data is as follows:
ID | Amount | Status
-----------------------------------------
1 | 15.00 | Paid
2 | 3.00 | Paid
3 | 10.00 | Awaiting
4 | 12.00 | Awaiting
The cursor loops and adds up the amounts until it reaches a set value, in this case we will use 20.00
What I need to replicate is the following:
The cursor, is holding a running total, when this running total goes over 20, then, as in the example above i need to do the following:
ID | Amount | Status
-----------------------------------------
1 | 15.00 | Paid
2 | 3.00 | Paid
3 | 8.00 | Paid
4 | 12.00 | Cancelled
5 | 8.00 | Brought Forward
As you can see, ID 3 in now showing as a part payment where the remaining balance over 20 becoming a brought forward payment ID 5.
On top of this any outstanding payments have become cancelled, Id 4.
Must I use cursors or is there a better way?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

There are 2 other ways that might possibly help you.

One is to use the window functions capabilities introduced in SQL Server 2012: http://sqlmag.com/sql-server-2012/how-use-microsoft-sql-server-2012s-window-functions-part-1

The second one is to use the Quirky update which is explained and compared in here: http://www.sqlservercentral.com/articles/T-SQL/68467/

If you don't feel comfortable with these methods yet, you can start by correctly defining the cursors to improve their performance: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx

http://sqlperformance.com/2012/09/t-sql-queries/cursor-options

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

pietlinden SSC Guru Points: 63503 More actions · Answer 2

Oh, inherited mess... my favorite! Here's a quick example of using a window function.

SELECT pmts.ID

, pmts.Amount

, pmts.[Status]

, SUM([Amount]) OVER (ORDER BY ID

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW) AS RunAmt

FROM

(SELECT 1 AS ID

, 15 AS Amount

, 'Paid' As [Status]

UNION ALL

SELECT 2, 3, 'Paid'

UNION ALL SELECT 3,10,'Awaiting'

UNION ALL

SELECT 4, 12,'Awaiting') pmts;

If you wanted to do a GROUP BY in your running total, it would be like this (note the PARTITION BY):

SELECT pmts.OwnerID

, pmts.ID

, pmts.Amount

, pmts.[Status]

, SUM([Amount]) OVER (PARTITION BY OwnerID ORDER BY ID

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW) AS RunAmt

FROM

(SELECT 'a' AS OwnerID

, 1 AS ID

, 15 AS Amount

, 'Paid' As [Status]

UNION ALL

SELECT 'a',2, 3, 'Paid'

UNION ALL SELECT 'a',3,10,'Awaiting'

UNION ALL

SELECT 'b',4, 12,'Awaiting') pmts;

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

http://sqlperformance.com/2012/07/t-sql-queries/running-totals

http://sqlperformance.com/2014/01/t-sql-queries/grouped-running-totals

Note that Aaron does NOT do the Quirky Update correctly in his stuff. But with 2012 Windowing Functions are the way to go in any case, at least when done properly (i.e. with ROWS and not RANGE).

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 4

Paul Giles-Randall (7/25/2016)
Firstly, this is a system that I have inherited.
I have a cursor that pulls out a set of ids and amounts. The data is as follows:
ID | Amount | Status
-----------------------------------------
1 | 15.00 | Paid
2 | 3.00 | Paid
3 | 10.00 | Awaiting
4 | 12.00 | Awaiting

Quick questions, how large is your data set?

a) Distinct count of IDs

b) Number of transactions in total

c) Number of periods

😎

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Paul Giles-Randall (7/25/2016)
What I need to replicate is the following:
The cursor, is holding a running total, when this running total goes over 20, then, as in the example above i need to do the following:
ID | Amount | Status
-----------------------------------------
1 | 15.00 | Paid
2 | 3.00 | Paid
3 | 8.00 | Paid
4 | 12.00 | Cancelled
5 | 8.00 | Brought Forward

I guess it must be

3 | 2.00 | Paid

Then it would make sense.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 6

I guess there is no point of getting rid of the cursor.

You may mark a payment "Paid" only after the payment has been actually made.

And I bet the payments are made one by one.

Which means using some sort of a cursor (loop) anyway.

What you need to do is within the loop, when the next "Awaiting" payment is picked up for processing calculate the already paid amount (SUM where Status = 'Paid'), see if adding the current one will go over the limit and if yes - split it accordingly into "Paid" and "Brought forward" parts.

All other "Awaiting" to be marked "Cancelled" (UPDATE where Status = 'Awaiting')

_____________
Code for TallyGenerator