SELECT
    SUB.ORDER_DATETIME AS ORDER_DATE
    , SUB.MEMBER_CODE AS CODE
    , CASE MM.OPTOUT_TYPE 
        WHEN 0 THEN '可' 
        WHEN 1 THEN '不可' 
        ELSE '不明' 
        END AS OPTOUT 
FROM
    ( 
        -- 会員毎に7月に初めて受注した日付を取得
        SELECT
            MEMBER_CODE
            , LAST_NAME
            , FIRST_NAME
            , MIN(ORDER_DATETIME) AS ORDER_DATETIME
            , MIN(DATE (ORDER_DATETIME)) AS M_DATE 
        FROM
            EC_ORDERS 
        WHERE
            -- DATE関数で日付のみに編集してから7月の受注データを抽出
            DATE (ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31' 
        GROUP BY
            MEMBER_CODE 
    ) AS SUB 
    -- オプトアウト区分を取得するため、会員テーブルを内部結合
    INNER JOIN MEMBER_MST AS MM 
        ON SUB.MEMBER_CODE = MM.MEMBER_CODE 
WHERE
    -- ７月に初めて受注した日付から１年より前に、受注データがあるか存在チェック
    EXISTS ( 
        SELECT
            ORDER_NO 
        FROM
            EC_ORDERS 
        WHERE
            SUB.MEMBER_CODE = MEMBER_CODE 
            AND DATE (ORDER_DATETIME) < DATE (SUB.M_DATE, '-1 year')
    ) 
    -- ７月に初めて受注した前日から１年前までに、受注データが存在しないかチェック
    AND NOT EXISTS ( 
        SELECT
            ORDER_NO 
        FROM
            EC_ORDERS 
        WHERE
            SUB.MEMBER_CODE = MEMBER_CODE 
            AND DATE (ORDER_DATETIME) BETWEEN DATE (SUB.M_DATE, '-1 year') AND DATE (SUB.M_DATE, '-1 day')
    ) 
ORDER BY
    SUB.ORDER_DATETIME DESC
    , SUB.MEMBER_CODE DESC;