SELECT
C.ORDER_7FIRST AS ORDER_DATE,
C.MEMBER_CODE AS CODE,
(CASE WHEN MEMBER_MST.OPTOUT_TYPE = 0 THEN '可'
WHEN MEMBER_MST.OPTOUT_TYPE = 1 THEN '不可'
ELSE '不明' END)
AS OPTOUT
(SELECT
*
FROM
(SELECT
    MEMBER_CODE,
    MIN(ORDER_DATETIME) AS ORDER_OLD    
FROM
    EC_ORDERS
WHERE
    date(ORDER_DATETIME) < '2023-07-01'
GROUP BY
    MEMBER_CODE) A
INNER JOIN
(SELECT
    MEMBER_CODE,
    MIN(ORDER_DATETIME) AS ORDER_7FIRST
FROM
    EC_ORDERS
WHERE
    date(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
GROUP BY
    MEMBER_CODE
) B
ON
    A.MEMBER_CODE = B.MEMBER_CODE
WHERE
   DATEADD(date(ORDER_OLD), 1,'%Y') > date(ORDER_7FIRST)) C
INNER JOIN
    MEMBER_MST
ON
    C.MEMBER_CODE = MEMBER_MST.MEMBER_CODE