WITH A AS
(
SELECT '홍길동'AS NAME,'20090701' AS SD,'20090703' AS ED ,'휴가' AS GB FROM DUAL UNION ALL
SELECT '홍길동'AS NAME,'20090711' AS SD,'20090711' AS ED ,'월차' AS GB FROM DUAL UNION ALL
SELECT '금강산'AS NAME,'20090701' AS SD,'20090707' AS ED ,'하계' AS GB FROM DUAL UNION ALL
SELECT '금강산'AS NAME,'20090709' AS SD,'20090712' AS ED ,'훈련' AS GB FROM DUAL UNION ALL
SELECT '한라산'AS NAME,'20090702' AS SD,'20090702' AS ED ,'월차' AS GB FROM DUAL UNION ALL
SELECT '한라산'AS NAME,'20090705' AS SD,'20090710' AS ED ,'년차' AS GB FROM DUAL UNION ALL
SELECT '백두산'AS NAME,'20090703' AS SD,'20090730' AS ED ,'휴직' AS GB FROM DUAL
)
SELECT NAME
, COUNT(CASE WHEN GB = '휴가' THEN 1 END) C01
, COUNT(CASE WHEN GB = '월차' THEN 1 END) C02
, COUNT(CASE WHEN GB = '하계' THEN 1 END) C03
, COUNT(CASE WHEN GB = '훈련' THEN 1 END) C04
, COUNT(CASE WHEN GB = '년차' THEN 1 END) C05
, COUNT(CASE WHEN GB = '휴직' THEN 1 END) C06
FROM ( SELECT A.*
, ROWNUM AS RN
FROM A
) A
, ( SELECT '2009' || LPAD(MM,2,0) || LPAD(DD,2,0) AS DAY#
FROM ( SELECT LEVEL AS MM FROM DUAL CONNECT BY LEVEL <= 12 )
, ( SELECT LEVEL AS DD FROM DUAL CONNECT BY LEVEL <= 31 )
WHERE '2009' || LPAD(MM,2,0) || LPAD(DD,2,0) BETWEEN '20090702' AND '20090711'
) B
WHERE B.DAY# BETWEEN SD AND ED
GROUP BY NAME
ORDER BY MAX(RN)

결과 1

with test as
(
select '홍길동' name , '20090701' sdate ,'20090703' edate , '휴가' ds from dual union all
select '홍길동' name , '20090711' sdate ,'20090711' edate , '월차' ds from dual union all
select '금강산' name , '20090701' sdate ,'20090707' edate , '하계' ds from dual union all
select '금강산' name , '20090709' sdate ,'20090712' edate , '훈련' ds from dual union all
select '한라산' name , '20090702' sdate ,'20090702' edate , '월차' ds from dual union all
select '한라산' name , '20090705' sdate ,'20090710' edate , '년차' ds from dual union all
select '백두산' name , '20090703' sdate ,'20090730' edate , '휴직' ds from dual
)


select T1.cd ,T2.name ,T2.ds FROM
(
select to_char(to_date('20090702','YYYY-MM-DD') - 1 + level,'YYYYMMDD') cd from dual connect by level <=
(to_date('20090711','YYYY-MM-DD') -to_date('20090702','YYYY-MM-DD')) +1
)T1 , test T2
WHERE T1.cd BETWEEN t2.sdate AND T2.EDATE
order by 2 desc ,1

결과 2

with test as
(
select '홍길동' name , '20090701' sdate ,'20090703' edate , '휴가' ds from dual union all
select '홍길동' name , '20090711' sdate ,'20090711' edate , '월차' ds from dual union all
select '금강산' name , '20090701' sdate ,'20090707' edate , '하계' ds from dual union all
select '금강산' name , '20090709' sdate ,'20090712' edate , '훈련' ds from dual union all
select '한라산' name , '20090702' sdate ,'20090702' edate , '월차' ds from dual union all
select '한라산' name , '20090705' sdate ,'20090710' edate , '년차' ds from dual union all
select '백두산' name , '20090703' sdate ,'20090730' edate , '휴직' ds from dual
)

SELECT
NAME,
SUM(DECODE(DS,'휴가',DD)) "휴가",
SUM(DECODE(DS,'월차',DD)) "월차",
SUM(DECODE(DS,'하계',DD)) "하계",
SUM(DECODE(DS,'훈련',DD)) "훈련",
SUM(DECODE(DS,'년차',DD)) "년차",
SUM(DECODE(DS,'휴직',DD)) "휴직"
FROM
(
select T1.NAME ,
CASE WHEN T1.EDATE >= T2.ED THEN TO_DATE(T2.ED,'YYYY-MM-DD') ELSE TO_DATE(T1.EDATE,'YYYY-MM-DD') END -
CASE WHEN T1.SDATE <= T2.SD THEN TO_DATE(T2.SD,'YYYY-MM-DD') ELSE TO_DATE(T1.SDATE,'YYYY-MM-DD') END +1 DD,
DS

FROM TEST T1,
(SELECT '20090702' SD , '20090711' ED FROM DUAL) T2
WHERE T1.SDATE <= t2.ED
AND T1.EDATE >= T2.SD
)
GROUP BY NAME

구경서님의 댓글 살짝 수정해봤습니다.
TO_DATE(LEAST(t1.edate,t2.ed),'yyyymmdd') - TO_DATE(GREATEST(t1.sdate,t2.sd),'yyyymmdd') + 1 dd

ㄴ 압 제가 밥먹고 올릴라고 했는대 !! ㅋㅋ 좋네요!!

빠르고 정확한 답변달아주신

러드님,구경서님,마농님

정말로 감사드립니다.

특히 구경서님께 더욱 감사드립니다.