/*
Turn this:

1   aaaa    1/15/2012
1   aaaa    1/16/2012
1   aaaa    1/17/2012
1   aaaa    1/20/2012
1   aaaa    1/21/2012

Into this:

1   aaaa    1/15/2012   1/17/2012
1   aaaa    1/20/2012   1/21/2012
*/


WITH base_table AS (
SELECT /*+ materialize */
detail AS nk, run_date,
CASE WHEN LAG(run_date, 1, TO_DATE('1900-01-01', 'yyyy-mm-dd')) over (ORDER BY run_date ASC) = run_date - 1 THEN 'N' ELSE 'Y' END AS begin_date_flag,
CASE WHEN LEAD(run_date, 1, TO_DATE('9999-12-31', 'yyyy-mm-dd')) over (ORDER BY run_date ASC) = run_date + 1 THEN 'N' ELSE 'Y' END AS end_date_flag
FROM jxw_fact_stage
),
begin_date AS (
  SELECT ROW_NUMBER() over (ORDER BY run_date) rn, nk, run_date AS begin_date FROM base_table WHERE begin_date_flag = 'Y'
),
end_date AS (
  SELECT ROW_NUMBER() over (ORDER BY run_date) rn, nk, run_date AS end_date FROM base_table WHERE end_date_flag = 'Y'
)
SELECT b.nk, b.begin_date, e.end_date
FROM begin_date b
inner join end_date e
ON b.nk = e.nk
AND b.rn = e.rn

/*
aaaa    1/15/2012   1/17/2012
aaaa    1/20/2012   1/21/2012
*/