select  WK.MINDATE ORDER_DATE
        ,MEM.MEMBER_CODE CODE
        ,CASE MEM.OPTOUT_TYPE
            WHEN '0' Then '可'
            WHEN '1' Then '不可'
        Else  '不明'
        End OPTOUT
  from  MEMBER_MST MEM,(
    select  ec7.member_code
            ,MIN(SUBSTR(ec7.ORDER_DATETIME,1,10)) MINDATE
      from  EC_ORDERS ec7 
     where  SUBSTR(ec7.ORDER_DATETIME,1,10) >= '2023-07-01'
      and   SUBSTR(ec7.ORDER_DATETIME,1,10) < '2023-08-01'
       and  not exists (
       select   'old'
         from   EC_ORDERS eco
        where   ec7.member_code = eco.member_code
        and     SUBSTR(eco.ORDER_DATETIME,1,10) < '2023-07-01'
        and     substr(eco.ORDER_DATETIME,1,10) >= datetime(substr(ec7.ORDER_DATETIME,1,10), '-1 years')
       )
       and  exists (
          select   'first'
         from   EC_ORDERS ecf
        where   ec7.member_code = ecf.member_code
        and     SUBSTR(ecf.ORDER_DATETIME,1,10) < '2023-07-01'
        and     substr(ecf.ORDER_DATETIME,1,10) < datetime(substr(ec7.ORDER_DATETIME,1,10), '-1 years')    
       )
       group by ec7.member_code
       ) wk
 where  MEM.MEMBER_CODE = wk.MEMBER_CODE
 order by WK.MINDATE desc,MEM.MEMBER_CODE desc