-- SELEcT *
-- FROM
--     ITEM 
-- WHERE
--     -- 在庫管理する商品が対象
--     STOCK_MANAGEMENT_TYPE = 1 
--     AND ( 
--         -- 商品コードが在庫テーブルに存在しないこと確認
--         NOT EXISTS ( 
--             SELECT
--                 ITEM_CODE 
--             FROM
--                 STOCK 
--             WHERE
--                 STOCK.ITEM_CODE = ITEM.ITEM_CODE
--         ) 
--         -- -- 商品コードが在庫テーブルに存在しているが、削除条件を満たしていることを確認
--         -- OR EXISTS ( 
--         --     SELECT
--         --         ITEM_CODE 
--         --     FROM
--         --         STOCK 
--         --     WHERE
--         --         STOCK.ITEM_CODE = ITEM.ITEM_CODE 
--         --     -- 商品コードで集計した結果を判定するためグルーピングをおこなう
--         --     GROUP BY
--         --         ITEM_CODE 
--         --     HAVING
--         --       -- 最終出荷日の最大値が６カ月以上前かチェック
--         --       -- 入荷実績があるがまだ出荷実績がない場合は、最終出荷日はNULLなので削除の対象外となる
--         --       --  （最終出荷日がNULLの場合は対象外とするため、ダミーで'2999-12-31'をセット)
--         --         MAX(IFNULL(LAST_DELIVERY_DATE, '2999-12-31')) <= DATE ('2023-06-01', '-6 months') 
--         --         -- 実在庫数の合計がゼロかチェック
--         --         AND SUM(ACTUAL_AMT) = 0
--         -- )
--     );

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'
)
delete from item where item_code in (
select ITEM_CODE from ne union all select ITEM_CODE from old2)