with tmp as (
    select min(order_datetime) order_date, member_code
    from ec_orders
    where order_datetime between '2023-07-01 00:00:00' and '2023-07-31 23:59:59'
    group by member_code
)
select order_date ORDER_DATE, ec_orders.member_code CODE, 
    case optout_type when 0 then '可' when 1 then '不可' else '不明' end OPTOUT
from ec_orders, tmp, member_mst
where ec_orders.member_code = tmp.member_code
    and ec_orders.member_code = member_mst.member_code
    and order_datetime <= '2023-06-30 23:59:59'
group by ec_orders.member_code
having date(max(order_datetime)) < date(order_date, '-1 year')
order by order_date desc, ec_orders.member_code desc