Decreasing a value across multiple rows until 0

  • 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

  • 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

  • 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

  • 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,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

  • 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

  • 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

  • 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

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

  • 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

  • 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

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

  • 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

  • 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

  • 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

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

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

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