Differences

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

Link to this comparison view

control_sql_-_fill_in_zero_unit_costs_in_inventory_log [2019/01/27 11:28] (current)
Line 1: Line 1:
 +=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 
 +    and IL2.PartID = InventoryLog.PartID 
 + order by ID)
 +      , 0) 
 +where PartID is not NULL
 + and (UnitCost