November 26, 2009 at 8:50 am
Hi all,
I have the following that product the inventory moving report, based on FIFO. Currently, I'm using the cursor to get the report. However this is taking a bit to long to run with data from production. The FIFO challenge,
http//ask.sqlservercentral.com/questions/826/the-fifo-stock-inventory-sql-problem , give me some direction on rewriting this as set-base solution, but I can't seem to put this all together.
thanks
IF OBJECT_ID('tempdb..#tmptb1','U') IS NOT NULL
drop table #tmptb1
IF OBJECT_ID('tempdb..#Inventory','U') IS NOT NULL
drop table #Inventory
select identity (int, 1,1) ID, prodid,tranid, qty into #tmptb1 from (
select 101 as prodid, 1 as tranid, -30 as qty
union all
select 101,2,20
union all
select 101,3,-5
union all
select 102, 1, 10
union all
select 102,2,20
union all
select 102,3,20
union all
select 103, 1, -10
union all
select 103,2,20
union all
select 103,3,-5
union all
select 104,1,20
union all
select 104,2,-20
union all
select 104,3,-20
union all
select 100,1,30
union all
select 100,2,10
union all
select 107,1,-30
union all
select 107,2,-10
union all
select 105,1,-10
union all
select 106,1,40
)x
DECLARE @Qty MONEY,
@Diff INT,
@input_ProdID INT,
@input_tranid INT,
@input_Qty MONEY,
@output_ProdID INT,
@output_tranid INT,
@output_Qty MONEY,
@curr_input_Qty MONEY,
@curr_output_Qty MONEY,
@input_flag INT,
@output_flag INT,
@prev_in INT,
@prev_out INT,
@prev INT,
@err1 INT,
@err2 INT
Declare InPut Cursor FORWARD_ONLY READ_ONLY FOR
select ProdID,tranid, Qty
from #tmptb1
WHERE SIGN(Qty) = 1
order by prodid,tranid
Declare OutPut Cursor FORWARD_ONLY READ_ONLY FOR
select ProdID,tranid, abs(Qty)Qty
from #tmptb1
WHERE SIGN(Qty) <> 1
order by prodid,tranid
CREATE TABLE #Inventory
(id int identity(1,1),
ProdID INT,
inputProdID int,
outputProdID int,
inputID INT,
outPutID int,
inputQty money,
outputQty money
)
set @err1=0
set @err2=0
set @input_flag=0
set @output_flag=0
set @prev=0
set @prev_in=0
set @prev_out=0
set @curr_input_Qty=0
set @curr_output_Qty=0
Open InPut
fetch next from InPut into
@input_ProdID,@input_tranid, @input_Qty
set @err1 = @err1 + @@Fetch_Status
Open OutPut
fetch next from OutPut into
@output_ProdID,@output_tranid, @output_Qty
set @err2 = @err2 + @@Fetch_Status
select @prev_in=0,@prev_out=0
select @curr_input_Qty=@input_Qty,@curr_output_Qty=@output_Qty
while (@err1=0 or @err2=0)
begin
SELECT @Diff = CASE
WHEN @err1<0 then 1
WHEN @ERR2<0 then 2
WHEN @input_ProdID = @output_ProdID THEN 0
WHEN @input_ProdID < @output_ProdID then 1
WHEN @input_ProdID > @output_ProdID THEN 2
END,
@prev = CASE
when @err2 < 0 then @input_ProdID
when @err1 < 0 then @output_ProdID
WHEN @input_ProdID <= @output_ProdID then @input_ProdID
WHEN @input_ProdID > @output_ProdID THEN @output_ProdID
END,
@Prev_in=
case when (@diff =0 and (@curr_input_Qty <= @curr_output_Qty)) then 0
when (@diff =0 and (@curr_input_Qty > @curr_output_Qty)) then @curr_input_Qty-@curr_output_Qty
when (@diff =1) then @curr_input_Qty
when (@diff =2) then @curr_input_Qty
end,
@Prev_out=
case when (@diff =0 and (@curr_input_Qty <= @curr_output_Qty)) then @curr_output_Qty-@curr_input_Qty
when (@diff =0 and (@curr_input_Qty > @curr_output_Qty)) then 0
when (@diff =1) then @curr_output_Qty
when (@diff =2) then @curr_output_Qty
end
if (@input_ProdID <= @prev and @err1=0)
begin
select
@curr_input_Qty=
case when (@diff =0 and @err2 <0) then @curr_input_Qty
when (@diff =0 and (@curr_input_Qty <= @curr_output_Qty)) then @curr_input_Qty
when (@diff =0 and (@curr_input_Qty > @curr_output_Qty)) then @curr_output_Qty
when (@diff =1) then @Prev_in
when (@diff =2) then @curr_input_Qty
end,
@curr_output_Qty=
case when (@diff =0 and (@curr_input_Qty <= @curr_output_Qty)) then @curr_input_Qty
when (@diff =0 and (@curr_input_Qty > @curr_output_Qty)) then @curr_output_Qty
when (@diff =1) then 0
when (@diff =2) then @Prev_out
end,
@output_Flag=
case
when (@err1 < 0 or @err2 < 0)then 1
when (@diff =0 and @Prev_in <= @Prev_out) then 0
when (@diff =0 and @Prev_in > @Prev_out) then 1
when (@diff =1) then 0
when (@diff =2) then 1
end,
@input_Flag=
case
when @err2 < 0 then 1
when (@diff =0 and @Prev_in <= @Prev_out) then 1
when (@diff =0 and @Prev_in > @Prev_out) then 0
when (@diff =1) then 1
when (@diff =2) then 0
end
end
if ((@err2=0 and @err1 <0) or @input_ProdID > @prev)
begin
select
@output_Flag=
case when @err1 < 0 then 1
when (@diff =0 and @Prev_in <= @Prev_out) then 0
when (@diff =0 and @Prev_in > @Prev_out) then 0
when (@diff =1) then 0
when (@diff =2) then 1
end,
@input_Flag=
case when @err2 < 0 then 1
when (@diff =0 and @Prev_in <= @Prev_out) then 1
when (@diff =0 and @Prev_in > @Prev_out) then 0
when (@diff =1) then 0
when (@diff =2) then 0
end,
@curr_input_Qty=
case when (@diff =0 and @Prev_in <= @Prev_out) then 0
when (@diff =0 and @Prev_in > @Prev_out) then 0
when (@diff =1) then @Prev_in
when (@diff =2) then 0
end,
@curr_output_Qty=
case when (@diff =0 and @Prev_in <= @Prev_out) then @curr_output_Qty
when (@diff =0 and @Prev_in > @Prev_out) then @curr_output_Qty
when (@diff =1) then @Prev_out
when (@diff =2) then @Prev_out
end
end
if (@curr_input_Qty > 0 or @curr_output_Qty> 0)
begin
insert into #Inventory( ProdID,inputProdID,outputProdID,inputID,outPutID,inputQty,outputQty)
select @prev,
(case when @diff =2 then 0 else @input_ProdID end),
(case when (@diff =1 and @err1=0) then 0 else @output_ProdID end),
(case when @diff =2 then 0 else @input_tranid end),
(case when (@diff =1 and @err1=0) then 0 else @output_tranid end),
@curr_input_Qty,@curr_output_Qty
end
select @curr_input_Qty=
case when (@diff =0 and (@prev_in <=@prev_out)) then @prev_out
when (@diff =0 and (@prev_in >@prev_out)) then @prev_in
when (@diff =1) then @Prev_in
when (@diff =2) then @Prev_in
end,
@curr_output_Qty=
case when (@diff =0 and (@prev_in <=@prev_out)) then @prev_out
when (@diff =0 and (@prev_in >@prev_out)) then @prev_out
when (@diff =1) then @Prev_out
when (@diff =2) then @Prev_out
end
set @output_Flag=@output_Flag
set @input_Flag=@input_Flag
if @input_Flag=1
begin
fetch next from InPut into
@input_ProdID,@input_tranid, @input_Qty
set @err1 = @err1 + @@Fetch_Status
set @prev_in=0
set @curr_input_Qty=@input_Qty
if @err1 < 0
begin
set @curr_input_Qty=0
set @input_ProdID=0
set @input_tranid=0
set @input_Qty=0
end
end
if @output_Flag=1
begin
fetch next from OutPut into
@output_ProdID,@output_tranid, @output_Qty
set @err2 = @err2 + @@Fetch_Status
set @prev_out=0
set @curr_output_Qty=@output_Qty
if @err2 < 0
begin
set @curr_output_Qty=0
set @output_ProdID=0
set @output_tranid=0
set @output_Qty=0
end
end
end
close InPut
close OutPut
deallocate InPut
deallocate OutPut
select * from #Inventory order by ProdID
November 26, 2009 at 12:20 pm
The FIFO challenge,
http//ask.sqlservercentral.com/questions/826/the-fifo-stock-inventory-sql-problem , give me some direction on rewriting this as set-base solution, but I can't seem to put this all together.
Would you mind sharing what you've tried so far and where you get stuck?
Since you already have an excellent resource to get the idea from, I think it would be more valuable to you if you'll try it first instead of going through the result someone else came up with...
November 26, 2009 at 8:09 pm
Including the test data creation, there are 250 lines of undocumented code there... would you care to explain the rules of what it does?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2009 at 11:41 pm
I can't make heads or tails out of all those unions but, I will offer that, switching to a set based approach will definitely yield performance improvements you're looking for. Last week I rewrote a stored procedure left behind by one of my predecessors that used a cursor inside of a cursor. It took 2 hours each night to grind through our AR data and create some tables for reporting, and that is on some really nice hardware too. After I re factored using a set based approach, it now runs in 2 seconds.
November 27, 2009 at 9:50 am
Thank you all for imput...this is what I have so far.
--BUILD DATA TO USED
IF OBJECT_ID('tempdb..#tmptb1','U') IS NOT NULL
drop table #tmptb1
select identity (int, 1,1) ID, prodid,tranid, qty into #tmptb1 from (
select 101 as prodid, 1 as tranid, -30 as qty
union all
select 101,2,20
union all
select 101,3,-5
union all
select 102, 1, 10
union all
select 102,2,20
union all
select 102,3,20
union all
select 103, 1, -10
union all
select 103,2,20
union all
select 103,3,-5
union all
select 104,1,20
union all
select 104,2,-20
union all
select 104,3,-20
union all
select 100,1,30
union all
select 100,2,10
union all
select 107,1,-30
union all
select 107,2,-10
union all
select 105,1,-10
union all
select 106,1,40
)x
--sum up the movement of input and output based on the 'Dave Ballantyne' from FIFO Challenge.
;WITH IN_PUT(TranType, Tranid, Prodid, QtyBefore, Qty)
AS
(SELECT 'I' TranType, t.tranid, t.Prodid,
COALESCE((SELECT SUM(t1.Qty)
FROM #tmptb1 t1
WHERE t1.tranid < t.tranid
AND t1.Prodid = t.Prodid and SIGN(Qty) = 1), 0) AS QtyBefore,
t.Qty
FROM #tmptb1 t where SIGN(Qty) = 1),
INPUT_TOTAL (TranType, Tranid, Prodid, QtyBefore, Qty,TotalQty)
AS
(SELECT TranType, tranid, Prodid, QtyBefore,QTY,
QtyBefore+QTY as TotalQty
FROM IN_PUT),
OUT_PUT(TranType, Tranid, Prodid, QtyBefore, Qty)
AS
(SELECT ' O' as TranType, t.tranid, t.Prodid,
COALESCE((SELECT SUM(abs(t1.Qty))
FROM #tmptb1 t1
WHERE t1.tranid < t.tranid
AND t1.Prodid = t.Prodid and SIGN(Qty) <> 1), 0) AS QtyBefore,
abs(t.Qty)
FROM #tmptb1 t where SIGN(Qty) <> 1),
OUTPUT_TOTAL (TranType, Tranid, Prodid, QtyBefore, Qty,TotalQty)
AS
(SELECT TranType, tranid, Prodid, QtyBefore,QTY,
QtyBefore+QTY as TotalQty
FROM OUT_PUT)
--get the product movement
SELECT COALESCE(I.PRODID, O.PRODID) AS ProdID, ISNULL(I.ProdID,0) AS INPUT_PRODID, ISNULL(O.ProdID,0) AS OUTPUT_PRODID,
ISNULL(I.Tranid,0) AS INPUT_TRANID, ISNULL(O.Tranid,0) AS OUTPUT_TRANID,
ISNULL(I.QTY,0) AS INPUT_QTY,ISNULL(O.QTY,0) AS OUTPUT_QTY
FROM INPUT_TOTAL I
FULL JOIN OUTPUT_TOTAL O ON I.ProdID = O.ProdID
AND
(
(I.QtyBefore >= O.QtyBefore AND I.QtyBefore < O.TotalQty)
OR
(I.TotalQty>= O.QtyBefore AND I.TotalQty < O.TotalQty)
OR
(O.QtyBefore >= I.QtyBefore AND O.TotalQty <= I.TotalQty)
)
ORDER BY COALESCE(I.PRODID, O.PRODID),COALESCE(I.TRANID,O.TRANID)
based on this code,the result almost correct, accept for prodid of 101 and 103...which is wrong.
for prodid-101 and 103 this should:
ProdIDinputProdIDoutputProdIDinputIDoutPutIDinputQtyoutputQty
1011011012120.0020.00
1010101010.0010.00
1010101030.005.00
1031031032110.0010.00
103103103235.005.00
1031030205.000.00
based on FIFO TRANID movement.
eg.
For 101, there is 1 movement of 20 for input, and 2 for output 30, 5. so therefore
20, 20
0, 10 (remain of 10 from 30)
0, 5.
thanks
Thanks...
November 27, 2009 at 12:17 pm
You miss the point I was trying to make... why do you have both an input and an output ProdID and Qty? What are the rules for what goes into these columns and when/why? Why do you need it in this denormalized form?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2009 at 12:45 pm
Hi Jeff,
The reason for this report and data present this way are for account department audit analysis, which have keep the track of the inventory flow in and out. Don't asked me why...that how they like the data to be present.
thanks
November 28, 2009 at 11:29 am
xponcall (11/27/2009)
Hi Jeff,The reason for this report and data present this way are for account department audit analysis, which have keep the track of the inventory flow in and out. Don't asked me why...that how they like the data to be present.
thanks
Sure... I can see that. But what are the rules? For example, why aren't all of the items on an "IN" output at the same time as the "OUT"? What are the rules that say that the "difference" must be output on the next row? That's what I'm looking for is a complete set of rules for this task instead of trying to glean them from some rather lengthy code examples.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply