v11 below. |
Select ItemCode =S.Code, WHCode = W.Code, ItemDescription1 =S.Description_1, ItemDescription2 =S.Description_2, ItemGroupCode =G.StGroup, ItemGroupDesc = G.Description, ItemWHGroupCode = WS.WHStockGroup, --WS.fAverageCost as WHAvgCost, --WS.WHQtyOnHand as WHQtyOnHand, Qty = SUM(CASE WHEN P.Id = 'JCI' THEN -fQuantityInvoiced --JCI invoices directly invoice out, so doesn't affect the available stock qty WHEN P.Id = 'JCS' THEN 0 --JCS is always 0 anyway on the quantity column, so this shouldn't affect anything. /*JCM -> standard rule applies on qty */ WHEN P.Id NOT IN ('JCI','JCS') AND Debit > 0 THEN Quantity ELSE -Quantity END) ,WS.WHQtyOnHand ,QtyJCWip = SUM(CASE WHEN P.Id = 'JCM' AND Credit > 0 THEN -Quantity ELSE fJCWIPQuantity END) ,QtyMFWIP = SUM(P.fMFPWIPQuantity) ,WS.WHJobQty ,WS.WHMFPQty ,P.AccountLink from PostST P LEFT JOIN StkItem S ON P.AccountLink = S.StockLink LEFT JOIN GrpTbl G ON S.ItemGroup = G.StGroup LEFT JOIN WhseMst W on WhseLink = P.WarehouseID LEFT JOIN WhseStk WS ON WS.WHStockLink = P.AccountLink AND WS.WHWhseID = P.WarehouseID WHERE /*P.TxDate <='2019/12/31' AND */ S.ServiceItem = 0 --AND P.AccountLink = 164 GROUP BY S.Code, W.Code, S.Description_1, S.Description_2, S.ItemGroup, G.StGroup, G.Description, WS.WHStockGroup ,WS.WHQtyOnHand ,WS.WHJobQty ,WS.WHMFPQty ,P.AccountLink HAVING /*Qty diff, QTy on Hand vs Qty in PostST*/ ABS(SUM(CASE WHEN P.Id = 'JCI' THEN -fQuantityInvoiced --JCI invoices directly invoice out, so doesn't affect the available stock qty WHEN P.Id = 'JCS' THEN 0 --JCS is always 0 anyway on the quantity column, so this shouldn't affect anything. /*JCM -> standard rule applies on qty */ WHEN P.Id NOT IN ('JCI','JCS') AND Debit > 0 THEN Quantity ELSE -Quantity END) - WS.WHQtyOnHand) >0.01 |
;WITH cteStocks as ( SELECT /*Groupings*/ p.AccountLink as StockID ,p.TrCodeID ,p.WarehouseID ,p.Id as TxId ,TxDate /*Values:*/ ,QuantityInOut = SUM(CASE WHEN p.Id = 'JCI' THEN 0 WHEN p.Id = 'JCS' THEN 0 WHEN p.Id NOT IN ('JCI','JCS') AND Debit > 0 THEN Quantity ELSE -Quantity END) ,QtyOut = SUM(CASE WHEN p.Id = 'JCI' THEN 0 WHEN p.Id = 'JCS' THEN 0 WHEN p.Id NOT IN ('JCI','JCS') AND Debit > 0 THEN 0 --Quantity ELSE -Quantity END) ,QtyIn = SUM(CASE WHEN p.Id = 'JCI' THEN 0 WHEN p.Id = 'JCS' THEN 0 WHEN p.Id NOT IN ('JCI','JCS') AND Debit > 0 THEN Quantity ELSE -Quantity*0 END) --,* from PostST p INNER JOIN _as_FM_TrCodes TR ON p.TrCodeID = TR.TrCodeId WHERE 1=1 /*Last 12 months:*/ AND p.TxDate >= DATEADD(m,-12,(DATEADD(d,1,EOMONTH(GETDATE(),-1)))) GROUP BY p.AccountLink ,p.TrCodeID ,p.WarehouseID ,p.Id ,p.Id , TxDate -- ORDER BY p.AccountLink, TxDate ASC ) , cteSummary AS (SELECT StockId, WarehouseId, SUM(QuantityInOut) as SumQty FROM cteStocks GROUP BY StockId, WarehouseId) , cteStockQty AS (SELECT StockId, WhseId as WarehouseId, SUM(QtyOnHand) as SumQty FROM _etblStockQtys group by StockID, WhseID) /*Checks with current qty.*/ SELECT * FROM cteSummary s FULL OUTER JOIN cteStockQty q ON s.StockID = q.StockID and s.WarehouseID = q.WarehouseId WHERE COALESCE(s.SumQty,0) <> COALESCE(q.SumQty,0) ORDER BY COALESCE(s.StockID,q.StockId) |