locked
ABOUT SQL (MSSQL) RRS feed

  • Question

  • 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
    Friday, February 2, 2018 1:36 AM

All replies

  • User364663285 posted

    Hi,
    Please check detail records in SmpOrderDetails. That should be affecting the results you've got.

    Sunday, February 4, 2018 3:31 PM
  • User347430248 posted

    Hi PPetee,

    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.

    Regards

    Deepak

    Tuesday, February 6, 2018 7:38 AM