Get Minimum day in a continuous series

Hi,

l've a series of day which record the date of an event. l would like to count the # of continuous days for the event. In this case, it would be 14/5, 15/5, 16/5, 17/5, 18/5, 19/5 and 20/5. Any idea to do this in SQL?

Date
--
20/5
19/5
18/5
17/5
16/5
15/5
14/5
09/5
07/5
06/5
05/5
And what about May/5 May/6 and May/7?.. those also meet your request.|||

The only thing that comes to mind is to use a cursor to loop through your rows.

You can then check each row to determine if it is exactly one day beyond the last row.

Do you just want to get a count of the highest number of days? Or what do you want to return?

|||

If you are using SQL Server 2005 you
can do this (this can be modified to work
with SQL Server 2000 if required)

set dateformat dmy
create table #dates(dt datetime)

insert into #dates(dt) values('20/5/2006')
insert into #dates(dt) values('19/5/2006')
insert into #dates(dt) values('18/5/2006')
insert into #dates(dt) values('17/5/2006')
insert into #dates(dt) values('16/5/2006')
insert into #dates(dt) values('15/5/2006')
insert into #dates(dt) values('14/5/2006')
insert into #dates(dt) values('09/5/2006')
insert into #dates(dt) values('07/5/2006')
insert into #dates(dt) values('06/5/2006')
insert into #dates(dt) values('05/5/2006');

with dt_rn(dt,rn)
as
(select dt,
dt-rank() over(order by dt)
from #dates)
select convert(char(5),min(dt),103) as EventStart,
count(*) as ContinuousDays
from dt_rn
group by rn
having count(*)>1

drop table #dates

|||

Hello

To get the Minimum day of each sequence you could try this...

drop table #dates

set dateformat dmy
create table #dates(dt datetime)

insert into #dates(dt) values('20/5/2006')
insert into #dates(dt) values('19/5/2006')
insert into #dates(dt) values('18/5/2006')
insert into #dates(dt) values('17/5/2006')
insert into #dates(dt) values('16/5/2006')
insert into #dates(dt) values('15/5/2006')
insert into #dates(dt) values('14/5/2006')
insert into #dates(dt) values('09/5/2006')
insert into #dates(dt) values('07/5/2006')
insert into #dates(dt) values('06/5/2006')
insert into #dates(dt) values('05/5/2006')

select * from #dates od where not exists (select dt from #dates id where id.dt = od.dt-1)
and exists (select dt from #dates id2 where id2.dt = od.dt+1)

The 2nd Part of the where clause is needed to eliminate the 09/05 date... If you consider that single date a "sequence" also then remove the 2nd part.

Be warned though that this querry can eat up a lot of performance.

|||this query must return the event start date and count of continous event dates. you must to replace date_column and table_name

select start_date, count(event_date) as cnt from (
select date_column as event_date,
(select max(date_column) from (
select date_column
from table_name t1
left outer join table_name t2
on t1.date_column=dateadd(t2,1,date_column)
where t2.date is null
) as start_dates
where start_dates.date_column<=events.date_column
) as start_date
from table_name events
) as event_dates
group by event_start_date