Increase the value of a column

I want create a CTE or something that subtract a know value from a sum() of value.

How can I solve this, I tried to use LAG() on my CTE but it faulted when my value is negative.

This is what I tried to do:

DROP TABLE IF EXISTS #table;
CREATE table #table (
       PR  Varchar(50),
       Rata  int ,
       Plafond  int
)

insert into #table
select 'PR1',3,10
Union
SELECT 'PR1',4,10
Union
SELECT 'PR1',6,10
Union
SELECT 'PR1',5,10
Union
SELECT 'PR2',5,10

;WITH  cte as (
select
       PR
       ,Plafond
       ,RATA 
    ,ROW_NUMBER() OVER (PARTITION by PR  ORDER BY PR) as cont
from #table
)

SELECT  
*
, Plafond - SUM (Rata) OVER ( partition by PR,cont  order by PR) AS result
FROM cte

This is the result of the query

PR  Plafond RATA    cont    result
PR1 10  3   1   7
PR1 10  4   2   6
PR1 10  5   3   5
PR1 10  6   4   4
PR2 10  5   1   5

And this is what I expect from the logic of the query but it isn’t the final result that I want:

PR  Plafond RATA    cont    Result
PR1 10      3       1       7
PR1 10      4       2       3
PR1 10      5       3       -2
PR1 10      6       4       -8
PR2 10      5       1       5

And this is (the final results) what I expecting to get out apply a WHERE clause or something, that transform the “Rata” when my “Result” is negative. The new value of “Rata” become the difference between the last “Result” and Zero value, in this case the value of “Rata”=5 (cont=3) become 3. The difference between residue “Result” and “Rata” make the new “Result” 0.

PR  Plafond RATA    cont    Result
PR1 10      3       1       7
PR1 10      4       2       3
PR1 10      3       3       0
PR1 10      0       4       0
PR2 10      5       1       5

Answer

Just a guess…

;WITH  cte as (
select
       PR
       ,Plafond
       ,RATA 
    ,ROW_NUMBER() OVER (PARTITION by PR  ORDER BY PR) as cont
from #table
)

SELECT  
*
, case when Plafond - SUM (Rata) OVER ( partition by PR  order by CONT) < 0 then 0 else Plafond - SUM (Rata) OVER ( partition by PR  order by CONT) end AS result
, case 
    when Plafond - SUM (Rata) OVER ( partition by PR  order by CONT) >= 0 then rata
    else iif (rata > abs(Plafond - SUM (Rata) OVER ( partition by PR  order by CONT)), rata-abs(Plafond - SUM (Rata) OVER ( partition by PR  order by CONT)), 0)
        --case when rata > abs(Plafond - SUM (Rata) OVER ( partition by PR  order by CONT)) then rata-abs(Plafond - SUM (Rata) OVER ( partition by PR  order by CONT))
        --else 0
        --end
    end as ratanew
FROM cte;



;WITH  cte as (
select
       PR
       ,Plafond
       ,RATA 
    ,ROW_NUMBER() OVER (PARTITION by PR  ORDER BY PR) as cont
from #table
)

SELECT  
*
, case when Plafond - SUM (Rata) OVER ( partition by PR  order by CONT) < 0 then 0 else Plafond - SUM (Rata) OVER ( partition by PR  order by CONT) end AS result
FROM cte;