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.

None. This is a selection query and no data is modified in the running of it.

-- 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
  • Entered : 11/2011
  • Version : Control 4.5+
You could leave a comment if you were logged in.