Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit ===== 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 ===== <code 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 </code> ===== Version Information ===== * Entered : 11/2011 * Version : Control 4.5+