Decreasing a value across multiple rows until 0

Question

Decreasing a value across multiple rows until 0

greg.senne

SSC Veteran

Points: 252
More actions
July 1, 2016 at 12:44 pm

#311459

If I have several records in a table such as this:
100
50
50
20
And I want to subtract 175, I want a result like this:
0
0
25
20
How would I go about doing this?
Thanks
Greg

Viewing 15 posts - 1 through 15 (of 19 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

greg.senne (7/1/2016)
If I have several records in a table such as this:
100
50
50
20
And I want to subtract 175, I want a result like this:
0
0
25
20
How would I go about doing this?
Thanks
Greg

care to explain your logic for your expected results ?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

This is a variation on the running totals problem.

The logic is that 100 is subtracted from the first value, 50 is subtracted from the second value, and 25 from the third value.

100+50+25 = 175 "subtracted"

My question is: Do you want to update these values, or produce a new output set?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

The Dixie Flatline (7/1/2016)
This is a variation on the running totals problem.
The logic is that 100 is subtracted from the first value, 50 is subtracted from the second value, and 25 from the third value.
100+50+25 = 175 "subtracted"
My question is: Do you want to update these values, or produce a new output set?

Hi Dixie....seems OP has gone AWOL !

would be interested to see your thoughts on a solution...given following sample data

CREATE TABLE #sometable(

Id INTEGER NOT NULL

,somevalue INTEGER NOT NULL

);

INSERT INTO #sometable(Id,somevalue) VALUES (1,100);

INSERT INTO #sometable(Id,somevalue) VALUES (1,50);

INSERT INTO #sometable(Id,somevalue) VALUES (1,20);

INSERT INTO #sometable(Id,somevalue) VALUES (2,100);

INSERT INTO #sometable(Id,somevalue) VALUES (2,300);

INSERT INTO #sometable(Id,somevalue) VALUES (2,100);

SELECT * FROM #sometable

--DROP TABLE #sometable

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Hey JL: You have two groups of IDs. What result are you expecting?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

The Dixie Flatline (7/1/2016)
Hey JL: You have two groups of IDs. What result are you expecting?

I dont know <grin>...just posted some sample data based on OP post...suggest we wait to see if OP replies.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

Fine with me. Here is some reading for anyone else who wanders along.

Solving the Running Total and Ordinal Rank Problems (Rewritten) by Jeff Moden

(I'm oddly unable to create a hyperlink.)

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003946 More actions · Answer 7

Since this is 2016, seems like a Lead or Lag would be the way to do this.

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

Hey Jeff. I've never done that, but then I don't usually have to do running totals in SQL. Going to go read up on that now.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

Just did some quick reading. Didn't find a LAG solution, but windowed functions are awesome for this.

In the OPs problem at hand, we need something other than ID to ensure sequence of rows. I'm creating one using ROW_NUMBER() but it really should be in the base table

; with cte as (select ID, row_number() over(Order by (select null)) as CalcID, SomeValue

from #sometable)

,cte2 as (SELECT ID, CalcID, SomeValue, sum(SomeValue) over(Order by CalcID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RunningTotal

from cte)

,cte3 as (SELECT ID, CalcID, SomeValue,RunningTotal, 175-RunningTotal as Remainder

from cte2)

select *, case when Remainder > 0 then 0

when ABS(Remainder) <= someValue then SomeValue + Remainder

else Somevalue

end as Newvalue

from cte3

Have a great weekend, everyone.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1003946 More actions · Answer 10

The Dixie Flatline (7/1/2016)
Just did some quick reading. Didn't find a LAG solution, but windowed functions are awesome for this.
In the OPs problem at hand, we need something other than ID to ensure sequence of rows. I'm creating one using ROW_NUMBER() but it really should be in the base table
; with cte as (select ID, row_number() over(Order by (select null)) as CalcID, SomeValue
from #sometable)
,cte2 as (SELECT ID, CalcID, SomeValue, sum(SomeValue) over(Order by CalcID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RunningTotal
from cte)
,cte3 as (SELECT ID, CalcID, SomeValue,RunningTotal, 175-RunningTotal as Remainder
from cte2)
select *, case when Remainder > 0 then 0
when ABS(Remainder) <= someValue then SomeValue + Remainder
else Somevalue
end as Newvalue
from cte3
Have a great weekend, everyone.

I have, very unfortunately, been grouping Windowing Functions with Preceding as Lag and it's a habit that I need to break. You did it the way I meant.

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

greg.senne SSC Veteran Points: 252 More actions · Answer 11

Hey, I'm still here! It was a holiday weekend! Guess I should've checked in over the weekend...

So, this procedure is being used for payment deductions. The base table holds deductions, and there could be multiple deductions.

For example, Payment A could have a deduction in the table of $100, and of $50. Now, I have a payment that needs issued for $125. I've already calculated that the $125 payment won't happen. Now, I need to adjust my deductions table to clear the first $100, and change the $50 to $25.

I know I could build this into a SQL function with a loop and a variable. I was wondering if there was a SQL statement that would do something similar to what I'm looking for without having to build a function / loop for it.

Thanks!

Greg

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 12

greg.senne (7/5/2016)
Hey, I'm still here! It was a holiday weekend! Guess I should've checked in over the weekend...
So, this procedure is being used for payment deductions. The base table holds deductions, and there could be multiple deductions.
For example, Payment A could have a deduction in the table of $100, and of $50. Now, I have a payment that needs issued for $125. I've already calculated that the $125 payment won't happen. Now, I need to adjust my deductions table to clear the first $100, and change the $50 to $25.
I know I could build this into a SQL function with a loop and a variable. I was wondering if there was a SQL statement that would do something similar to what I'm looking for without having to build a function / loop for it.
Thanks!
Greg

please post some sample data ( see my earlier example in this thread) and your expected results based on the sample data.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

daniel.shepherd Grasshopper Points: 14 More actions · Answer 13

OP is my supervisor and posted this question on my behalf.

Transaction table - MerchantID, TransAmount

20296, 200

20331, 90.58

Corrections table - MerchantID, CorrectionAmount, CreatedOn

20296, 100.00, 6/1/16

20331, 50.00, 6/1/16

20331, 50.00, 6/5/16

We already have the statements to group the total corrections together and calculate CorrectionAmount per merchant. What we need is a way to subtract from the TransAmount the CorrectionAmount, in order from oldest to most recent, until either the TransAmount or all the CorrectionAmount (per merchantID) is 0, and update the CorrectionAmount with how much was able to be subtracted (if the whole amount wasnt able to be).

For example, using the data listed, we should end up with the following data:

Transaction table - MerchantID, TransAmount

20296, 100

20331, 0

Corrections table - MerchantID, CorrectionAmount, CreatedOn

20296, 0, 6/1/16

20331, 0, 6/1/16

20331, 9.42, 6/5/16

Jeff Moden SSC Guru Points: 1003946 More actions · Answer 14

greg.senne (7/5/2016)
Hey, I'm still here! It was a holiday weekend! Guess I should've checked in over the weekend...
So, this procedure is being used for payment deductions. The base table holds deductions, and there could be multiple deductions.
For example, Payment A could have a deduction in the table of $100, and of $50. Now, I have a payment that needs issued for $125. I've already calculated that the $125 payment won't happen. Now, I need to adjust my deductions table to clear the first $100, and change the $50 to $25.
I know I could build this into a SQL function with a loop and a variable. I was wondering if there was a SQL statement that would do something similar to what I'm looking for without having to build a function / loop for it.
Thanks!
Greg

Have you tried morphing the solution that The Dixie Flatline posted?

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