WITH MAX_LD AS (
    SELECT
        ITEM_CODE
        , MAX(LAST_DELIVERY_DATE) AS MAX_LASTDELIVERY
    FROM 
        STOCK
    GROUP BY
        ITEM_CODE
),
DEST_ITEM AS (
    SELECT
        ITEM.ITEM_CODE AS ITEM_CODE
    FROM ITEM
    	LEFT JOIN STOCK
    		ON ITEM.ITEM_CODE = STOCK.ITEM_CODE
    	INNER JOIN MAX_LD
    	    ON STOCK.ITEM_CODE = MAX_LD.ITEM_CODE
    WHERE
        1=1
        AND STOCK_MANAGEMENT_TYPE = 1
        AND (
            STOCK.ITEM_CODE IS NULL
            OR (
                MAX_LD.MAX_LASTDELIVERY <= datetime(date('2023-06-01'), '-6 months')
        		AND STOCK.ACTUAL_AMT = 0
            )
        )
)
DELETE
FROM
    ITEM
WHERE
    1=1
    AND ITEM_CODE IN (SELECT ITEM_CODE FROM DEST_ITEM)
;