INSERT INTO Production.ZeroInventory
(
    DeletedProductID
  , RemovedOnDate
)
SELECT  ProductID , GETDATE()
FROM    (
            MERGE Production.ProductInventory AS pi
            USING (
                      SELECT    ProductID
                              , SUM(OrderQty)
                      FROM      Sales.SalesOrderDetail AS sod
                      JOIN      Sales.SalesOrderHeader AS soh
                      ON        sod.SalesOrderID  = soh.SalesOrderID
                                AND soh.OrderDate = '20070401'
                      GROUP BY  ProductID
                  ) AS src (ProductID, OrderQty)
            ON (pi.ProductID = src.ProductID)
            WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0
            THEN DELETE
            WHEN MATCHED
            THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
            OUTPUT $action , deleted.ProductID
        ) AS Changes(Action, ProductID)
WHERE   Action = 'DELETE';