How to avoid overlapping date ranges when using a grouping clause?
- by k rey
I have a situation where I need to find time spans between value changes. I tried a simple group by clause but it eliminates overlapping changes. Consider the following example:
create table #items (
code varchar(4)
, class varchar(4)
, txdate datetime
)
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-01');
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-02');
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-03');
insert into #items (code, class, txdate) values ('A', 'D', '2010-01-04');
insert into #items (code, class, txdate) values ('A', 'D', '2010-01-05');
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-06');
insert into #items (code, class, txdate) values ('A', 'C', '2010-01-07');
insert into #items (code, class, txdate) values ('A', 'D', '2010-01-08');
insert into #items (code, class, txdate) values ('A', 'D', '2010-01-09');
select code
, class
, min(txdate) mindate
, max(txdate) maxdate
from #items
group by code, class
This returns the following results (notice the overlapping date ranges):
|code|class|mindate |maxdate |
----------------------------------
|A |C |2010-01-01|2010-01-07|
|A |D |2010-01-04|2010-01-09|
I would like to have the query return the following:
|code|class|mindate |maxdate |
----------------------------------
|A |C |2010-01-01|2010-01-03|
|A |D |2010-01-04|2010-01-05|
|A |C |2010-01-06|2010-01-07|
|A |D |2010-01-08|2010-01-09|
Any ideas and suggestions?
