delete from item
where item_code in (
select
  a.item_code as item_code
from item as a
left outer join stock as b
  on a.item_code = b.item_code
where b.item_code is null
  and STOCK_MANAGEMENT_TYPE = 1
union
select
  a.item_code
from item as a
left outer join stock as b
  on a.item_code = b.item_code
where STOCK_MANAGEMENT_TYPE = 1
group by 1
having
  --max(cast(replace(b.LAST_DELIVERY_DATE, '-', '') as date)) < cast('20230101' as date)
  MAX(IFNULL(LAST_DELIVERY_DATE, '2999-12-31')) <= DATE ('2023-06-01', '-6 months') 
  and sum(b.ACTUAL_AMT) = 0
)
;