with o7 as (
select o7.MEMBER_CODE
, min(o7.ORDER_DATETIME) ORDER_DATETIME
, date(datetime(min(o7.ORDER_DATETIME),'-12 months')) ORDER_DATETIME7M
from EC_ORDERS o7
where o7.ORDER_DATETIME >= '2023-07-01'
and o7.ORDER_DATETIME < '2023-08-01'
group by o7.MEMBER_CODE
)
, t as (
select o7.MEMBER_CODE
, max(o7.ORDER_DATETIME) ORDER_DATETIME
from o7
     inner join EC_ORDERS ec
       on ec.MEMBER_CODE = o7.MEMBER_CODE
       and ec.ORDER_DATETIME < o7.ORDER_DATETIME7M
group by o7.MEMBER_CODE
)
select
 t.ORDER_DATETIME ORDER_DATE
, t.MEMBER_CODE CODE
, case when m.OPTOUT_TYPE = 0 then '可'
       when m.OPTOUT_TYPE = 1 then '不可'
       else '不明'
  end OPTOUT
from t
     inner join MEMBER_MST m
       on m.MEMBER_CODE = t.MEMBER_CODE
order by
 t.ORDER_DATETIME desc
, m.MEMBER_CODE desc
;