数据库出货加权平均成本计算问题

今天同事有个问题如下：

经过多重方案尝试，最后选择了 while 循环，欢迎大家提供更优质的方案

代码如下：

--CREATE DATABASE TEST
--GO
--USE TEST
--GO
--create table test
--(
--Style char,
--Color NVARCHAR(8),
--Size char,
--Price decimal(10,2),
--Quantity int,
--InDate DateTime
--)
--GO
--INSERT INTO test VALUES('A','红色','L',100,100,'2018-01-01')
--INSERT INTO test VALUES('B','蓝色','L',50,50,'2018-02-01')
--INSERT INTO test VALUES('A','红色','L',80,50,'2018-03-01')
--INSERT INTO test VALUES('A','蓝色','S',100,100,'2018-04-01')
--INSERT INTO test VALUES('C','红色','M',10,60,'2018-05-01')
--INSERT INTO test VALUES('A','红色','L',111,1,'2018-06-01')

 
declare @num int =110
declare @bnum int = @num
declare @index int = 1
declare @result decimal(10,4) = 0
declare @Quantity int
declare @Price int 
declare @wavg decimal(10,4) = 0
while 1=1
begin
	 SELECT @Quantity =Quantity,@Price=Price FROM (SELECT 
		ROW_NUMBER() OVER(ORDER BY InDate ASC) ID,
		Price,
		Quantity 
	 FROM test where Style='A' and Color = '红色' AND Size ='L') TEMP WHERE ID = @index
	    if @num < @Quantity
		begin	 
		    set @result =  @result + @num * @Price
			print @result  
			break
		end 
		else
		begin
		   set @num = @num - @Quantity  
		   set  @result =  @result + @Quantity * @Price
	    end 
	set @index += 1	
end 
set @wavg= @result / @bnum 
select @wavg wavg