Need Oracle sql query for grouping the date -

i have table date 02/11/2015 01/12/2015. eg...

attndate -------- 02/11/2015 03/11/2015 -- -- -- 01/12/2015. 

this table may have missing dates also.. lets assume 06/11/2015 , 20/11/2015 missing table.

i want output like

sl.no      attnfrom          attntill   1.       02/11/2015        05/11/2015   2.       07/11/2015        19/11/2015   3.       21/11/2015        01/12/2015 

kindly me output in oracle plsql

you can lead , lag analytic functions - in subquery group over, may missed - can analytic 'trick'.

if @ difference between each date , lowest date broken sequence, in case 0, 1, 2, 3, 5, ..., 27, 28, 29. can see attndate - min(attndate) on ().

you have unbroken sequence available row_number() on (order attndate), gives 1, 2, 3, ... 28.

if subtract 1 other each contiguous block of dates gets same answer, i've called 'slot_no':

select attndate,   attndate - min(attndate) on ()     - row_number() on (order attndate) slot_no your_table; 

with data every row gets either -1, 0 or 1. (you can add 2 make them more friendly if want, works if gaps in data single days). can group slot number:

with cte (   select attndate,     attndate - min(attndate) on ()       - row_number() on (order attndate) slot_no   your_table ) select dense_rank() on (order slot_no) slot_no,   min(attndate) attnfrom, max(attndate) attntill cte group slot_no order slot_no; 

with generated data:

alter session set nls_date_format = 'dd/mm/yyyy'; your_table (attndate) (   select date '2015-11-02' + level - 1 dual connect level <= 4   union select date '2015-11-07' + level - 1 dual connect level <= 13   union select date '2015-11-21' + level - 1 dual connect level <= 11 ), cte (   select attndate,     attndate - min(attndate) on ()       - row_number() on (order attndate) slot_no   your_table ) select dense_rank() on (order slot_no) slot_no,   min(attndate) attnfrom, max(attndate) attntill cte group slot_no order slot_no;     slot_no attnfrom   attntill  ---------- ---------- ----------          1 02/11/2015 05/11/2015          2 07/11/2015 19/11/2015          3 21/11/2015 01/12/2015 

if real scenario getting these ranges multiple keys, person id, can add partition by clause each of analytic function calls, in 3 over () sections.