User544906106 posted
DEAR all,
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),
[a].[StkTakeNo],[a].[TakeDate],[ExpireDate],[isp].[TransKg],
[TakeDateExpire]=(DateAdd(DAY,180,TakeDate)),
[i].[ItemName],[c].[LotNo],[b].[AdjQty],[AdjQtyG]=
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
| ItemId |
LotId |
StkTakeDetailId |
StkTakeId |
StockOrNot |
StockTxt |
StkTakeNo |
TakeDate |
ExpireDate |
TransKg |
TakeDateExpire |
ItemName |
LotNo |
AdjQty |
AdjQtyG |
StockQty |
| 619 |
52313 |
12763 |
10011 |
NULL |
0 |
10702001 |
2018/2/1 |
2019/1/16 |
NULL |
2018/7/31 |
#1【AKR】 |
170117R01 |
0.2 |
-200 |
-400 |
| 619 |
52313 |
12583 |
10000 |
NULL |
0 |
10611022 |
2017/11/13 |
2019/1/16 |
NULL |
2018/5/12 |
#1【AKR】 |
170117R01 |
-0.2 |
200 |
0 |
