--7月の１カ月間に受注データがある
WITH A AS(
SELECT MEMBER_CODE AS CODE,MIN(DATE(ORDER_DATETIME)) AS ORDER_DATE
FROM EC_ORDERS
WHERE DATE(ORDER_DATETIME) BETWEEN '2023-07-01' AND '2023-07-31'
GROUP BY CODE
),
--対象IDの履歴、オプトアウト情報追加
B AS (
SELECT ORDER_DATE,CODE,ORDER_DATETIME,
(CASE OPTOUT_TYPE WHEN 0 THEN "可"
WHEN 1 THEN "不可" ELSE "不明" END) AS OPTOUT
FROM A
INNER JOIN EC_ORDERS
ON A.CODE = EC_ORDERS.MEMBER_CODE
INNER JOIN MEMBER_MST
ON A.CODE = MEMBER_MST.MEMBER_CODE
),
--6月以前で直近の日付と日数差
C AS(
SELECT CODE,ORDER_DATE,
MAX(DATE(ORDER_DATETIME)) AS LAST_DATE,
JULIANDAY(ORDER_DATE) - JULIANDAY(MAX(DATE(ORDER_DATETIME))) AS CNT,
OPTOUT
--ORDER_DATE-MAX(DATE(ORDER_DATETIME))  AS CNT
--DATEDIFF('DAY',LAST_DATE,ORDER_DATE) AS CNT
FROM B
WHERE DATE(ORDER_DATETIME)<'2023-07-01'
GROUP BY CODE
)
SELECT ORDER_DATE,CODE,OPTOUT
FROM C
WHERE CNT >= 366
ORDER BY ORDER_DATE DESC,CODE DESC;