with ld as (
select
    ITEM_CODE
    ,date(max(coalesce(LAST_DELIVERY_DATE,"3000-01-01"))) as LAST_DELIVERY_DATE
    ,sum(ACTUAL_AMT) as ACTUAL_AMT
from
    STOCK
group by
    ITEM_CODE
)
,base as (
select
    i.ITEM_CODE
from
    ITEM as i
    left join
    ld
    on i.ITEM_CODE = ld.ITEM_CODE
where
    i.STOCK_MANAGEMENT_TYPE=1
    and(
        ld.ITEM_CODE is null
        or (LAST_DELIVERY_DATE<=date("2023-06-01","-6 month") and ACTUAL_AMT=0)
    )
)
delete from
    ITEM as i
where
    ITEM_CODE in (SELECT ITEM_CODE from base)