Standard Cost History

Or ideally, I see the Std cost at the start of the year, the end of the year, and the date of the change.

Hi ​@claudematherne24 ,

I think Yes, we can assume the date of the last standard cost from the Pending Cost Date field, because after processing the Update Standard Cost, this field is updated with the new pending cost's date. we don't have specific Last Standard Cost

Hope this helps!!

I ran the data and it returned the last update from the Audit.

select InventoryItemCurySettings.laststdcost, 
       InventoryItemCurySettings.stdcost as currentcost, 
	   InventoryItemCurySettings.StdCostDate as lastupdate, 
	   InventoryItemCurySettings.pendingstdcost, 
	   InventoryItemCurySettings.PendingStdCostDate
  from InventoryItem
  join InventoryItemCurySettings 
    on InventoryItemCurySettings.InventoryID = InventoryItem.InventoryID
   and InventoryItemCurySettings.CompanyID = InventoryItem.CompanyID
/* where InventoryItem.inventorycd = 'xxxxxxx'  -- TEST SKU
   and InventoryItem.companyid = 2  */

laststdcost    currentcost    lastupdate    pendingstdcost    PendingStdCostDate
2.248700      2.392930    2024-01-27 00:00    0.000000    NULL

If you have InventoryItem auditing enabled you could pull a Generic Inquiry off the Audit.

select InventoryItemCurySettings.laststdcost, 
       max(AuditHistory.ChangeDate) as previousdate,
       InventoryItemCurySettings.stdcost as currentcost, 
	   InventoryItemCurySettings.StdCostDate as lastupdate, 
	   InventoryItemCurySettings.pendingstdcost, 
	   InventoryItemCurySettings.PendingStdCostDate
  from InventoryItem
  join InventoryItemCurySettings 
    on InventoryItemCurySettings.InventoryID = InventoryItem.InventoryID
   and InventoryItemCurySettings.CompanyID = InventoryItem.CompanyID
  left
  join AuditHistory
    on AuditHistory.ScreenID = 'IN202500'  
   and AuditHistory.TableName = 'InventoryItem'
   and AuditHistory.ModifiedFields like 'PendingStdCost%'
   and AuditHistory.CombinedKey = InventoryItem.InventoryCD
   and AuditHistory.CompanyID = InventoryItem.CompanyID
/* where InventoryItem.inventorycd = 'xxxxxx'  -- TEST SKU
   and InventoryItem.companyid = 2   */
 group by InventoryItemCurySettings.laststdcost, 
       InventoryItemCurySettings.stdcost, 
	   InventoryItemCurySettings.StdCostDate, 
	   InventoryItemCurySettings.pendingstdcost, 
	   InventoryItemCurySettings.PendingStdCostDate

laststdcost    previousdate          currentcost    lastupdate    pendingstdcost    PendingStdCostDate
2.248700    2024-01-27 22:47    2.392930    2024-01-27 00:00    0.000000    NULL

You could do some logic where you track years on change date, etc.

ChangeID    ScreenID    UserID                                                                    ChangeDate
40338494    IN202500    56DED2C7-EB6E-4D14-8D01-241D0383BE0D    2024-01-27 22:47:26.990
40279636    IN202500    56DED2C7-EB6E-4D14-8D01-241D0383BE0D    2024-01-27 03:02:03.330
38706294    IN202500    56DED2C7-EB6E-4D14-8D01-241D0383BE0D    2023-12-11 20:08:58.320
38657133    IN202500    56DED2C7-EB6E-4D14-8D01-241D0383BE0D    2023-12-11 19:38:29.993
38142020    IN202500    56DED2C7-EB6E-4D14-8D01-241D0383BE0D    2023-11-29 19:46:56.820
22242860    IN202500    56DED2C7-EB6E-4D14-8D01-241D0383BE0D    2022-10-22 06:26:41.273