How to avoid overlapping date ranges when using a grouping clause?

Question

I have a situation where I need to find the time intervals between changes in value. I tried a simple group by element, 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 (note 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 the request to 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 or suggestions?

Answer 1

After researching SQL SERVER ISLANDS , as suggested by @KM, I came up with the following query, which seems to work well when additional class codes are added to the dataset.

 select a.code, a.class, a.txdate as mindate, b.txdate as maxdate from ( --Find minimum island select code , class , txdate , row_number() over (order by code, class, txdate) as n from #items tb1 where not exists ( select * from #items tb2 where datediff(d, tb1.txdate, tb2.txdate) = -1 and tb1.class = tb2.class and tb1.code = tb2.code ) ) as a inner join ( --Find maximum island select code , class , txdate , row_number() over (order by code, class, txdate) as n from #items tb1 where not exists ( select * from #items tb2 where datediff(d, tb1.txdate, tb2.txdate) = 1 and tb1.class = tb2.class and tb1.code = tb2.code ) ) as b on an = bn 

The only caveat in this approach is that the number of records in the minimum set should match the number of records in the maximum set. Until now, I could not do anything to make it untrue. However, I have not tested null values ​​or performance.

Answer 2

EDIT . As noted in the comments, this is still not entirely correct.

 ;with cteNtile as ( select code, class, txdate, ntile((select count(*) from (select NULL as dummy from #items group by code, class) a)) over(partition by code, class order by txdate) as tilenum from #items ) select code, class, MIN(txdate) as mindate, MAX(txdate) as maxdate from cteNtile group by code, class, tilenum order by mindate, maxdate 

Answer 3

Here is a query that can give the desired result.

 ;WITH items1 AS ( SELECT ROW_NUMBER() OVER (ORDER BY txdate) rowid, code, class, txdate from #items ), items2 AS ( SELECT ROW_NUMBER() OVER (ORDER BY rowid) id, rowid, i1.Code, i1.Class, i1.txdate FROM items1 i1 WHERE NOT EXISTS (SELECT 1 FROM items1 i2 WHERE i2.txdate < i1.txdate AND i2.class = i1.class AND i2.Code = i1.Code AND i2.rowid+1=i1.rowid) ) SELECT items2.code, items2.class, items2.txdate mindate, items1.txdate maxdate FROM items2, items2 items3, items1 WHERE (items2.id+1=items3.id AND items3.rowid-1=items1.rowid) OR items2.rowid = (SELECT MAX(t.rowid) FROM items1 t) UNION SELECT items2.code, items2.class, MAX(items2.txdate) mindate, MAX(items1.txdate) maxdate FROM items2, items1 WHERE items1.class = items2.class GROUP BY items1.class, items2.class, items2.code, items2.class ORDER BY items2.txdate 

Answer 4

I think you cannot do this with a simple select statement.

You can use the cursor to iterate through the lines and detect class changes.