Dates that intersect

Question

I have been studying this problem for a while, and I cannot come to a solution, hope someone here can help.

I am currently working with Microsoft SQL Server Management, I tried to do the following:

Previously, the old query simply returned results that match two dates Represents 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'

An example of a table created after running a query (a real table has many more rows: 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 dates that intersect during day x find dates that do not intersect during day x

http://bloggingabout.net/blogs/egiardina/archive/2008/01/30/check-intersection-of-two-date-ranges-in-sql.aspx , , / .

.

Answer 1

, :

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'

, , :

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'

. , , .

Answer 2

:

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)

; .. ... .. ... . ( , dbo.Date ID - , ).