=Explanation of SQL=

In some cases (reasons not yet identified), the UnitCost is not filled in on the InventoryLog table. If you are trying to compute historical inventory valuations, this can cause problems as the computed price will be zero. This SQL fills in the unit cost with the last unit cost that is not zero for that part.

=Risk of Data Corruption if Run Improperly=

Medium. Data is modified in this query, but only data that is zero and is already wrong. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.

=SQL to Show Unit Cost Changes=

<code sql> select Journal.CompletedDateTime,

  IL.ID as ILID, 
  (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 
	 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 
	 order by ID)
    , 0) as NextUnitCost,
  IL.PartID

from InventoryLog IL join Journal on IL.ID = Journal.ID where

IL.PartID is not NULL

and IL.UnitCost 0

	   and IL2.PartID = InventoryLog.PartID 
	 order by ID)
    , 0) 

where PartID is not NULL and (UnitCost

You could leave a comment if you were logged in.