WITH JULY_DATA AS(
	SELECT 
		MEMBER_CODE,
		MIN(ORDER_DATETIME) AS MIN_ORDER,
		DATETIME(MIN(ORDER_DATETIME), '-1 years', '-1 days') AS JUDGE_DATE
	FROM
		EC_ORDERS
	WHERE
		ORDER_DATETIME >= '2023-07-01 00:00:00'
	GROUP BY
		MEMBER_CODE
),
 MAX_DATA AS(
	SELECT 
		EO.MEMBER_CODE,
		MAX(ORDER_DATETIME) AS MAX_ORDER
	FROM
		EC_ORDERS EO
		INNER JOIN JULY_DATA JD
		ON EO.MEMBER_CODE = JD.MEMBER_CODE
	WHERE
		EO.ORDER_DATETIME < JD.MIN_ORDER
	GROUP BY
		EO.MEMBER_CODE
)
SELECT 
	JD.MIN_ORDER AS ORDER_DATE,
	MD.MEMBER_CODE AS CODE,
	CASE MM.OPTOUT_TYPE
		WHEN '0' THEN '可'
		WHEN '1' THEN '不可'
		ELSE '不明'
	END AS OPTOUT
FROM
	MAX_DATA MD
	INNER JOIN JULY_DATA JD
	ON MD.MEMBER_CODE = JD.MEMBER_CODE
	INNER JOIN MEMBER_MST MM
	ON MD.MEMBER_CODE = MM.MEMBER_CODE
WHERE
	MD.MAX_ORDER <= JD.JUDGE_DATE
ORDER BY
	ORDER_DATE DESC,
	MD.MEMBER_CODE DESC
;