WITH pricing AS (
  SELECT 
    _VPW.MFG_SKU,
    CASE
      WHEN MAX(orders.OrderDate) IS NULL THEN 'DEAD'
      WHEN DATEDIFF(CURDATE(), MAX(orders.OrderDate)) > 360 THEN 'DEAD'
      WHEN DATEDIFF(CURDATE(), MAX(orders.OrderDate)) > 180 THEN 'SLOW'
      WHEN SUM(orderdetails.Quantity) = 0 THEN 'SLOW'
      ELSE ''
    END AS WouldBePricingType
  FROM products._VPW
  JOIN products.pins ON _VPW.MFG_SKU = pins.PIN
  LEFT JOIN custopayted.orderdetails 
    ON pins.ProductCode = orderdetails.ProductCode AND vendor_price != 0
  LEFT JOIN custopayted.orders 
    ON orderdetails.OrderID = orders.OrderID
  WHERE _VPW.Stock > 0
  GROUP BY _VPW.MFG_SKU
)
SELECT 
  pins.ProductCode,
  pins.Manufacturer,
  pins.SalePriceAfterRatio,
  pricing.WouldBePricingType,
  ROUND(
    CASE
      WHEN pricing.WouldBePricingType IN ('SLOW', 'DEAD')
        THEN CASE WHEN pins.TypeBasedPrice = 0 THEN pins.SalePrice * 0.995
                  ELSE pins.TypeBasedPrice * 0.995 END
      ELSE pins.TypeBasedPrice
    END, 2) AS WouldBeTypeBasedPrice,
  pins.PricingType,
  pins.TypeBasedPrice,
  pins.SalePrice,
  _VPW.MFG_SKU AS PIN,
  pins.Cost,
  _VPW.Stock,
  ROUND(
    (pins.Cost * _VPW.Stock) / 
    (CASE WHEN pricing.WouldBePricingType = 'SLOW' THEN 2 ELSE 1 END), 2
  ) AS TotalCost
FROM products.pins
JOIN pricing 
  ON pins.PIN = pricing.MFG_SKU
JOIN products._VPW 
  ON _VPW.MFG_SKU = pins.PIN
WHERE pins.PricingType IN ('MANUAL', 'MAP')
  AND pricing.WouldBePricingType IN ('SLOW', 'DEAD');