Grouping SQL results by continous time intervals (oracle sql)

Question

Hi I have following data in the table as below and I am looking for a way to group the continuous time intervals for each id to return:

CREATE TABLE DUMMY

( ID VARCHAR2(10 BYTE),

TIME_STAMP VARCHAR2(8 BYTE),

NAME VARCHAR2(255 BYTE)

);

ID, min(TIME_STAMP) "startDate", max(TIME_STAMP) "endDate", NAME group by ID , NAME

something like

100 20011128 20011203 David

100 20011204 20011207 Unknown

100 20011208 20011215 David

100 20011216 20011220 Sara

and so on ...

Thanks

ps. I have sample script, but i dont no how to attach my file. ds.

Hi every one here is more input:

1. There is only one record with time_stamp for a specific ID.

2. Users kan be diffrent, for example for day1 David, day2 unknown, day3 David and so on.

so there is one row for every day of year for each ID but with diffrent users.

Now, i want to se the break point, diffrences base on time_stamp intervals from day one

until last day for a specific ID in day order from begin day until last day.

Query Result should be :

ID NAME MIN_DATE MAX_DATE

100 David 20011128 20050407

100 Sara 20050408 20050417

100 David 20050418 20080416

100 Unknown 20080417 20080507

100 David 20080508 20080508

100 Unknown 20080509 20080607

100 David 20080608 20080608

100 Unknown 20080609 20080921

100 David 20080922 20080922

100 Unknown 20080923 20081231

100 David 20090101 20090405

thanks

Hi again, many tanks to every one, i have solved the problem, here is the solution:

select id, min(time_stamp), max(time_stamp), name

  from (

select id, time_stamp, name,

       max(rn) over (order by time_stamp) grp

  from (

select id, time_stamp, name,

       case when lag(name) over (order by time_stamp) <> name or

                 row_number() over (order by time_stamp) = 1

            then row_number() over (order by time_stamp)

       end rn

 from dummy

      )

      )

group by id, grp, name

order by 1

Answer 1

Select
   ID,
   Name,
   min(time_stamp) min_date,
   max(time_stamp) max_date
from
   Dummy
group by
   Id,
   Name

That should work.

IF you want the date range for each Id, but all the names you can do:

Select
   d.Id,
   d.Name,
   dr.min_date,
   dr.max_date
from
   Dummy d

   JOIN 
      (Select
         Id,
         min(time_stamp) min_date,
         max(time_stamp) max_date
      from
         Dummy
      group by
         Id 
      ) dr
      on ( dr.Id = d.Id)

Ron