with T1 as (
    select MEMBER_CODE,min(ORDER_DATETIME) DT from EC_ORDERS where ORDER_DATETIME between '2023-07-00' and '2023-08-00' group by MEMBER_CODE
),
    T2 as (
        select distinct  EC.MEMBER_CODE  as memc from EC_ORDERS EC inner join T1 on EC.MEMBER_CODE=T1.MEMBER_CODE
                 where DATE(ORDER_DATETIME)>=DATE(T1.DT, '-1 year') and ORDER_DATETIME<T1.DT
    )
    select
        max(T1.DT) as ORDER_DATE,
        max(EC.MEMBER_CODE) as CODE,
        max(case when MEMBER_MST.OPTOUT_TYPE=0 then '可' when MEMBER_MST.OPTOUT_TYPE=1 then '不可' else '不明' end) as OPTOUT

        from EC_ORDERS EC inner join  T1 on EC.MEMBER_CODE=T1.MEMBER_CODE and ORDER_DATETIME<T1.DT
        inner join MEMBER_MST on EC.MEMBER_CODE=MEMBER_MST.MEMBER_CODE
    where DATE(ORDER_DATETIME)<DATE(T1.DT, '-1 year') and EC.MEMBER_CODE not in (select memc from T2)
    group by EC.MEMBER_CODE
order by 1 desc,2 desc
;