Home » SQL & PL/SQL » SQL & PL/SQL » Numbering groups of consecutive dates (Oracle 9i)  () 1 Vote
Numbering groups of consecutive dates [message #386967] Tue, 17 February 2009 07:25 Go to next message
miro_mtl
Messages: 4
Registered: February 2009
Location: Montreal, Quebec
Junior Member
Hello all,

I have a table with records containing a date and an employee ID, no tuple. I am trying to figure out the different periods of consecutive dates for an employee, which can span over more than 1 year. I have tried with Lead but with little success. I thought that if I can get to number each period, I could then get the min and max date for each period. There is also the connect by way I explored a bit but witout succes also.

I need help on this one.

Thank you.
Re: Numbering groups of consecutive dates [message #386975 is a reply to message #386967] Tue, 17 February 2009 07:48 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Please show us the input and the output to get a applicable answer.
Re: Numbering groups of consecutive dates [message #386978 is a reply to message #386975] Tue, 17 February 2009 07:54 Go to previous messageGo to next message
miro_mtl
Messages: 4
Registered: February 2009
Location: Montreal, Quebec
Junior Member
What I have:

EMPID DATE ...
1234 10-01-2007
1234 11-01-2007
1234 12-01-2007
1234 13-01-2007
1234 27-12-2007
1234 28-12-2007
1234 29-12-2007
1234 30-12-2007
1234 31-12-2007
1234 01-01-2008

What I want:

EMPID DATE PERIOD
1234 10-01-2007 1
1234 11-01-2007 1
1234 12-01-2007 1
1234 13-01-2007 1
1234 27-12-2007 2
1234 28-12-2007 2
1234 29-12-2007 2
1234 30-12-2007 2
1234 31-12-2007 2
1234 01-01-2008 2

Final result:

Period #1 10-01-2007 to 13-01-2007
Period #2 27-12-2007 to 01-01-2008

[Updated on: Tue, 17 February 2009 07:55]

Report message to a moderator

Re: Numbering groups of consecutive dates [message #386982 is a reply to message #386978] Tue, 17 February 2009 08:05 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
How period is calculated in the given example. Post DDL and DML for quick reply.

Thanks
Trivendra

Re: Numbering groups of consecutive dates [message #386984 is a reply to message #386982] Tue, 17 February 2009 08:10 Go to previous messageGo to next message
miro_mtl
Messages: 4
Registered: February 2009
Location: Montreal, Quebec
Junior Member
The period number is precisely what I want to get! When there is a gap in the dates, it is a new period.

Thanks.
Re: Numbering groups of consecutive dates [message #386985 is a reply to message #386984] Tue, 17 February 2009 08:13 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Have a look at the documentation for the lag function
Re: Numbering groups of consecutive dates [message #386987 is a reply to message #386985] Tue, 17 February 2009 08:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you can use Dense_Rank and Row_Number

with src as (
select 1234 col_1 ,to_date('10-01-2007','dd-mm-yyyy') col_2 from dual union all
select 1234 ,to_date('11-01-2007','dd-mm-yyyy') from dual union all
select 1234 ,to_date('12-01-2007','dd-mm-yyyy') from dual union all
select 1234 ,to_date('13-01-2007','dd-mm-yyyy') from dual union all
select 1234 ,to_date('27-12-2007','dd-mm-yyyy') from dual union all
select 1234 ,to_date('28-12-2007','dd-mm-yyyy') from dual union all
select 1234 ,to_date('29-12-2007','dd-mm-yyyy') from dual union all
select 1234 ,to_date('30-12-2007','dd-mm-yyyy') from dual union all
select 1234 ,to_date('31-12-2007','dd-mm-yyyy') from dual union all
select 1234 ,to_date('01-01-2008','dd-mm-yyyy') from dual)
select col_1
      ,col_2
      ,dense_rank() over (partition by col_1 order by grp) 
from (      
select col_1
      ,col_2
      ,col_2-row_number() over (partition by col_1 order by col_2) grp
from   src)
order by col_1,col_2;
Re: Numbering groups of consecutive dates [message #387002 is a reply to message #386987] Tue, 17 February 2009 09:54 Go to previous message
miro_mtl
Messages: 4
Registered: February 2009
Location: Montreal, Quebec
Junior Member
Thanks JRowbottom,

Your solution works like a charm, this is exactly what I wanted. I knew there was a clean and simple solution.

Thanks
Previous Topic: WAY OF STORING DATA IN ORACLE
Next Topic: Automatic Value Passing to WHERE Clause...
Goto Forum:
  


Current Time: Fri Dec 09 15:46:40 CST 2016

Total time taken to generate the page: 0.19650 seconds