1

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)

14
  • 2
    Please do not cross post. You can flag your question to be moved by moderators. – Kin Shah Mar 4 '15 at 20:27
  • I deleted it from StackOverflow. – A.B. Mar 4 '15 at 21:06
  • 01.05.2012 - 01.09.2012 is more than 1 month but left unchanged. The next 3 rows are all less than 1 month but are combined into 1 row. The last row is also less than 1 month but left unchanged. I think you should describe more carefully your criteria. – ypercubeᵀᴹ Mar 4 '15 at 21:07
  • I am so sorry. I corrected the post. The difference must be greater than 1 month. – A.B. Mar 4 '15 at 21:08
  • 2
    Which version of SQL-Server are you using? This is a gaps-and-islands problem and for 2012+ versions, it will be easier to write. – ypercubeᵀᴹ Mar 4 '15 at 22:00
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.

1
  • Thx i will check your answer tomorrow – A.B. Mar 4 '15 at 22:11
0
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
1
  • The ; is called statement terminator. It should terminate" (go at the end of) every statement, not before of some of them. – ypercubeᵀᴹ Sep 25 '16 at 14:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.