Sql addition query



  • If the sum of a group comes as negative. The negative numbers are to be increased (biggest negative first) to bring the total sum as 0

    Example

    Group 1 has this 4 values -4,-3,1,1

    The total is negative

    Thus it gets tweaked to

    0,-2,1,1 .

    -4 becomes 0 as it is the highest negative..

    the balance is removed from -3 to make it to -2..

    Thus the total is 0.

    Can anybody please help in this



  • I've added a few extra values to make sure I'm covering some edge cases.

    The idea here is to look at the rolling sum, and figure out the point where the sum of the negative values goes past the overall sum. Before that point, the newvals are 0, after that point, they're the original value, and at that point, it's the difference between the rolling sum and the overall sum.

    with vals as (select * from (values (1), (-3), (1), (-4), (-5), (-6), (2)) v(val))
    , 
    ordered as
    (
    select v.val
        , ROW_NUMBER() over (order by v.val) as rownum
        , sum(v.val) over () as OverallTotal
    from vals v
    )
    ,
    rollingsum as
    (
    select o.*
        , sum(case when o.val < 0 then o.val else 0 end) over (order by o.rownum rows between unbounded preceding and current row) as RollingSumNegsByRownum
        , sum(case when o.val < 0 then o.val else 0 end) over (order by o.rownum rows between unbounded preceding and 1 preceding) as RollingSumNegsByRownumPrev
    from ordered o
    )
    select r.val
        , case 
            when r.OverallTotal < 0 and r.val < 0 and r.OverallTotal  r.RollingSumNegsByRownum then r.RollingSumNegsByRownum - r.OverallTotal
            else r.val
        end as NewVal
    from rollingsum r
    ;
    

    Results:

    -6  0
    -5  0
    -4  -1
    -3  -3
    1   1
    1   1
    2   2
    

    And results on the original list:

    -4  0
    -3  -2
    1   1
    1   1
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2