SQL & PL/SQL

All,

I would like to find the number of "groups" of consecutive dates (2 or more)... assume I have a table of ID,DATE... and my dates are as follows:

3/11/2009
5/18/2009
7/8/2009 grp 1
7/9/2009 grp 1
7/24/2009
8/31/2009 grp 2
9/1/2009 grp 2
9/2/2009 grp 2

In this case, the answer should be 2.... as there are two groups of "2 or more" consecutive dates.

How would I write this query?


What I have so far:

select id, count(*)
from (
select p.id,
p.date,
case
when lag(p.date) over(partition by p.id order by p.date) + 1 = p.date then
(lag(p.date) over(partition by p.id order by p.date) + 1) || '-LAG'

when lead(p.date) over(partition by p.id order by p.date) - 1 = p.date then
(lead(p.date) over(partition by p.id order by p.date) -1) || '-LEAD'
else
NULL
end as CONS_DATES
from mytable p
where p.id = 123)
where CONS_DATES like '%-LEAD'
group by id;

It kinda works, but it is not very elegant. I am sure there is better way to do this. Also, I ideally would like to control the "consecutive number", could be 2,3,4,n. This query only works fore 2 or more, it will not work for 3 or more.

Your help is greatly appreciated!!!

Edited by: dloeppky@centennialcollege.ca on Sep 1, 2009 1:26 PM

Edited by: dloeppky@centennialcollege.ca on Sep 1, 2009 1:32 PM