WITH T1 AS(
    SELECT
        MIN(ORDER_DATETIME) AS ORDER_DATE,
        MEMBER_CODE AS CODE
    FROM
        EC_ORDERS
    WHERE
        DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
    GROUP BY
        MEMBER_CODE
),T2 AS(
    SELECT
        MAX(DATE(ORDER_DATETIME)) AS LAST_DATE,
        EC_ORDERS.MEMBER_CODE AS CODE
    FROM
        EC_ORDERS
        INNER JOIN
            T1 ON EC_ORDERS.MEMBER_CODE = T1.CODE 
    WHERE
        DATE(ORDER_DATETIME) < 
        CASE
            WHEN ORDER_DATE IS NULL THEN '2023-07-01'
            ELSE DATE(ORDER_DATE, '-1 year')
        END
    GROUP BY
        EC_ORDERS.MEMBER_CODE
)
SELECT
    ORDER_DATE,
    T1.CODE,
    CASE OPTOUT_TYPE
        WHEN 0 THEN '可'
        WHEN 1 THEN '不可'
        ELSE '不明'
    END AS OPTOUT
FROM
    T1
    INNER JOIN
        MEMBER_MST ON T1.CODE = MEMBER_MST.MEMBER_CODE,
        T2 ON T1.CODE = T2.CODE
WHERE
    LAST_DATE IS NOT NULL
ORDER BY
    ORDER_DATE DESC,
    T1.CODE DESC