SELECT
DAMIN AS ORDER_DATE
,EC_ORDERS.MEMBER_CODE AS CODE
,CASE MEMBER_MST.OPTOUT_TYPE
      WHEN 0 THEN '可'
      WHEN 1 THEN '不可'
      ELSE '不明' 
 END                    AS OPTOUT
FROM
EC_ORDERS
LEFT JOIN
(SELECT MEMBER_CODE AS MINCODE,MIN(ORDER_DATETIME) AS DAMIN
FROM EC_ORDERS
WHERE ORDER_DATETIME LIKE '2023-07%'
GROUP BY MINCODE
ORDER BY ORDER_DATETIME)
ON EC_ORDERS.MEMBER_CODE = MINCODE
LEFT JOIN MEMBER_MST
ON EC_ORDERS.MEMBER_CODE = MEMBER_MST.MEMBER_CODE
WHERE EC_ORDERS.ORDER_DATETIME NOT LIKE '2023-07%'
GROUP BY EC_ORDERS.MEMBER_CODE
HAVING cast(julianday(DAMIN) - julianday(MAX(ORDER_DATETIME)) as integer) >= 365
--AND NOT EXISTS (SELECT * FROM EC_ORDERS WHERE cast(julianday(DAMIN) - julianday(ORDER_DATETIME) as integer) < 365 AND )
--GROUP BY MEMBER_CODE
;