Running total between 2 sql tables in Sql

Declare @Table1 table
(
	StockCode varchar(10),
	Warehouse varchar(10),
	AvailQty decimal(20,6),
	SalesQty decimal(20,6),
	Remaining decimal(20,6)
)
insert into @Table1
values
('0422 2001', 'BR-DROP', 5200000.000000, 1981299.230000, 3218700.770000);

Declare @Table2 table
(
	Ser3 varchar(10),
	StockCode varchar(10),
	Ser varchar(30),
	Warehouse varchar(10),
	TrnType varchar(1),
	Bin varchar(10),
	Qty decimal(20,6),
	CusInvoice varchar(10)
)

insert into @Table2
values
('201508', '0422 2001', '2015080313165079', 'BR-DROP', 'A', 'BR-DROP', 100000.000000, NULL),                   
('201509', '0422 2001', '2015091413551294', 'BR-DROP', 'A', 'BR-DROP', 100000.000000, NULL),  
('201511', '0422 2001', '2015111216063103', 'BR-DROP', 'A', 'BR-DROP', 2000000.000000, NULL),                  
('201602', '0422 2001', '2016021214424950', 'BR-DROP', 'A', 'BR-DROP', 3000000.000000, NULL);

;with cte
as
(
   select *, row_number() Over (Order By Ser3) idx
   from @Table2
)
, cte2
as
(
	select a.*, b.AvailQty, b.SalesQty, b.Remaining, (select sum(qty) total from cte a2 where a2.idx <= a.idx) CumulativeTotal
	from cte a inner join @Table1 b
	on a.StockCode = b.StockCode
)
select Ser3, StockCode, Ser, Warehouse, TrnType, Bin, Qty, CusInvoice,
case when CumulativeTotal-SalesQty = Remaining then
	Qty
else
	case when SalesQty >= CumulativeTotal then
		0
	else
		CumulativeTotal- SalesQty 
	end
end	CalculatedTotal
from cte2

Select allOpen in new window