--drop temp table 
drop table #temp

--product inventory table
SELECT 
      [UnitCost]
      ,[UnitsBalance]
	  , ProductKey into #temp -- insert into a temp table to avoid multiple nested SELECT statements
  FROM [AdventureWorksDW].[dbo].[FactProductInventory] a
  right join -- right join because we want only those matching from table a to table b
  ( 
  SELECT 
	  max(cast(movementdate as date)) as Max_Date --most recent date
  FROM [AdventureWorksDW].[dbo].[FactProductInventory] 
  ) b on a.MovementDate = b.Max_Date -- this ensures that we are pulling the max date of the whole table (assuming each product is updated daily)

  --join with product table
  SELECT  a.[ProductKey]
      ,[ProductAlternateKey]
      ,[EnglishProductName]
      ,[StandardCost]
      ,[SafetyStockLevel]
      ,[ReorderPoint]
      ,[ListPrice]
      ,[DaysToManufacture]
      ,[ProductLine]
	  , UnitCost
	  , UnitsBalance
	  , case when UnitsBalance < ReorderPoint then 1 else 0 end as ReorderPoint_Met -- determines if the minimum reorder point has been met
	  , cast(safetystocklevel as float) - cast(unitsbalance as float) as UnitsNeeded -- units needed to order to reach safety stock level
	  , (cast(safetystocklevel as float) - cast(unitsbalance as float)) * cast(unitcost as float) as OrderCost -- the cost of the order if we were to order the unitsneeded
  FROM [AdventureWorksDW].[dbo].[DimProduct] a
  left join #temp b on a.ProductKey = b.ProductKey
  where Status = 'current' -- only want to show the current products
  --and case when UnitsBalance < ReorderPoint then 1 else 0 end = 1 -- this is optional to include depending on if we want to see only the products that need to be ordered