with qry as 
(select 1 ID, -10 amt union all
 select 2 ID,  40 amt union all
 select 6 ID, -20 amt union all
 select 4 ID,  30 amt union all
 select 3 ID,  20 amt )
select qry.*,sum(amt) over() allsum,sum(amt) over(order by amt) rsum  from qry

with qry as 
(select 1 ID,  50 amt union all
 select 4 ID, -20 amt union all
 select 2 ID, -20 amt union all
 select 6 ID,  80 amt union all
 select 3 ID, -90 amt )
, qry1 as (select  qry.* --TOP(100)PERCENT
,sum(amt) over() allsum
from qry )
, qry2 as (select  qry1.* from qry1 WHERE Sign(amt) = Sign(allsum))
, qry3 as (select *
,sum(amt) over(order by amt ROWS UNBOUNDED PRECEDING) rsum
 from qry2 )
select * 
, iif( Abs(rsum) <= Abs(allsum), amt, iif(Abs(amt + allsum) >  Abs(rsum), amt - (rsum - allsum) ,0))   new
, iif( Abs(rsum) <= Abs(allsum), 1, iif(Abs(amt + allsum) >  Abs(rsum), (0.0 + amt - (rsum - allsum)) / amt ,1))   scalor
from qry3