Hi everybody,

Here is an interesting SQL challenge (IMHO)

http://forums.asp.net/p/1351246/2762267.aspx#2762267

This is my solution.

declare @Test table (ID int Identity, BatchID char(2), Quantity int)

insert into @Test values ('B1', 100)
insert into @Test values ('B1', 200)
insert into @Test values ('B1', 300)
insert into @Test values ('B1', 500)
insert into @Test values ('B2', 500)

declare @Amount int
set @Amount = 500

;with Cte_RunningTotal (ID, BatchID, Quantity, Total) as 
(select *, (select SUM(Quantity) as Total from @Test T where T.BatchID = 'B1' AND Quantity <= T1.Quantity) from @Test T1 where T1.BatchID = 'B1')

--select * from Cte_RunningTotal 

update T set quantity = 

case

                  when Total <= @Amount then 0

                  when Total > @Amount and Total - T.Quantity <= T.Quantity then (Total -@Amount)

                 when Total > @Amount and Total - T.Quantity > T.Quantity then  T.Quantity end 
                 from @Test T inner join Cte_RunningTotal on T.ID = Cte_RunningTotal.ID 

select * from @Test 

Do you think it is correct? What if I add records in different order?

Thanks in advance.