hmc
September 20th, 2005, 10:54 AM
I am trying to detect overlapping dates in my schedule. This is my table:
ID fromDate toDate
1 10/15/2005 10/20/2005
2 10/21/2005 10/24/2005
3 10/27/2005 10/29/2005
The query i have now detects overlapping dates, but it selects records twice.
SELECT T1.ID, T1.dateFrom, T1.dateTo, T2.ID, T2.dateFrom, T2.dateTo
FROM testdate AS T1, testdate AS T2
WHERE
T1.ID<>T2.ID AND
((T1.dateFrom<T2.dateFrom AND T1.dateTo>T2.dateFrom) OR
(T1.dateFrom<T2.dateTo AND T1.dateTo>T2.dateTo) OR
(T1.dateFrom<T2.dateFrom AND T1.dateTo>T2.dateTo) OR
(T1.dateFrom>T2.dateFrom AND T1.dateTo<T2.dateTo));
Result:
T1.ID T1.dateFrom T1.dateTo T2.ID T2.dateFrom T2.dateTo
4 9/25/2005 10/6/2005 3 9/24/2005 9/30/2005
3 9/24/2005 9/30/2005 4 9/25/2005 10/6/2005
5 10/4/2005 10/15/2005 4 9/25/2005 10/6/2005
4 9/25/2005 10/6/2005 5 10/4/2005 10/15/2005
How can i adjust this query so that it is only appears once in the result??
DISTINCT keyword does not help.
ID fromDate toDate
1 10/15/2005 10/20/2005
2 10/21/2005 10/24/2005
3 10/27/2005 10/29/2005
The query i have now detects overlapping dates, but it selects records twice.
SELECT T1.ID, T1.dateFrom, T1.dateTo, T2.ID, T2.dateFrom, T2.dateTo
FROM testdate AS T1, testdate AS T2
WHERE
T1.ID<>T2.ID AND
((T1.dateFrom<T2.dateFrom AND T1.dateTo>T2.dateFrom) OR
(T1.dateFrom<T2.dateTo AND T1.dateTo>T2.dateTo) OR
(T1.dateFrom<T2.dateFrom AND T1.dateTo>T2.dateTo) OR
(T1.dateFrom>T2.dateFrom AND T1.dateTo<T2.dateTo));
Result:
T1.ID T1.dateFrom T1.dateTo T2.ID T2.dateFrom T2.dateTo
4 9/25/2005 10/6/2005 3 9/24/2005 9/30/2005
3 9/24/2005 9/30/2005 4 9/25/2005 10/6/2005
5 10/4/2005 10/15/2005 4 9/25/2005 10/6/2005
4 9/25/2005 10/6/2005 5 10/4/2005 10/15/2005
How can i adjust this query so that it is only appears once in the result??
DISTINCT keyword does not help.