--7月の１カ月間に受注データがある
WITH A AS(
SELECT MEMBER_CODE AS CODE,MIN(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,
JULIANDAY(DATE(ORDER_DATE)) - JULIANDAY(DATE(MAX(ORDER_DATETIME))) AS CNT,
OPTOUT
--DATEDIFF('DAY',LAST_DATE,ORDER_DATE) AS CNT
FROM B
WHERE ORDER_DATETIME < '2023-07-01 00:00:00'
GROUP BY CODE
)
SELECT ORDER_DATE,CODE,OPTOUT
FROM C
WHERE CNT > 365
ORDER BY ORDER_DATE DESC,CODE DESC;