Differences

This shows you the differences between two versions of the page.

Link to this comparison view

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,​ 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+
 +
 +
 +