Dates that intersect

I've been researching this problem for awhile now and I can't seem to come to a solution, hopefully someone here can help.

Currently I'm working with Microsoft SQL server management, I've been trying to do the following:

Previously, the old query would just return the results that fit between two dates Heres the previous query:

SELECT e.Name, o.StartDate, o.EndDate
FROM dbo.Name e, dbo.Date o
WHERE
where e.Name = o.Name
and o.StartDate <= '2010-09-28 23:59:59'
and o.EndDate >= '2010-9-28 00:00:00'
and e.Name like 'A'开发者_开发问答

Example table that is produced after the query runs (The real table has a lot more rows obviously :P) :

Name    Start                   End
A       2010-09-28 07:00:00     2010-09-28 17:00:00
A       2010-09-28 13:45:00     2010-09-28 18:00:00
A       2010-09-28 08:00:00     2010-09-28 16:00:00
A       2010-09-28 07:00:00     2010-09-28 15:30:00

However we need to change this, so that the query does the following:

find the dates that intersect for a day x find the dates that don't intersect for a day x

I've found a real useful site regarding this http://bloggingabout.net/blogs/egiardina/archive/2008/01/30/check-intersection-of-two-date-ranges-in-sql.aspx However the date to compare against is inputted, mine on the other hand has to all dates that intersect/don't intersect.

Thanks for the help everyone.

Dates that intersect:

SELECT
    E.name,
    O.start_date,
    O.end_date
FROM
    dbo.Names E
INNER JOIN dbo.Dates O ON
    O.name = E.name AND
    O.start_date < @end_date AND
    O.end_date > @start_date
WHERE
    E.name LIKE 'A'

Dates that don't intersect is simply the inverse with respect to the dates:

SELECT
    E.name,
    O.start_date,
    O.end_date
FROM
    dbo.Names E
INNER JOIN dbo.Dates O ON
    O.name = E.name AND
    (O.start_date > @end_date OR
    O.end_date < @start_date)
WHERE
    E.name LIKE 'A'

I didn't account for exact matches of the dates. Decide how you want to handle those and then adjust accordingly.

Try the following:

SELECT e.Name, o.StartDate, o.EndDate
FROM dbo.Name e, dbo.Date o
WHERE
where e.Name = o.Name
and o.StartDate <= '2010-09-28 23:59:59'
and o.EndDate >= '2010-09-28 00:00:00'
and exists
(select null from dbo.Date o2 
 where o.Name = o2.Name and  
 o.StartDate <= o2.EndDate and  
 o.EndDate >= o2.StartDate and  
 o.ID <> o2.ID)

for intersecting dates; change ..and exists... to ..and not exists... for non-intersecting dates. (I've assumed that dbo.Date has a primary key field called ID - you want to prevent date ranges overlapping themselves.)