How to isolate non-sequential start/end dates? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to isolate non-sequential start/end dates?

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2007-08-23 : 16:18:01

I am working with a table similar to this one:

-----------------------------------

create table #test1
(
symbol char(3),
dt smalldatetime
)
go

insert into #test1
select 'abc','01/01/1990'
insert into #test1
select 'abc','01/02/1990'
insert into #test1
select 'abc','01/03/1990'
insert into #test1
select 'abc','01/04/1990'
insert into #test1
select 'abc','01/05/1990'
insert into #test1
select 'def','01/03/1990'
insert into #test1
select 'def','01/04/1990'
insert into #test1
select 'def','01/05/1990'
insert into #test1
select 'def','01/06/1990'
insert into #test1
select 'def','01/07/1990'
insert into #test1
select 'ghi','01/01/1990'
insert into #test1
select 'ghi','01/02/1990'
insert into #test1
select 'ghi','01/06/1990'
insert into #test1
select 'ghi','01/07/1990'
insert into #test1
select 'ghi','01/08/1990'
go

select symbol, "startdt" = min(dt), "enddt" = max(dt)
from #test1
group by symbol
go

drop table #test1
go

---------------------------

You will notice that when I run this query, the result set returned gives me the correct start and end dates for the symbols 'abc' and 'def'. However, I am at a loss as to how to return the 2 sets of start and end dates for symbol 'ghi'.

I would like these to read:
symbol startdt enddt
----------------------------------
ghi 01/01/1990 01/02/1990
ghi 01/06/1990 01/08/1990

(Obviously my query is setup only to handle the earliest startdt and the latest enddt, thus missing the 'gap' between 01/02/1990 and 01/06/1990).

I've tried tons of self-joins to try and reconcile this issue but to no avail.

If anyone has any suggestions as to how to do this efficiently, I would tremendously appreciate it.

Thanks,

-KS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 16:35:12

[code]select symbol,
min(dt),
max(dt2)
from (
select t1.symbol,
t1.dt,
t2.dt as dt2,
(select count(distinct t3.symbol) from #test as t3 where t3.symbol < t1.symbol and t3.dt <= t1.dt) AS r
from #test as t1
inner join #test as t2 on t2.symbol = t1.symbol
where t2.dt - 1 = t1.dt
) as d
group by symbol,
r
order by 1,
2[/code]

E 12°55'05.25"
N 56°04'39.16"

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2007-08-23 : 16:54:53

Thank you very much - I'll give that a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources