데이터베이스 질문과 답변
안녕하세요.
회사에 인터넷이 되지 않아 집에와서 질문을 올리네요.
조회조건 : 시작일자 ~ 종료일자
select dpet_cd
, manager
, bas_dt
from ( select dept_cd
, manager
, bas_dt
, rank() over (partition by dept_cd , substr(bas_dt, 0, 6) order by bas_dt desc) rnk
from (
select 'A10000' dept_cd , '12800449' manager , '20130101' bas_dt , '20130403' ed_dt from dual
union all
select 'A10000' dept_cd , '12505115' manager , '20130403' bas_dt , '20130416' ed_dt from dual
union all
select 'A10000' dept_cd , '12801631' manager , '20130416' bas_dt , '99991231' ed_dt from dual
)
where ed_dt > '20130201' AND BAS_DT <='20130530'
)
where rnk = 1
------------------ 조회결과 --------------
DEPT_CD | MANAGER | BAS_DT
--------------------------------------------------------------
A10000 12800449 20130101
A10000 12801631 20130416
이렇게 나오는 결과를...
조회기간의 FROM ~ TO 의 월별로 데이타를 출력하고 싶습니다.
예를 들면 2~ 3 월은 2013년1월1일부터 MANAGER 인 12800449 가 출력이 되고.
4~5월은 4월16일부터 MANAGER 인 12801631 가 출력이 되는 구조로..
(월중변경인 경우 해당월의 마지막으로 임명된 사람이 그 월의 최종 MANAGER 가 되는 업무요건이어서 내부에서 rank 를 두었습니다.)
MONTH | DEPT_CD | MANAGER | BAS_DT
------------------------------------------------------------------------
201302 A10000 12800449 20130101
201303 A10000 12800449 20130101
201304 A10000 12801631 20130416
201305 A10000 12801631 20130416
이런식으로 출력이 되었으면 합니다.
염치불구하고 답변 부탁드려요..
일하는 곳 인터넷이 안되니 ㅡㅜ
아침에 일어나서 질문 올리네요.. 아 요샌 인터넷 안되는 곳이 왜이리 많은 걸까요?
by 마농
[2013.06.14 08:51:59]
WITH month_t AS
(
SELECT TO_CHAR(ADD_MONTHS(sdt, LEVEL-1), 'yyyymm') ym
, TO_CHAR(GREATEST(sdt, ADD_MONTHS(TRUNC(sdt, 'mm'), LEVEL-1)), 'yyyymmdd') sdt
, TO_CHAR(LEAST(edt, ADD_MONTHS(TRUNC(sdt, 'mm'), LEVEL) - 1), 'yyyymmdd') edt
FROM (SELECT TO_DATE('20130201', 'yyyymmdd') sdt -- 조회시작일
, TO_DATE('20130530', 'yyyymmdd') edt -- 조회종료일
FROM dual
)
CONNECT BY LEVEL <= MONTHS_BETWEEN(TRUNC(edt, 'mm'), TRUNC(sdt, 'mm')) + 1
)
, data_t AS
(
SELECT 'A10000' dept_cd, '12800449' manager, '20130101' bas_dt, '20130403' ed_dt FROM dual
UNION ALL SELECT 'A10000', '12505115', '20130403', '20130416' FROM dual
UNION ALL SELECT 'A10000', '12801631', '20130416', '99991231' FROM dual
)
SELECT a.ym
, b.dept_cd
, MAX(b.manager) KEEP(DENSE_RANK LAST ORDER BY b.bas_dt) manager
, MAX(b.bas_dt) bas_dt
FROM month_t a
LEFT OUTER JOIN data_t b
PARTITION BY (b.dept_cd)
ON b.ed_dt > a.sdt
AND b.bas_dt <= a.edt
GROUP BY b.dept_cd, a.ym
ORDER BY b.dept_cd, a.ym
;
by 디케이
[2013.06.14 09:44:25]
-- 마농님 쿼리 참조해서 다른방법으로 만들어봤습니다. -- CONNECT BY LEVEL <= CEIL(MONTHS_BETWEEN(edt, sdt)) SELECT ym, dept_cd, manager, bas_dt FROM month_t a, data_t b WHERE a.ym||'99' BETWEEN b.bas_dt and b.ed_dt
by 마농
[2013.06.14 09:59:31]
다시 보니. 기간(a.sdt~a.edt) 검색할 필요가 없었네요.
월의 마지막 일자(a.edt)로만 비교하면 되겠네요.
- 변경전 : ON b.ed_dt > a.sdt AND b.bas_dt <= a.edt
- 변경후 : ON b.ed_dt > a.edt AND b.bas_dt <= a.edt
이렇게 변경하면 Group By 가 필요 없이 1건만 조인되겠네요.
by 손님
[2013.06.14 20:29:57]
감사합니다. 덕분에 잘 해결되었습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.