END DATE라는칼럼은어디서 나온건지 모르겠네요..
그리고 어떤 조건에서 저런 결과가 나오는지 전혀 감이 안오네요..^^;

글게요 첫번째 결과행의 END_DATE를 보면 2009-06-30 이란 날짜는 절대 리스트에서는 나올수가 없는 결과인데;;

어떻게 저렇게;;나올수가 ㅋ

질문의 의도와 조건이나 그런것들 다시 수정해서 올려주세요

저 위에 색으로 회사 구분 되어 있는거 보이시죠?
기간을 C => B로 넘어가는 것이 1번
B => C로 넘어가는 것이 2번
C => B로 넘어가는 것이 3번으로 나누어서

START DATE/ END DATE
1. 빨간색의 min(등록일)/ 파란색 min(등록일) -1
2. 파란색의 min(등록일)/ 연두색 min(등록일) -1
3. 연두색의 min(등록일)/ 분홍색의 min(등록일) -1
4. 분홍색의 min(등록일)/ 현재 날짜.

이런식으로의 정렬을 원하는데 저렇게 어떻게 구분을 해야 할지 ^^;;

WITH t AS
(
SELECT 'ABC' comcd, 'A' jobcd, '서울' dept, '001001' empid, TO_DATE('20060907','yyyymmdd') regdt, 'C' comgb FROM dual
UNION ALL SELECT 'ABC', 'A', '서울', '001002', TO_DATE('20080710','yyyymmdd'), 'C' FROM dual
UNION ALL SELECT 'ABC', 'O', '서울', '001001', TO_DATE('20081201','yyyymmdd'), 'C' FROM dual
UNION ALL SELECT 'ABC', 'O', '서울', '001004', TO_DATE('20090118','yyyymmdd'), 'C' FROM dual
UNION ALL SELECT 'ABC', 'I', '서울', '001001', TO_DATE('20090118','yyyymmdd'), 'C' FROM dual
UNION ALL SELECT 'ABC', '3', '서울', '001001', TO_DATE('20090118','yyyymmdd'), 'C' FROM dual
UNION ALL SELECT 'ABC', 'I', '서울', 'AAA001', TO_DATE('20090701','yyyymmdd'), 'B' FROM dual
UNION ALL SELECT 'ABC', 'O', '서울', 'AAA002', TO_DATE('20090701','yyyymmdd'), 'B' FROM dual
UNION ALL SELECT 'ABC', 'O', '서울', 'AAA001', TO_DATE('20090701','yyyymmdd'), 'B' FROM dual
UNION ALL SELECT 'ABC', 'I', '서울', '001001', TO_DATE('20090725','yyyymmdd'), 'C' FROM dual
UNION ALL SELECT 'ABC', 'I', '서울', 'AAA001', TO_DATE('20090803','yyyymmdd'), 'B' FROM dual
)
SELECT comcd
, MIN(regdt) sdt
, LEAD(MIN(regdt)-1,1,TRUNC(sysdate)) OVER(PARTITION BY comcd ORDER BY grp) edt
FROM
(
SELECT a.*
, SUM(flag) OVER(PARTITION BY comcd ORDER BY regdt) grp
FROM
(
SELECT t.*
, DECODE(comgb,LAG(comgb) OVER(PARTITION BY comcd ORDER BY regdt),0,1) flag
FROM t
) a
)
GROUP BY comcd, grp
ORDER BY comcd, grp
;

와우 감사 합니다.

대단 하셔요 ㅎㅎ 잘 쓰도록 하겠습니다.

분석후 다시 글 올릴께요

역시 마농님^^ 몇번 마농님꺼 봤는데;;; 왜 생각이 난나는지;;; 아~~ 그룹을 못만들어서 거루로 가장 큰값부터 정렬한다음에 MIN해서 값을 찾아왔는데 ㅠㅠ

WITH A AS
(
SELECT 'ABC' AS CD, 'A' AS G, '서울' AS C, '001001' AS S, '20060907' AS DT, 'C' AS GBN FROM DUAL UNION ALL
SELECT 'ABC' AS CD, 'A' AS G, '서울' AS C, '001002' AS S, '20080710' AS DT, 'C' AS GBN FROM DUAL UNION ALL
SELECT 'ABC' AS CD, 'O' AS G, '서울' AS C, '001001' AS S, '20081201' AS DT, 'C' AS GBN FROM DUAL UNION ALL
SELECT 'ABC' AS CD, 'O' AS G, '서울' AS C, '001004' AS S, '20090118' AS DT, 'C' AS GBN FROM DUAL UNION ALL
SELECT 'ABC' AS CD, 'I' AS G, '서울' AS C, '001001' AS S, '20090118' AS DT, 'C' AS GBN FROM DUAL UNION ALL
SELECT 'ABC' AS CD, '3' AS G, '서울' AS C, '001001' AS S, '20090118' AS DT, 'C' AS GBN FROM DUAL UNION ALL
SELECT 'ABC' AS CD, 'I' AS G, '서울' AS C, 'AAA001' AS S, '20090701' AS DT, 'B' AS GBN FROM DUAL UNION ALL
SELECT 'ABC' AS CD, 'O' AS G, '서울' AS C, 'AAA002' AS S, '20090701' AS DT, 'B' AS GBN FROM DUAL UNION ALL
SELECT 'ABC' AS CD, 'O' AS G, '서울' AS C, 'AAA001' AS S, '20090701' AS DT, 'B' AS GBN FROM DUAL UNION ALL
SELECT 'ABC' AS CD, 'I' AS G, '서울' AS C, '001001' AS S, '20090725' AS DT, 'C' AS GBN FROM DUAL UNION ALL
SELECT 'ABC' AS CD, 'I' AS G, '서울' AS C, 'AAA001' AS S, '20090803' AS DT, 'B' AS GBN FROM DUAL
)
SELECT CD, GBN, DT
, CASE WHEN TTTT = '99999999' THEN '진행중' ELSE TTTT END AS TT
FROM (
SELECT A.*
, MIN(TTTT) OVER( PARTITION BY GBN ORDER BY DT DESC)
FROM (
SELECT CD, GBN, DT
, LEAD( TT, 1, 99999999) OVER( ORDER BY DT, TT ) AS TTTT
FROM (
SELECT A.*
, CASE WHEN GBN <> TEMP THEN TO_CHAR( TO_DATE( DT ) - 1, 'YYYYMMDD') END AS TT

FROM (
SELECT A.*
, LAG( GBN, 1, GBN ) OVER( ORDER BY DT ) AS TEMP
FROM A
) A
) A
) A
ORDER BY 1,3
) A
WHERE TTTT IS NOT NULL

WITH t AS
(
SELECT 'ABC' comcd, 'A' jobcd, '서울' dept, '001001' empid, TO_DATE('20060907','yyyymmdd') regdt, 'C' comgb FROM dual
UNION ALL SELECT 'ABC', 'A', '서울', '001002', TO_DATE('20080710','yyyymmdd'), 'C' FROM dual
UNION ALL SELECT 'ABC', 'O', '서울', '001001', TO_DATE('20081201','yyyymmdd'), 'C' FROM dual
UNION ALL SELECT 'ABC', 'O', '서울', '001004', TO_DATE('20090118','yyyymmdd'), 'C' FROM dual
UNION ALL SELECT 'ABC', 'I', '서울', '001001', TO_DATE('20090118','yyyymmdd'), 'C' FROM dual
UNION ALL SELECT 'ABC', '3', '서울', '001001', TO_DATE('20090118','yyyymmdd'), 'C' FROM dual
UNION ALL SELECT 'ABC', 'I', '서울', 'AAA001', TO_DATE('20090701','yyyymmdd'), 'B' FROM dual
UNION ALL SELECT 'ABC', 'O', '서울', 'AAA002', TO_DATE('20090701','yyyymmdd'), 'B' FROM dual
UNION ALL SELECT 'ABC', 'O', '서울', 'AAA001', TO_DATE('20090701','yyyymmdd'), 'B' FROM dual
UNION ALL SELECT 'ABC', 'I', '서울', '001001', TO_DATE('20090725','yyyymmdd'), 'C' FROM dual
UNION ALL SELECT 'ABC', 'I', '서울', 'AAA001', TO_DATE('20090803','yyyymmdd'), 'B' FROM dual
)
SELECT comcd
,comgb
,Min(regdt)
,Max(dt)-1
FROM
(SELECT a.*,
Sum(gb) over(ORDER BY regdt) gb2,
Lead(regdt) over(ORDER BY regdt) dt
FROM
(SELECT t.*
,Decode(comgb,Lag(comgb) over(PARTITION BY comcd ORDER BY regdt),0,1) gb
FROM t) a) a
GROUP BY gb2,comgb,comcd