My subject is to get the price from item which go into stock and put the price into out item by (FIFO order by TranDate) , then calculate the total price of any line of production (" lineid ")
This is what i have done
---------------------------------------------------------------------
-- temp table for resultdeclare @Test table ( stockID int , OriQty decimal(16,2) ,inuse decimal(16,2) , price decimal(16,2) , lineid int , Inid int )
declare @StockIn table (ID int , StockID int ,qty decimal(16,2),Price decimal(16,2), tranDate Date , running int)
insert into @StockIn(ID , StockID , qty , Price , tranDate , running) values
(1,1 , 15 , 430 , '2014-10-09' , 1),
(2,1 , 10 , 431, '2014-12-09' , 2),
(3,1 , 15 , 432, '2015-02-02' , 3),
(4,2 , 15 , 450, '2014-08-05' , 1),
(5,2 , 6 , 450, '2014-10-09' , 2),
(6,2 , 15 , 452, '2015-02-02' , 3)
-- lineid = line production
declare @StockOut table (ID int , StockID int ,qty decimal(16,2), lineid int, tranDate Date)
insert into @StockOut(ID , StockID ,qty , lineid, tranDate )
values
(1,1 , 20 , 2, '2014-10-10'),
(2,1 , 10 , 4, '2014-12-20'),
(3,2 , 12 , 8, '2014-10-01'),
(4,2 , 3 , 8, '2014-10-01') ;
DECLARE @intFlag INT
SET @intFlag = 1 -- initial of loop
WHILE (@intFlag <= (Select Max(ID) from @StockOut) )
BEGIN
declare @stockID int
declare @ids table (ID int , Inuse decimal(16,2))
declare @lineid int
declare @qtyToRemove decimal(16,2) -- get qty from @StockOut
--get data from @StockOut row by row
select @stockID = StockID , @qtyToRemove = qty , @lineid = lineid from @StockOut where ID = @intFlag;
with cte as (
select *, sum(qty) over (order by tranDate ASC , qty DESC , tranDate ASC Rows UNBOUNDED PRECEDING) - @qtyToRemove
as Total FROM @StockIn Where stockID = @stockID )
-- running FIFO from @StockIn and update QTy when stock is running out
, RunningFIFO as (
select cte.StockID , cte.qty AS OriginalQty , Case when
case when 0 > total then cte.qty else cte.qty-total End > 0 then case when 0 > total then cte.qty else cte.qty-total End else 0 End As Inuse
, cte.Price , @lineid AS lineid , id
from cte
)
-- insert result into result table
insert into @test (stockID , OriQty ,inuse , cte.Price , lineid ,Inid ) OUTPUT inserted.Inid , inserted.inuse into @ids select * from RunningFIFO Where Inuse > 0
UPDATE @StockIn set qty = qty - inuse from @ids A inner join @StockIn B on A.ID = b.ID
SET @intFlag = @intFlag + 1
END
Select sum(inuse * price) AS total , lineid from @Test group by lineid
-------------------------------------------------------
But when data are more than thousand , This query run super slow , Could you please help me increase the performance of this query.
- Edited by Hemkoe 14 minutes ago