-- 在庫テーブル(STOCK)に商品コード(ITEM_CODE)が存在しない
delete 
from
    ITEM 
where
    ITEM_CODE in ( 
        select
            i.ITEM_CODE 
        from
            ITEM i 
            left outer join STOCK s 
                on i.ITEM_CODE = s.ITEM_CODE 
        where
            i.STOCK_MANAGEMENT_TYPE = 1 
            and s.ITEM_CODE is null
    ); 

--商品毎の最終出荷日(LAST_DELIVERY_DATE)の最大値が6カ月以上前
--かつ実在庫数(ACTUAL_AMT)がゼロ
delete 
from
    ITEM 
where
    ITEM_CODE in ( 
        select
            ITEM_CODE 
        from
            ( 
                select
                    i.ITEM_CODE
                    , max(s.LAST_DELIVERY_DATE) as MAX_LAST_DELIVERY_DATE 
                from
                    ITEM i 
                    left outer join STOCK s 
                        on i.ITEM_CODE = s.ITEM_CODE 
                where
                    i.STOCK_MANAGEMENT_TYPE = 1 
                    and s.ITEM_CODE is not null 
                    and s.ACTUAL_AMT = 0 
                group by
                    i.ITEM_CODE
            ) 
        where
            MAX_LAST_DELIVERY_DATE <= '2022-12-01' 
            and ITEM_CODE not in ( 
                --入荷されたが、まだ出荷実績のないデータの最終出荷日はNULL
                select
                    i.ITEM_CODE 
                from
                    ITEM i 
                    left outer join STOCK s 
                        on i.ITEM_CODE = s.ITEM_CODE 
                where
                    i.STOCK_MANAGEMENT_TYPE = 1 
                    and s.ITEM_CODE is not null 
                    and s.LAST_DELIVERY_DATE is null
            )
    );