DELETE 
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
                MAX(IFNULL(LAST_DELIVERY_DATE, '2999-12-31')) <= DATE ('2023-06-01', '-6 months') 
                AND SUM(ACTUAL_AMT) = 0
        )
    );