WITH STOCK_INFO AS (
    SELECT ITEM_CODE
          ,MAX(LAST_DELIVERY_DATE) AS LAST_DELIVERY_DATE
          ,SUM(ACTUAL_AMT) AS ACTUAL_AMT
         FROM STOCK
        GROUP BY ITEM_CODE
        ORDER BY ITEM_CODE
)

,agg AS (

    SELECT *
        FROM STOCK_INFO
    WHERE LAST_DELIVERY_DATE <= DATE('2023-06-01','-6 months') --出荷日が6か月以前
    AND ACTUAL_AMT = 0

)
SELECT a.*
    FROM ITEM AS a
    LEFT JOIN agg AS b
    ON a.ITEM_CODE = b.ITEM_CODE
WHERE ACTUAL_AMT IS NULL 
AND STOCK_MANAGEMENT_TYPE = 1
ORDER BY ITEM_CODE