-- SELECT MAX(ORDER_DATETIME),MEMBER_CODE FROM EC_ORDERS WHERE ORDER_DATETIME < '2023-07-01' GROUP BY MEMBER_CODE;
-- SELECT MIN(ORDER_DATETIME),MEMBER_CODE FROM EC_ORDERS WHERE ORDER_DATETIME >= '2023-07-01' GROUP BY MEMBER_CODE;


SELECT
-- ORDER_DATETIME
-- ,2023年7月最初のORDER_DATETIME AS ORDER_DATE
s.ORDER_DATETIME,
s2.ORDER_DATETIME AS ORDER_DATE
,m.MEMBER_CODE AS CODE
,CASE WHEN OPTOUT_TYPE = '0' THEN '可' 
      WHEN OPTOUT_TYPE = '1' THEN '不可' ELSE '不明' END AS OPTOUT
-- FROM EC_ORDERS as e
-- INNER JOIN MEMBER_MST as m
FROM MEMBER_MST as m
-- on e.MEMBER_CODE = m.MEMBER_CODE 
INNER JOIN (SELECT MAX(ORDER_DATETIME) as ORDER_DATETIME,MEMBER_CODE FROM EC_ORDERS WHERE ORDER_DATETIME < '2023-07-01' GROUP BY MEMBER_CODE) as s
on s.MEMBER_CODE = m.MEMBER_CODE
INNER JOIN (SELECT MIN(ORDER_DATETIME) as ORDER_DATETIME,MEMBER_CODE FROM EC_ORDERS WHERE ORDER_DATETIME >= '2023-07-01' GROUP BY MEMBER_CODE) as s2
on s.MEMBER_CODE = s2.MEMBER_CODE 
and date(s.ORDER_DATETIME ,'1 years') < date(s2.ORDER_DATETIME)
WHERE s2.ORDER_DATETIME >= '2023-07-01' 
  AND  s2.ORDER_DATETIME < '2023-08-01' 
ORDER BY 
ORDER_DATE desc
,m.MEMBER_CODE desc;