Differences
This shows you the differences between two versions of the page.
— |
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), | ||
+ | |||
+ | |||
+ | |||
+ | =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, | ||
+ | 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 | ||