WITH
CURRENT_ORDERS AS (
    SELECT
        MIN(ORDER_DATETIME) AS ORDER_DATE,
        MEMBER_CODE
    FROM
        EC_ORDERS
    WHERE
        ORDER_DATETIME BETWEEN '2023-07-01 00:00:00' AND '2023-07-31 23:59:59'
    GROUP BY
        MEMBER_CODE
),
LAST_ORDERS AS (
    SELECT
        MAX(ORDER_DATETIME) AS LAST_ORDER_DATE,
        MEMBER_CODE
    FROM
        EC_ORDERS
    WHERE
        ORDER_DATETIME < '2023-07-01'
    GROUP BY
        MEMBER_CODE
)
SELECT
    CURRENT_ORDERS.ORDER_DATE,
    CURRENT_ORDERS.MEMBER_CODE AS CODE,
    CASE MEMBER_MST.OPTOUT_TYPE
    WHEN 0 THEN '可'
    WHEN 1 THEN '不可'
    ELSE '不明' END AS OPTOUT
FROM
    CURRENT_ORDERS
INNER JOIN
    LAST_ORDERS ON
    LAST_ORDERS.MEMBER_CODE = CURRENT_ORDERS.MEMBER_CODE AND
    LAST_ORDERS.LAST_ORDER_DATE < DATE(CURRENT_ORDERS.ORDER_DATE, '-1 years')
INNER JOIN
    MEMBER_MST ON
    MEMBER_MST.MEMBER_CODE = CURRENT_ORDERS.MEMBER_CODE
ORDER BY
    CURRENT_ORDERS.ORDER_DATE DESC,
    CURRENT_ORDERS.MEMBER_CODE DESC