with 
    sub_min as(
    select
        MEMBER_CODE
        ,MIN(ORDER_DATETIME) as ORDER_DATETIME
    from EC_ORDERS
    where ORDER_DATETIME between '2023-07-01 00:00:00' and '2023-07-31 23:59:59'
    group by MEMBER_CODE
    )
    ,sub as (
    select
        EC_ORDERS.ORDER_DATETIME as ORDER_DATETIME
        ,EC_ORDERS.MEMBER_CODE as MEMBER_CODE
    from 
        sub_min
        inner join EC_ORDERS
            on sub_min.MEMBER_CODE = EC_ORDERS.MEMBER_CODE
    where 
        sub_min.MEMBER_CODE = EC_ORDERS.MEMBER_CODE
        and date(EC_ORDERS.ORDER_DATETIME) <= date(sub_min.ORDER_DATETIME,'-1 year') 
    )
    ,sub2 as (
    select 
        EC_ORDERS.MEMBER_CODE as MEMBER_CODE
    from 
        sub_min
        inner join EC_ORDERS
            on sub_min.MEMBER_CODE = EC_ORDERS.MEMBER_CODE
    where
        sub_min.MEMBER_CODE = EC_ORDERS.MEMBER_CODE
        and date(EC_ORDERS.ORDER_DATETIME) 
        between date(sub_min.ORDER_DATETIME,'-1 year') 
        and date(sub_min.ORDER_DATETIME,'-1 day')
    )
--select * from sub2
    
select
    sub.ORDER_DATETIME as ORDER_DATE
    ,sub.MEMBER_CODE as CODE
    ,(case when MEMBER_MST.OPTOUT_TYPE = '0'
     Then '可'
     when MEMBER_MST.OPTOUT_TYPE = '1'
     Then '不可'
     else '不明' end) as OPTOUT
from 
    sub
    left outer join sub2
        on sub.MEMBER_CODE = sub2.MEMBER_CODE
    inner join MEMBER_MST
        on sub.MEMBER_CODE = MEMBER_MST.MEMBER_CODE
where 
    not exists(
        select MEMBER_CODE
        from sub
        where sub.MEMBER_CODE = sub2.MEMBER_CODE
        )
order by
    ORDER_DATE desc
    ,CODE desc