Geek asks and answers

Delete dates from other dates?

I have the following lines:

CREATE TABLE #TEMP (id int, name varchar(255), startdate datetime, enddate datetime)
INSERT INTO #TEMP VALUES(1, 'John', '2011-01-11 00:00:00.000','2011-01-11 00:01:10.000')
INSERT INTO #TEMP VALUES(2, 'John', '2011-01-11 00:00:20.000','2011-01-11 00:01:05.000')
INSERT INTO #TEMP VALUES(3, 'John', '2011-01-11 00:01:40.000','2011-01-11 00:01:50.000')
INSERT INTO #TEMP VALUES(4, 'Adam', '2011-01-11 00:00:40.000','2011-01-11 00:01:20.000')
INSERT INTO #TEMP VALUES(5, 'Adam', '2011-01-11 00:00:45.000','2011-01-11 00:01:15.000')

SELECT * FROM #TEMP

DROP TABLE #TEMP

I am trying to delete records containing dates contained in other dates in order to get the following:

John 2011-01-11 00:00:00.000 2011-01-11 00:01:10.000
John 2011-01-11 00:01:40.000 2011-01-11 00:01:50.000
Adam 2011-01-11 00:00:40.000 2011-01-11 00:01:20.000

Any suggestions on how to do this for a table of about 100k rows?

+5
source share
2 answers

This gives the desired result:

DELETE T1 FROM #TEMP T1
WHERE EXISTS(
    SELECT NULL FROM #TEMP T2
    WHERE   t1.id <> t2.id
    AND     t1.name = t2.name
    AND     t1.startdate >= t1.startdate
    AND     t1.enddate   <= t1.enddate
)

http://msdn.microsoft.com/en-us/library/ms188336.aspx

Change . I just noticed that there is one problem. If there are duplicates (the same beginning and end), both will be deleted (none of them match John, even with the same date). Therefore, you need to consider this:

DELETE T1 FROM #TEMP T1
WHERE EXISTS(
    SELECT NULL FROM #TEMP T2
    WHERE   t1.id <> t2.id
    AND     t1.name = t2.name
    AND     t1.startdate > t2.startdate
    AND     t1.enddate   < t2.enddate
    OR      t1.id <> t2.id
    AND     t1.name = t2.name
    AND     t1.startdate = t2.startdate
    AND     t1.enddate   < t2.enddate
    OR      t1.id <> t2.id
    AND     t1.name = t2.name
    AND     t1.startdate > t2.startdate
    AND     t1.enddate   = t2.enddate
    OR      t1.id > t2.id
    AND     t1.name = t2.name
    AND     t1.startdate = t2.startdate
    AND     t1.enddate   = t2.enddate
)
+2
source 
DELETE t1 FROM #TEMP t1
INNER JOIN #TEMP t2 ON t2.startdate < t1.startdate AND t1.enddate < t2.enddate
AND t1.name = t2.name

Results are consistent

+2
source

More articles:


All Articles