with ne as (
select ITEM.ITEM_CODE
from ITEM left join STOCK on ITEM.ITEM_CODE = STOCK.ITEM_CODE
where STOCK.ITEM_CODE is null and ITEM.STOCK_MANAGEMENT_TYPE = 1
)
, old as (
select STOCK.ITEM_CODE
, MAX(ifnull(LAST_DELIVERY_DATE, '2999-12-31')) as ML
, SUM(ACTUAL_AMT) as SA
from STOCK join ITEM on STOCK.ITEM_CODE = ITEM.ITEM_CODE
where ITEM.STOCK_MANAGEMENT_TYPE = 1
group by STOCK.ITEM_CODE
)
, old2 as (
select ITEM_CODE from old
where SA = 0 and ML < '2022-12-01'
)
-- select ITEM_CODE from ne union all select ITEM_CODE from old2
delete from item where ITEM_CODE IN (select ITEM_CODE from ne union all select ITEM_CODE from old2)
-- ;