WITH A AS
(
    SELECT
        EO.MEMBER_CODE,
        MIN(EO.ORDER_DATETIME) AS MIN_ORDER_DATETIME
    FROM
        EC_ORDERS AS EO
    WHERE
        EO.ORDER_DATETIME BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59'
    GROUP BY
        EO.MEMBER_CODE
),
B AS
(
    SELECT
        EO.MEMBER_CODE,
        MAX(EO.ORDER_DATETIME) AS LAST_ORDER_DATETIME
    FROM
        EC_ORDERS AS EO
    WHERE
        EO.ORDER_DATETIME < '2023-07-01 00:00:00'
    GROUP BY
        EO.MEMBER_CODE
)
SELECT
    A.MIN_ORDER_DATETIME AS ORDER_DATE,
    A.MEMBER_CODE AS CODE,
    CASE MM.OPTOUT_TYPE WHEN 0 THEN '可' WHEN 1 THEN '不可' ELSE '不明' END AS OPTOUT
FROM
    A
    LEFT OUTER JOIN
        B
        ON
            B.MEMBER_CODE = A.MEMBER_CODE
    LEFT OUTER JOIN
        MEMBER_MST AS MM
        ON
            MM.MEMBER_CODE = A.MEMBER_CODE
WHERE
    DATE(A.MIN_ORDER_DATETIME) > DATE(DATE(B.LAST_ORDER_DATETIME), '+1 year')

ORDER BY
    A.MIN_ORDER_DATETIME DESC,
    A.MEMBER_CODE DESC