CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
Jande
Jande

Reputation: 1705

Subtract variable from column while variable has remaining value

I have a column with different numerical values. And I have a numeric variable called X with a value of 50000. 

  rownumber           col1 
 --------------------------
     1                5000
     2                1000
     3               10000
     4               12000
     5                 300
     6               35000

 DECLARE @X AS decimal(18,2) = 50000

I need somehow cover the value from col1 to zero while the variable x have value. How to get the following result?

  rownumber            col1      col2         

     1                 5000       0     
     2                 1000       0      
     3                10000       0      
     5                  300       0      
     6                35000     13300

if @X = 1000 then result should be:

 rownumber            col1        col2         

     1                 5000       4000        
     2                 1000       1000       
     3                10000      10000     
     4                12000      12000     
     5                  300        300      
     6                35000      35000

Upvotes: 1

Views: 366

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

You can do this with running sums:

with cte as(select *, sum(col1) over(order by rn) as s from t)
select rn, col1, 0 as col2 from cte where s <= 1000 
union all
select * from (select top 1 rn, col1, s - 1000 as col2 from cte where s > 1000 order by rn)t
union all
select * from (select rn, col1, col1 as col2 from cte where s > 1000 order by rn offset 1 row)t

This is the version that is born in my head without checking. I can not check right now, but I think it should work and it is setbased.

The idea is to make a running sum:

rownumber  col1   s        
1          5000   5000    
2          1000   6000   
3          10000  16000    
4          12000  28000
5          300    28300      
6          35000  63300

Now you are selecting all rows where s <= 50000. This will give you rn{1,2,3,4,5}. Note you make col2 all zeros. In second union you are selecting first row where s > 50000. This will give you rn{6}. Note col2 is 63300 - 50000 = 13300. Now if you have additional rows then you are selecting those in third union except of first row which we already selected in second union.

Upvotes: 1

Related Questions