WITH MAX_LAST(ITEM_CODE,LAST_DELIVERY_DATE,ACTUAL_AMT) AS(
SELECT ITEM_CODE,MAX(LAST_DELIVERY_DATE),MAX(ACTUAL_AMT)
FROM STOCK
GROUP BY ITEM_CODE
)
DELETE FROM ITEM
WHERE (ITEM_CODE NOT IN (SELECT STOCK.ITEM_CODE FROM STOCK INNER JOIN ITEM ON STOCK.ITEM_CODE=ITEM.ITEM_CODE)
OR (ITEM_CODE IN (SELECT MAX_LAST.ITEM_CODE FROM MAX_LAST INNER JOIN ITEM ON MAX_LAST.ITEM_CODE = ITEM.ITEM_CODE WHERE LAST_DELIVERY_DATE <= '2022-12-01' AND ACTUAL_AMT = 0))
)
AND STOCK_MANAGEMENT_TYPE = 1