by 쿼리초보 [SQL Query] [2019.06.12 12:03:18]
select YEAR
,DECODE( DAY , 'x' , MONTH-1 , 'xx' , MONTH+1 , MONTH) as MONTH
,T1
,DECODE( DAY , 'x' , '' , 'xx' , '' , day) as day
,DAY
,CELL_NUM
,WEEKS
,WEEK
from (
SELECT '2019' YEAR,
'6' MONTH,
T_DUMMY.RNUM + 1 - '7' as t1 ,
DECODE (
GREATEST (T_DUMMY.RNUM + 1 - '7', 0),
0, 'x',
DECODE (GREATEST (T_DUMMY.RNUM + 1 - '7', '30'),
'30', TO_CHAR (T_DUMMY.RNUM + 1 - '7'),
'xx'))
DAY,
T_DUMMY.RNUM CELL_NUM,
T_DUMMY.WEEKS + 1 WEEKS,
T_DUMMY.WEEK WEEK
FROM ( SELECT (W.W * 7) + D.D RNUM, W.W WEEKS, D.D WEEK
FROM (SELECT 0 W FROM DUAL
UNION ALL
SELECT 1 FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL
UNION ALL
SELECT 4 FROM DUAL
UNION ALL
SELECT 5 FROM DUAL) W,
(SELECT 1 D FROM DUAL
UNION ALL
SELECT 2 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL
UNION ALL
SELECT 4 FROM DUAL
UNION ALL
SELECT 5 FROM DUAL
UNION ALL
SELECT 6 FROM DUAL
UNION ALL
SELECT 7 FROM DUAL) D
ORDER BY (W.W * 7) + D.D) T_DUMMY
) ta
ORDER BY ta.CELL_NUM
전자정부 프레임워크 휴일관리에 있는 달력을 조금 변환시켜서..
해당달 조회세시 전달 과 다음달의 잔여날짜도 뿌려주고싶습니다
위쿼리 날리면
,DECODE( DAY , 'x' , '' , 'xx' , '' , day) as day <- 이부분 을 어떻게 해야할지 생각이 떠오르지 않는데
방법 아시면 좀 부탁드릴게요..
조회한달은 6월이고
DAY가 X인 부분은.... 5월달이고
XX인 부분은 7월입니다
감사합니다.
SELECT TO_CHAR(dt, 'yyyy') year , TO_CHAR(dt, 'fmmm') month , TO_CHAR(dt, 'fmdd') day , TO_CHAR(dt, 'd' ) week , CEIL(cell_num / 7) weeks , cell_num FROM (SELECT LEVEL cell_num , TRUNC(ym, 'd') + LEVEL - 1 dt FROM (SELECT TO_DATE('201906', 'yyyymm') ym FROM dual) CONNECT BY LEVEL <= TRUNC(LAST_DAY(ym), 'd') + 6 - TRUNC(ym, 'd') + 1 ) ;