running total max value by group

Question

running total max value by group

Talvin Singh

Ten Centuries

Points: 1288
More actions
February 15, 2018 at 1:45 pm

#343607

Hi all,
i want a running total but with 2 clauses for the counter, the counter restarts when the total is 10, and it restarts to 1 when a new group starts:
ID Value Ref 1 4 1 1 4 1 1 8 2 1 10 3 2 5 1 2 5 1 3 1 1 3 5 1 3 5 2 3 6 3
as you can see above the Ref column increments when goes over 10, but also resets when there is a new ID.
the Value column will be in ascending order for each group.
SELECT * INTO #TestTable FROM (VALUES (1, 4) ,(1,4) ,(1, 8) ,(1,10) ,(2,5) ,(2,5) ,(3,1) ,(3,5) ,(3,5) ,(3,6) )v([ID],[Value]);

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

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

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

Why is this different from your previous post?
https://www.sqlservercentral.com/Forums/1922239/Increament-id-if-running-total-for-group-exceeds-value

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

Talvin Singh Ten Centuries Points: 1288 More actions · Answer 2

Luis Cazares - Thursday, February 15, 2018 1:52 PM
Why is this different from your previous post?
https://www.sqlservercentral.com/Forums/1922239/Increament-id-if-running-total-for-group-exceeds-value

Similar but I need the basket ID to restart. I have played around with that code but not getting it to work

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Talvin Singh - Thursday, February 15, 2018 2:00 PM
Luis Cazares - Thursday, February 15, 2018 1:52 PM
Why is this different from your previous post?
https://www.sqlservercentral.com/Forums/1922239/Increament-id-if-running-total-for-group-exceeds-value
Similar but I need the basket ID to restart. I have played around with that code but not getting it to work

There is nothing in your table to guarantee the processing order within an ID. Without such a thing, this is doomed to failure.

If the goal is to process in order by both the ascending order of both the ID and the Value, then such a thing can be done quite easily but if you're trying for something like load balancing, then that won't work.

What is this for? Load Balancing? Packing Boxes/Bins? Assigning tasks by duration? What?

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