Recurrent Balance that resets when hitting a value

Question

Recurrent Balance that resets when hitting a value

patricio.carpio

Valued Member

Points: 57
More actions
October 15, 2014 at 10:25 am

#291019

Hello all,
I have query that calculate a running balance, but I need to reset that balance when it reaches 240. Then start calculating the balance again from that value.
For instance, let's see we have the following table:
YearAmmount
200049.95
200179.92
200279.92
2003114.99
2004120.00
2005120.00
2006120.00
2007120.00
200872.00
200943.00
201016.00
2011-68.00
20128.00
2013-72.00
2014-46.00
If I run the following query to get the running balance:
SELECT Year,
Ammount,
SUM(Ammount) OVER(ORDER BY Year ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM _YearlyTotals
I get:
YearAmmountRunningTotal
200049.9549.95
200179.92129.87
200279.92209.79
2003114.99324.78
2004120.00444.78
2005120.00564.78
2006120.00684.78
2007120.00804.78
200872.00876.78
200943.00919.78
201016.00935.78
2011-68.00867.78
20128.00875.78
2013-72.00803.78
2014-46.00757.78
I'm looking to reset the Running Total if it reaches 240, and continue calculating from there:
Something like this:
YearAmmountRunningTotalAdjustment
200049.9549.9549.95
200179.92129.870.00
200279.92209.790.00
2003114.99240.00-84.78
2004120.00240.00-120.00
2005120.00240.00-120.00
2006120.00240.00-120.00
2007120.00240.00-120.00
200872.00240.00-72.00
200943.00240.00-43.00
201016.00240.00-16.00
2011-68.00172.000.00
20128.00180.000.00
2013-72.00108.000.00
2014-46.0062.000.00
Any ideas, please?
Here is the code to create the table and insert data:
CREATE TABLE _YearlyTotals(
[Year] int NULL,
Ammount numeric(18, 2) NULL
) ON [PRIMARY]
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2000,49.95)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2001,79.92)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2002,79.92)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2003,114.99)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2004,120)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2005,120)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2006,120)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2007,120)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2008,72)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2009,43)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2010,16)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2011,-68)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2012,8)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2013,-72)
INSERT INTO _YearlyTotals([Year],Ammount) VALUES (2014,-46)
Thank you for you help!

Viewing 5 posts - 1 through 4 (of 4 total)

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Someone just did a nice blog post on this, but I can't find it!! 🙁 Itzik Ben-Gan or Dwain Camps or maybe Aaron Bertrand?

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

patricio.carpio Valued Member Points: 57 More actions · Answer 2

Hello,

I got the book Training Kit (Exam 70-461) Querying Microsoft SQL Server 2012 and read chapter 4 several times, especially the part it talks about CTEs.

I found out that it wasn't difficult to create a query like this if you understand how CTEs work.

This is the query I came up with:

WITH YearCTE AS

(

SELECT[Year],

Ammount,

Ammount AS RunningTotal,

CASE

WHENAmmount > 240

THEN240 - Ammount

ELSE0

END AS Adjustment

FROM_tmp_YearlyTotals

WHERE[Year] = (SELECT MIN([Year]) FROM _tmp_YearlyTotals)

UNION ALL

SELECTY.[Year],

Y.Ammount,

CASE

WHEN(C.RunningTotal + Y.Ammount) > 240

THEN240

ELSE(C.RunningTotal + Y.Ammount)

END AS RunningTotal,

CASE

WHEN(C.RunningTotal + Y.Ammount) > 240

THEN240 - (C.RunningTotal + Y.Ammount)

ELSE0

END AS Adjusment

FROM_tmp_YearlyTotals AS Y

INNER JOIN YearCTE AS C

ON Y.[Year] = C.[Year] + 1

)

SELECT * FROM YearCTE

I hope is useful for somebody else with this same kind of problem.

🙂

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Be careful. Recursive CTEs can be DREADFULLY poor performers!!

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

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 4

TheSQLGuru (11/12/2014)
Be careful. Recursive CTEs can be DREADFULLY poor performers!!

Absolutely agreed!

How many rows are we talking about here?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)