WITH CTE AS (
    SELECT
        a.ITEM_CODE
        , LAST_VALUE(LAST_DELIVERY_DATE) OVER (PARTITION BY a.ITEM_CODE) AS LATEST_DELIVERY_DATE
        , ACTUAL_AMT
    FROM STOCK AS a
    INNER JOIN ITEM AS b ON 
        a.ITEM_CODE = b.ITEM_CODE
        AND b.STOCK_MANAGEMENT_TYPE = 1
    WHERE
        1=1
        AND NOT EXISTS (
            SELECT
                *
            FROM 
                STOCK AS c
            WHERE
                ITEM_CODE = a.ITEM_CODE
                AND LAST_DELIVERY_DATE IS NULL
        )
), CTE_2 AS (
    SELECT
        ITEM_CODE
        , MAX(LATEST_DELIVERY_DATE) AS LATEST_DELIVERY_DATE
        , SUM(ACTUAL_AMT) AS TOTAL_AMT
    FROM
        CTE
    GROUP BY
        ITEM_CODE
)
DELETE
FROM 
    ITEM AS a
WHERE EXISTS (
    SELECT
        ITEM_CODE
    FROM
        CTE_2 b
    WHERE
        1=1
        AND a.ITEM_CODE = b.ITEM_CODE
        AND LATEST_DELIVERY_DATE < date('2023-06-01', 'localtime', '-6 month')
        AND TOTAL_AMT = 0
)
;