Last post Feb 06, 2018 07:38 AM by Deepak Panchal
Feb 02, 2018 01:36 AM|PPetee|LINK
i have a small question about SQL,
When I take some item away, it should count the StockQty,
but the result is strange,
the same LotId , different Stockdate(column: StkTakeDetailed), I take 200 from StkTakeDatailed 12583/LotId 52313 ,the StockQty should be 0 , it's no problem,
but the problem happens in other line, why the StkTakeDetailedId 12763/LotId 52313 ,will change the StockQty to -400 ?? this shold be 200 ,because i didn't take this item
How can i solve this problem?
MY SQL is below.....
SELECT DISTINCT [i].[ItemId],[b].[LotId],[StkTakeDetailId],[b].[StkTakeId],[b].[StockOrNot],
[StockTxt]=IIF([b].[StockOrNot] IS NULL,0,1),
cast([b].[AdjQty] * ISNULL([isp].[TransKG]*-1,-1) *1000 as decimal(6,1))
,[StockQty]=cast([b].[AdjQty] * ISNULL([isp].[TransKG]*-1,-1) *1000 as decimal(6,1)) - (SELECT SUM([QTY]*[NUM])FROM [so].[SmpOrderDetails] WHERE [LotId]=[c].[LotId] GROUP BY LotId)
FROM [inv].[StkTake] [a]
inner join [inv].[StkTakeDetails] [b] on [a].[StkTakeId]=[b].[StkTakeId]
inner join [com].[ItemLots] [c] on [c].[LotId]=[b].[LotId]
inner join [com].[Items] [i] ON [c].[ItemId] = [i].[ItemId]
inner join [com].[ItemStockPkg] [isp] ON [b].[StockPkgId] = [isp].[ItemStockPkgId]
WHERE [TakeType]='P' AND [i].[ItemId]=619
ORDER BY [TakeDateExpire] DESC
Feb 04, 2018 03:31 PM|wmec|LINK
Please check detail records in SmpOrderDetails. That should be affecting the results you've got.
Feb 06, 2018 07:38 AM|Deepak Panchal|LINK
I can see that you had only posted the select query.
Based on your description,You are subtracting the value from field.
It is possible that you have some issue in your subtract query.
I suggest you to check it and if possible for you then post it here with some sample data.
We will try to test your query to see the difference.