Explanation of SQL
This Query identified places in the InventoryLog where the UnitCost changes abruptly (more than 10%) between successive entries for a given part. This may indicate problems with the UnitCost.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
-- Query to identify large variances in the UnitCost between concurrent transactions for the same part -- SELECT TempA.* , Part.TrackCosts , Part.TrackInventory , Part.AccrueCosts FROM ( SELECT Journal.CompletedDateTime, IL.ID AS InventoryLogID, (SELECT ItemName FROM Part WHERE ID = IL.PartID) AS PartName, IL.QuantityBilled, IL.UnitCost, COALESCE( (SELECT top 1 UnitCost FROM InventoryLog IL2 WHERE IL2.ID < IL.ID AND IL2.UnitCost > 0 AND IL2.PartID = IL.PartID AND IL2.QuantityBilled 0 ORDER BY ID DESC) , 0) AS PriorUnitCost, COALESCE( (SELECT top 1 UnitCost FROM InventoryLog IL2 WHERE IL2.ID > IL.ID AND IL2.UnitCost > 0 AND IL2.PartID = IL.PartID AND IL2.QuantityBilled 0 ORDER BY ID) , 0) AS NextUnitCost, IL.PartID FROM InventoryLog IL JOIN Journal ON IL.ID = Journal.ID WHERE IL.PartID IS NOT NULL ) TempA JOIN Part ON Part.ID = TempA.PartID WHERE (QuantityBilled 0) AND NOT (TempA.UnitCost = 0 AND TempA.NextUnitCost = 0 AND TempA.PriorUnitCost = 0) AND ( TempA.UnitCost 0.01 AND TempA.UnitCost / TempA.NextUnitCost NOT BETWEEN 0.8 AND 1.25) OR (abs(TempA.PriorUnitCost) > 0.01 AND TempA.UnitCost / TempA.PriorUnitCost NOT BETWEEN 0.8 AND 1.25) ) ORDER BY PartName, InventoryLogID
Version Information
- Entered : 11/2011
- Version : Control 4.5+
You could leave a comment if you were logged in.