데이터베이스 질문과 답변
by 바라기짱조아 [2018.08.28 22:09:50]
오라클로 구현하려하니 힘드네요. 총 파라미터는 2개를 받아서 처리합니다.
*input값2에 가장 가까운 가장 마지막년월과 그에 해당하는 방배정번호를 가져오는 sql을 알고싶어요.
예를들어
inputparm1 : 201806 기준으로 총 7개의 방(최대 7개)이 생성되는거죠.
1번방은 inputParma이 201806이니 1년전 으로하면
1)201707 ~ 201806
2)201607~ 201706
3)201507 ~ 201606
4)201407 ~ 201506
5)201307 ~ 201406
6)201207 ~ 201306
7)201107 ~ 201206
이런식으로 7개 정보가 생성됩니다.
이 정보를 기준으로
(원하는 결과값)
input값2 -
(input)201611 -> 2번방에 201612
(input)201606 -> 3번방,201512
(input)201511 -> 3번방,201512
이런식으로 가장가까운 해당 방배정번호에 속해있는 그해 년의 마지막 월을 가져오고싶습니다.
한번더 설명드리면 201511-> 해당 방배정번호는 3번방... 그안에 속해있는 데이터는 201507 ~ 201606 이니. 가장가까운 그해 년도의 마지막월은 201512입니다.
이렇게 셀렉트절에서 바로 뽑아보려니 잘안되네요..
어떻게 하면 좋을지요..
고수님들 부탁드립니다.
SELECT last_mm
FROM (
SELECT LEVEL AS room_no
, to_char(add_months(to_date('&input1','YYYYMM'),-12*LEVEL +1 ),'YYYYMM') from_yyyymm
, to_char(add_months(to_date('&input1','YYYYMM'),-12*(LEVEL -1)),'YYYYMM') to_yyyymm
, to_char(trunc(add_months(to_date('&input1','YYYYMM'),-12*(LEVEL -1)),'YYYY') -1,'YYYYMM') AS last_mm
FROM dual
CONNECT BY LEVEL <= 7
)
WHERE '&input2' BETWEEN from_yyyymm AND to_yyyymm
param1 이 12월인 경우 오류.
WITH t AS
(
SELECT '201806' p1, '201611' p2 FROM dual
UNION ALL SELECT '201806', '201606' FROM dual
UNION ALL SELECT '201806', '201511' FROM dual
UNION ALL SELECT '201812', '201511' FROM dual
UNION ALL SELECT '201812', '201512' FROM dual
)
SELECT p1, p2
, y1 - y2 + x + DECODE(m1, 12, 1, 0) romm_no
, y2 - x || '12' ym
FROM (SELECT p1, p2
, y1, m1
, y2, m2
, CASE WHEN m1 < m2 OR m1 = '12' THEN 0 ELSE 1 END x
FROM (SELECT p1, p2
, SUBSTR(p1, 1, 4) y1, SUBSTR(p1, 5, 2) m1
, SUBSTR(p2, 1, 4) y2, SUBSTR(p2, 5, 2) m2
FROM t
)
)
;
디비안에도 올리셨네요..
SELECT BEGIN_YM
, END_YM
, TO_CHAR
(
CASE
WHEN ADD_MONTHS( TRUNC( BEGIN_YM, 'YEAR' ), 11 ) BETWEEN BEGIN_YM AND END_YM
THEN
ADD_MONTHS( TRUNC( BEGIN_YM, 'YEAR' ), 11 )
ELSE
ADD_MONTHS( TRUNC( END_YM, 'YEAR' ), 11 )
END
, 'YYYYMM'
)
OUTPUT
FROM ( SELECT ADD_MONTHS( TO_DATE( :PARAM1, 'YYYYMM' ), (LEVEL * -12) + 1 ) BEGIN_YM
, ADD_MONTHS( TO_DATE( :PARAM1, 'YYYYMM' ), (LEVEL - 1) * -12 ) END_YM
FROM DUAL
CONNECT BY LEVEL <= 7 )
WHERE TO_DATE( :PARAM2, 'YYYYMM' ) BETWEEN BEGIN_YM AND END_YM
이미 2개나 답변이 달렸지만, 저도 답변을 달아봅니다. ^^
마농님 댓글대로 12월일때 문제가 있었네요
그래서 수정 버젼 올려 봅니다
Select last_mm
FROM (
SELECT LEVEL AS room_no
, to_char(add_months(to_date('&input1','YYYYMM'),-12*LEVEL +1 ),'YYYYMM') from_yyyymm
, to_char(add_months(to_date('&input1','YYYYMM'),-12*(LEVEL -1)),'YYYYMM') to_yyyymm
, to_char(trunc(add_months(to_date('&input1','YYYYMM'),-12*(LEVEL -1)+1),'YYYY') -1,'YYYYMM') AS last_mm
FROM dual
CONNECT BY LEVEL <= 7
)
WHERE '&input2' BETWEEN from_yyyymm AND to_yyyymm
WITH t AS
(
SELECT '201806' p1, '201611' p2 FROM dual
UNION ALL SELECT '201806', '201606' FROM dual
UNION ALL SELECT '201806', '201511' FROM dual
UNION ALL SELECT '201812', '201511' FROM dual
UNION ALL SELECT '201812', '201512' FROM dual
)
SELECT p1, p2
, rn + 1 rn
, yy - rn || '12' ym
FROM (SELECT p1, p2
, SUBSTR(p1, 1, 4) - DECODE(SUBSTR(p1, 5, 2), '12', 0, 1) yy
, FLOOR(MONTHS_BETWEEN(TO_DATE(p1, 'yyyymm'), TO_DATE(p2, 'yyyymm')) / 12) rn
FROM t
)
;
) 버튼을 클릭하여 작성 하시면 됩니다.