SELECT *
FROM DUSER.MY_NOTICE_MGMT A
WHERE A.USER_ID = :USER_ID
AND A.NOTICE_DATE >= :START_NOTICE_DATE
AND A.NOTICE_DATE <= :END_NOTICE_DATE
AND A.REPETITION_CD = 'O'
UNION ALL
/* 주마다 반복인 상태 - 표현은 어떻게 할지 결정 필요 */
SELECT A.*
FROM DUSER.MY_NOTICE_MGMT A
, (
SELECT TO_CHAR(TEMP_DATE, 'YYYYMMDD') AS TEMP_DATE
, TO_CHAR(TEMP_DATE, 'D') AS WEEK
FROM (
SELECT TO_DATE(:START_NOTICE_DATE, 'YYYYMMDD') + LEVEL - 1 AS TEMP_DATE
FROM DUAL
CONNECT
BY LEVEL <= TO_DATE(:END_NOTICE_DATE, 'YYYYMMDD') - TO_DATE(:START_NOTICE_DATE, 'YYYYMMDD') + 1
) B
) B
WHERE A.USER_ID = :USER_ID
AND A.NOTICE_DATE <= :END_NOTICE_DATE
AND A.REPETITION_CD = 'W'
AND B.WEEK = TO_CHAR(TO_DATE(A.NOTICE_DATE, 'YYYYMMDD'), 'D')
UNION ALL
/* 월마다 반복인 상태 - 표현은 어떻게 할지 결정 필요 */
SELECT *
FROM DUSER.MY_NOTICE_MGMT A
WHERE A.USER_ID = :USER_ID
AND A.NOTICE_DATE <= :END_NOTICE_DATE
AND A.REPETITION_CD IN ('M', 'Y')
UNION ALL
/* 반복 */
;
SELECT TO_DATE(:END_NOTICE_DATE, 'YYYYMMDD') - TO_DATE(NOTICE_DATE, 'YYYYMMDD')
, A.REPETITION_DAY
, A.*
FROM DUSER.MY_NOTICE_MGMT A
WHERE A.USER_ID = :USER_ID
AND A.NOTICE_DATE <= :END_NOTICE_DATE
AND A.REPETITION_CD IN ('D', 'R')
AND (
:END_NOTICE_DATE = A.NOTICE_DATE
OR TO_DATE(:END_NOTICE_DATE, 'YYYYMMDD') - TO_DATE(A.NOTICE_DATE, 'YYYYMMDD') >= A.REPETITION_DAY
)
;
카테고리 없음