with afterJulyOrder as(
select
	 MEMBER_CODE 
	,min(substr(ORDER_DATETIME,1,10)) as ORDER_DATE
	,min(ORDER_DATETIME) as ORDER_DATETIME
from
	EC_ORDERS
where
	substr(ORDER_DATETIME,1,10) between '2023-07-01' and '2023-07-31'
group by MEMBER_CODE
),
beforeJulyOrder as(
select
	 MEMBER_CODE 
	,max(substr(ORDER_DATETIME,1,10)) as ORDER_DATE
from
	EC_ORDERS
where
	substr(ORDER_DATETIME,1,10) < '2023-07-01'
group by MEMBER_CODE
)
select
	a.ORDER_DATETIME, a.MEMBER_CODE as CODE,
	case 
		when m.OPTOUT_TYPE = '0' then '可'
		when m.OPTOUT_TYPE = '1' then '不可'
		else '不明'
	end as OPTOUT
from afterJulyOrder a
	inner join beforeJulyOrder b
	on  a.MEMBER_CODE = b.MEMBER_CODE
	and datetime(a.ORDER_DATE, '-1 years') > b.ORDER_DATE
	inner join MEMBER_MST m
	on  a.MEMBER_CODE = m.MEMBER_CODE
order by
	a.ORDER_DATETIME desc, a.MEMBER_CODE desc