SELECT wk3 ORDER_DATE
      ,wk4 CODE
      ,CASE
         WHEN T4.OPTOUT_TYPE = '0' THEN '可'
         WHEN T4.OPTOUT_TYPE = '1' THEN '不可'
         ELSE '不明'
       END OPTOUT
FROM
(
SELECT ORDER_DATETIME wk3
      ,MEMBER_CODE wk4
      ,wk1
      ,JULIANDAY(T2.ORDER_DATETIME) - JULIANDAY(wk1,'+1 day') wk5
FROM EC_ORDERS T2
LEFT OUTER JOIN
(
SELECT MAX(ORDER_DATETIME) wk1
      ,MEMBER_CODE wk2
  FROM EC_ORDERS
 WHERE ORDER_DATETIME < '2023-07-01 00:00:00'
 GROUP BY 2
) T1
ON wk4 = wk2
WHERE T2.ORDER_DATETIME >= '2023-07-01 00:00:00'
  AND T2.ORDER_DATETIME < '2023-08-01 00:00:00'
) T3
LEFT OUTER JOIN MEMBER_MST T4
ON T3.wk4 = T4.MEMBER_CODE
WHERE T3.wk5 >= 365
GROUP BY 2
ORDER BY 1 DESC,2 DESC