-----надо продать 15 штук
declare @to_sale table(itemid int,qty int)
insert into @to_sale 
select 1,15
-----на складе 2 партии товара 
declare @store table(itemid int,qty int,partid int)
insert into @store
select 1,0,1
union all
select 1,10,2
union all
select 1,10,3
union all
select 1,10,4
union all
select 1,10,5;
select * from @store
;

with cte 
as
(
   select itemid,partid,qty,(select sum(qty) from @store s1 where s.itemid=s1.itemid and s.partid>=s1.partid) as tot
   from @store s 
  
)
-------списываем с хранилища 
select 
  case when s.qty-(tot-c.qty)>c.qty then c.qty 
       when s.qty-(tot-c.qty)<0      then 0
       else s.qty-(tot-c.qty)
  end     , 
  c.itemid,c.partid
from cte c join @to_sale s 
on c.itemid=s.itemid

create table #t(id int, Ostatki int, Ostatkiafter int)

insert #t(id, Ostatki) values(1, 10)
insert #t(id, Ostatki) values(2, 10)
insert #t(id, Ostatki) values(3, 10)
insert #t(id, Ostatki) values(4, 10)

DECLARE @qty int 
SET @qty = 15
select t.id, Ostatki,  
qut = case when sum(Ostatki) over (order by t.id) - @qty < 0 then @qty 
                    when sum(Ostatki) over (order by t.id) - @qty < Ostatki then Ostatki - (sum(Ostatki) over (order by t.id) - @qty)
                    else 0 end,
Ostatkiafter= case when sum(Ostatki) over (order by t.id) - @qty < 0 then 0 
                    when sum(Ostatki) over (order by t.id) - @qty < Ostatki then sum(Ostatki) over (order by t.id) - @qty
                    else Ostatki end
from #t t 
ORDER BY t.id

drop table #t

DECLARE @i Int, @a Int
SET @i = 0
SET @a = 0
WHILE @i<=(SELECT MAX([Числа]))
BEGIN
@a = (SELECT * FROM [Числа] WHERE [Числа] = @a) + 1
END

SELECT SUM([Числа]) from [Числа]

SET STATISTICS TIME ON;
create table #t(id int, Ostatki int, Ostatkiafter int)

insert #t(id, Ostatki) values(1, 10)
insert #t(id, Ostatki) values(2, 10)
insert #t(id, Ostatki) values(3, 10)
insert #t(id, Ostatki) values(4, 10)
DECLARE @qty int 
SET @qty = 15
select * from(
select t.id, Ostatki,  
qut = case when sum(Ostatki) over (order by t.id) - @qty < 0 then @qty 
                    when sum(Ostatki) over (order by t.id) - @qty < Ostatki then Ostatki - (sum(Ostatki) over (order by t.id) - @qty)
                    else 0 end,
Ostatkiafter= case when sum(Ostatki) over (order by t.id) - @qty < 0 then 0 
                    when sum(Ostatki) over (order by t.id) - @qty < Ostatki then sum(Ostatki) over (order by t.id) - @qty
                    else Ostatki end
from #t t


) as a
WHERE qut > 0 
drop table #t