with tmp as (
--最大出荷日が6か月以上前で在庫ゼロ
select item_code as CODE  
from 
(select item_code,max(last_delivery_date) as maxdate,sum(actual_amt) as totalamt
from stock 
where last_delivery_date is not null 
group by item_code 
having maxdate<=date('2023-06-01','-6 months') and totalamt=0)
) 
,tmp1 as (
--stockにないitem
select item.item_code as CODE 
from item 
where item.item_code not in (select item_code from stock) 
)
,delitemcode as (
--削除itemcode
select code from tmp 
union select code from tmp1
)
,delitem as (
select item_code as code from item 
inner join delitemcode 
on item.item_code=delitemcode.code 
where item.stock_management_type=1
)
delete from item where item_code in (select code from delitem);