|
|
Posted by Razvan Socol on 10/04/06 17:34
Given the following DDL and sample data:
CREATE TABLE Intervals (
ID int IDENTITY PRIMARY KEY,
Start int,
Stop int,
UNIQUE (Start, Stop)
)
INSERT INTO Intervals VALUES (1,2)
INSERT INTO Intervals VALUES (3,4)
INSERT INTO Intervals VALUES (5,8)
INSERT INTO Intervals VALUES (7,9)
INSERT INTO Intervals VALUES (11,12)
INSERT INTO Intervals VALUES (12,13)
INSERT INTO Intervals VALUES (3,6)
INSERT INTO Intervals VALUES (5,9)
The following query returns the expected result:
SELECT (
SELECT MIN(d.Start) FROM Intervals d
WHERE d.Start<x.Stop AND NOT EXISTS (
SELECT * FROM (
SELECT DISTINCT Stop FROM Intervals a
WHERE NOT EXISTS (
SELECT * FROM Intervals b
WHERE a.Stop BETWEEN b.Start AND b.Stop
AND b.Start BETWEEN a.Start AND a.Stop
AND a.ID<>b.ID
)
) y WHERE y.Stop<x.Stop
AND d.Start<y.Stop
)
) as Start, x.Stop
FROM (
SELECT DISTINCT Stop FROM Intervals a
WHERE NOT EXISTS (
SELECT * FROM Intervals b
WHERE a.Stop BETWEEN b.Start AND b.Stop
AND b.Start BETWEEN a.Start AND a.Stop
AND a.ID<>b.ID
)
) x
The above query was inspired by reading (a few years ago) the following
article:
http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/groupingtimeintervals.asp
Razvan
Rsapru@gmail.com wrote:
> i have a table containing following data
> effdate termdate uid
> ----------- ----------- -----------
> 1 2 1
> 3 4 2
> 5 8 3
> 7 9 4
> 11 12 5
> 12 13 6
> 3 6 7
> 5 9 8
>
> i need to replace all the overlapping records with one record
> such that resultant table shud look like
>
>
> effdate termdate uid
> 1 2 1
> 11 13 2
> 3 9 3
>
>
> Thanks
Navigation:
[Reply to this message]
|