CODEWITHSUNDEEP
sqlsql-servert-sql
stanke
stanke

Reputation: 386

How to create bigger time period from multiple smaller ones?

I have a table containing time periods

StartDate               CancelDate
2014-01-01 00:00:00.000 2014-01-15 00:00:00.000
2014-01-16 00:00:00.000 2014-01-31 00:00:00.000
2014-02-05 00:00:00.000 2014-02-15 00:00:00.000

You can see that periods in first two rows are connected and a they are creating one bigger period from 2014-01-01 to 2014-01-31, and there is a period that is not connected in third row.

How can I get this table from the one above? 

    StartDate               CancelDate
    2014-01-01 00:00:00.000 2014-01-31 00:00:00.000
    2014-02-05 00:00:00.000 2014-02-15 00:00:00.000

Upvotes: 1

Views: 50

Answers (2)

mohan111
mohan111

Reputation: 8865

declare @t table (start VARCHAR(10),enddate VARCHAR(10))
insert into @t (start,enddate)values
('2014-01-01','2014-01-15'),
('2012-01-01','2012-01-15'),
('2014-01-15','2014-01-31'),
('2014-02-02','2014-02-15'),
('2014-02-15','2014-02-28')

Select MIN(CAST (start AS DATE)),MAX(CAST (enddate AS DATE)) 
from @t 
group by LEFT(start,7),LEFT(enddate,7)

OR Using CTE

   ;WITH CTE AS 
(
select DISTINCT

    (select min(start) from @t
        where LEFT(start,7) = LEFT(m.start,7)
        ) as StartDate,
    (select max(enddate) from @t
        where LEFT(enddate,7) = LEFT(m.enddate,7)
        ) as EndDate,
        ROW_NUMBER()OVER(PARTITION BY LEFT(STart,7) ORDER BY  LEFT(STart,7) DESC)RN 
from
    @t m
    )
    Select DISTINCT StartDate,EndDate from CTE

Upvotes: 0

Jayvee
Jayvee

Reputation: 10873

something on these lines should work:

SELECT MIN(startdate) StartDate, CancelDate
FROM
(
SELECT a.startdate, 
COALESCE(b.canceldate, a.canceldate) canceldate
FROM dt a
LEFT JOIN dt b ON
(b.startdate=DATEADD(d,1,a.canceldate) OR a.canceldate=b.startdate)
) x
GROUP BY CancelDate

dt is your data table

Upvotes: 1

Related Questions