Differences
This shows you the differences between two versions of the page.
control_sql_-_find_large_variances_in_inventorylog_unit_costs [2019/01/27 11:28] |
control_sql_-_find_large_variances_in_inventorylog_unit_costs [2019/01/27 11:28] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ===== 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, | ||
+ | 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 | ||
+ | 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 | ||
+ | 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 | ||
+ | and | ||
+ | not (TempA.UnitCost = 0 and TempA.NextUnitCost = 0 and TempA.PriorUnitCost = 0) | ||
+ | and | ||
+ | ( TempA.UnitCost | ||
+ | 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+ | ||
+ | |||
+ | |||
+ | |||