reset running total to x amount

Question

reset running total to x amount

faisal_xyz

SSC Enthusiast

Points: 164
More actions
December 22, 2014 at 6:53 am

#299591

Hi
Can anybody help me please? I need to calculate running total which resets when reached to 16, and also needs to calculate remaining amount as paid.
I have attached sample data, so we have two columns Earned, and Used. And we need to calculate Balance, and Paid columns. Many thanks.

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

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

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

I have a partial solution, that will get you in the right direction. I created some DDL for you. This solution is based on the technique described in this article:

Solving the Running Total and Ordinal Rank Problems (Rewritten) By JefF Moden[/url]

--First the sample data

DECLARE @sampledata

table

(some_id int identity primary key,

earned int not null,

used int not null,

balance int null,

paid int null

);

INSERT @sampledata (earned, used) VALUES (5,1),(16,2),(0,2),(2,1);

-- Review:

-- SELECT * FROM @sampledata;

DECLARE @runningtotal int = 0, @paid int = 0;

UPDATE @sampledata

SET @runningtotal = balance =

CASE

WHEN @runningtotal + (earned - used) < 16 THEN @runningtotal + (earned - used)

ELSE 16

END

OPTION (MAXDOP 1);

SELECT * FROM @sampledata;

Note that I can't quickly figure out the paid column (I get what you are trying to do but failed at doing it). Also note the rules section at the end of the article. I may have missed a couple. Again, not a complete solution but something to get you in the right direction.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

LutzM SSC Guru Points: 107049 More actions · Answer 2

What SQL Server version do you use?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

CELKO (12/22/2014)
Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL. And you probably need to read and download the PDF for:
https://www.simple-talk.com/books/sql-books/119-sql-code-smells/
I do not feel like doing your typing from a silly picture :angry:

I put together some sample DDL that should suffice. (I went with the lead by example approach of getting new visitors to post DDL)

And Mr. Celko's solution is...

Drum roll please...

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

This is similar to Itzik Ben-Gan's Depleting Quantities challenge:

http://sqlmag.com/t-sql/t-sql-challenges-replenishing-and-depleting-quantities

It can be solved with a QU as Alan.B has suggested.

However I should warn you that there are those that are not believers in that method. IBG happens to be one of them. When I proposed using a QU for his Depleting Quantities challenge, he explained to me why. And I have seen cases that will break a QU.

I'm fence-sitting on that solution for the time being (even though many have seen me use it and write about it in the past).

I did propose an alternate solution to Mr. Ben-Gan that did work and I haven't heard that he's come up with a better one (he'd probably have written about it by now if he did). It consists of a set-based while loop.

If you're happy with the QU solution Alan provided then use it. If you have concerns about it, let me know and I'll try to dig up what I did for IBG.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St