Date overlapping

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 1 2
3 9 3
thanks
Hi,
The interpretation of the required results may be slightly ambiguous due to
your definition of 'overlapping'. But the following should give you what you
are looking for:
CREATE TABLE Overlap
(uid int identity(1,1) PRIMARY KEY,
effdate datetime,
termdate datetime)
GO
Using your sample data and generating date values:
INSERT INTO Overlap VALUES(getdate()+1,getdate()+2)
INSERT INTO Overlap VALUES(getdate()+3,getdate()+4)
INSERT INTO Overlap VALUES(getdate()+5,getdate()+8)
INSERT INTO Overlap VALUES(getdate()+7,getdate()+9)
INSERT INTO Overlap VALUES(getdate()+11,getdate()+12)
INSERT INTO Overlap VALUES(getdate()+12,getdate()+13)
INSERT INTO Overlap VALUES(getdate()+3,getdate()+6)
INSERT INTO Overlap VALUES(getdate()+5,getdate()+9)
GO
I am using a CTE with subqueries although I am sure only subqueries would do
just fine.
WITH Sequence(d1,d2)
AS
(
SELECT ov.d1, d2=MAX(ov.d2)
FROM
(
SELECT d1=o.effdate,d2=o.termdate
FROM Overlap o LEFT JOIN Overlap o1
ON o1.effdate>=o.effdate AND o1.termdate<=o.termdate
AND o1.uid<>o.uid
) ov
Group By d1
)
SELECT effdate=MIN(d1),termdate=d2,uid=ROW_NUMBER() OVER(ORDER BY d2)
FROM
(
SELECT s1.d1,d2=ISNULL(s2.d2,s1.d2) FROM Sequence s1 LEFT JOIN Sequence s2
ON s2.d1 > s1.d1 AND s2.d1 < s1.d2
AND s1.d1<>s2.d1
) ov1
GROUP BY d2
GO
This will merge overlapping date ranges assuming that events ending and
starting on the same day are not 'overlapping'. If you think that events are
'overlapping' if they start and end on the same day then replace the CTE
above with this one:
WITH Sequence(d1,d2)
AS
(
SELECT ov.d1, d2=MAX(ov.d2)
FROM
(
SELECT d1=o.effdate,d2=o.termdate
FROM Overlap o LEFT JOIN Overlap o1
ON o1.effdate>=o.effdate AND o1.termdate<=o.termdate
AND o1.uid<>o.uid
) ov
Group By d1
)
SELECT effdate=MIN(d1),termdate=d2,uid=ROW_NUMBER() OVER(ORDER BY d2)
FROM
(
SELECT s1.d1,d2=ISNULL(s2.d2,s1.d2) FROM Sequence s1 LEFT JOIN Sequence s2
ON s2.d1 BETWEEN s1.d1 AND s1.d2
AND s1.d1<>s2.d1
) ov1
GROUP BY d2
GO
Nathan H. Omukwenyi
<Rsapru@.gmail.com> wrote in message
news:1159931179.273146.111290@.e3g2000cwe.googlegro ups.com...
>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 1 2
> 3 9 3
> thanks
>
|||Hey,
Thanks for replyig... it helped but i have to find a shoter way...
Thanks
Rohit