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