A query for merging date intervals

Question

I have a simple table with 3 columns:

(cid, from, to) -> (int, datetime, datetime)

The difference between dateFrom and dateTo should be >= 1 month. And unfortunately i have some corrupt data which do not satisfy this condition.

INPUT: 

An example data (cid - from - to) : 
8 - 01.05.2012 - 01.09.2012 
8 - 02.09.2012 - 10.09.2012 
8 - 11.09.2012 - 18.09.2012 
8 - 18.09.2012 - 02.10.2012 
8 - 03.10.2012 - 04.10.2013 

DESIRED OUTPUT in a temp table:

I want to merge the dates whose interval is less than a month into 1 row:

8 - 01.05.2012 - 01.09.2012 
8 - 02.09.2012 - 02.10.2012 
8 - 03.10.2012 - 04.10.2013 

So everything between 02.09.2012 - 02.10.2012 is merged into one row.

Is this possible using only T-SQL? (even without a cursor)

Answer 1

I think what you'll need to do is create another temp table that has the same columns but with an identity column so you can use it for joining purposes. I also noticed that your date format had day.month.year which threw me off initially....

This will get you pretty close:

create table newtable ( cid int, fromdate datetime, todate datetime)
go

insert into dbo.newtable
values
(8 , '05/01/2012', '09/01/2012'), 
(8 , '09/02/2012', '09/10/2012'),
(8 , '09/11/2012', '09/18/2012'),
(8 , '09/18/2012', '10/02/2012'),
(8 , '10/03/2012', '10/04/2013')


create table dbo.newtable2 (newIdent int identity(1,1), cid int, fromdate datetime, todate datetime, targetdate datetime)

insert into dbo.newtable2 (cid, fromdate, todate, targetdate)
select cid, fromdate, todate, dateadd(m, 1, fromdate) from dbo.newtable


select cid, fromdate, todate from newtable2 where todate > targetdate

union

select cid, min(fromdate), max(todate) from newtable2 where newident in
(
select a.newident
from newtable2 a inner join newtable2 b on dateadd(m, 1, a.fromdate) > b.fromdate and a.newident = b.newident
where 
a.todate <= a.targetdate
order by a.fromdate
)
group by cid

The nested select is sorted by fromdate so you will get merged records in consecutive rows so that you can leverage MIN and MAX functions.

The only thing is the UNION will not have the rows sorted but you can always dump that into another temp table and then sort it.

Answer 2

create table #test ( cid int, fromdt datetime , todt datetime)

insert into #test values
(8,'05-01-2012','09-01-2012'),
(8,'09-02-2012','09-10-2012'),
(8,'09-11-2012','09-18-2012'),
(8,'09-18-2012','10-02-2012'),
(8,'10-03-2012','10-04-2013') 


;with cte as
(select cid, fromdt, todt, row_number() over(order by fromdt) -
            ROW_NUMBER() over(partition by datepart(mm, fromdt) order by datepart(mm, fromdt)) grp from #test)
select cid, min(fromdt), max(todt) from cte
group by cid,grp