SQL Server – How to Detect If Two Time Periods Overlap

You simply need to think about the date range slightly differently. An event falls in your given range if the start date is prior to the end of your reporting period, and the end date is after the beginning of your reporting period:

Select * from tbl_Events
where StartDate <= @end
    AND EndDate >= @start;

For example:

CREATE TABLE MyTestDates
(
    MyTestDateID INT NOT NULL PRIMARY KEY CLUSTERED CONSTRAINT PK_MyTestDates IDENTITY(1,1)
    , StartDate DATETIME
    , EndDate DATETIME
    , EventName NVARCHAR(255)
);

INSERT INTO MyTestDates (StartDate, EndDate, EventName) 
VALUES ('2012-01-27', '2012-01-30', 'Test Event 1');

INSERT INTO MyTestDates (StartDate, EndDate, EventName) 
VALUES ('2012-01-27', '2012-02-28', 'Test Event 2');

INSERT INTO MyTestDates (StartDate, EndDate, EventName) 
VALUES ('2011-01-01', '2012-03-22', 'Test Event 3');

DECLARE @FromDate DATETIME;
DECLARE @ToDate DATETIME;

SET @FromDate = '2012-01-01';
SET @ToDate = '2012-02-01'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, * 
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

SET @FromDate = '2012-01-28';
SET @ToDate = '2012-01-29'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, * 
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

SET @FromDate = '2010-01-01';
SET @ToDate = '2012-05-15'
SELECT @FromDate AS ReportFrom, @ToDate AS ReportTo, * 
FROM MyTestDates
WHERE StartDate <= @ToDate
    AND EndDate >= @FromDate;

DROP TABLE MyTestDates;

The three SELECT statements return all events in this example.

I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this.

The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee on the entire table. So as always, test with your own situation. This query also assumes that the MAX date is either unique or that equivalent rows would be acceptable.

USE AdventureWorks2012
GO
;
WITH Base AS (
   SELECT 
      TransactionHistory.*
      ,ProductVendor.BusinessEntityID
      ,MAX(CASE WHEN TransactionDate < '2008-08-01' THEN TransactionDate END) 
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS PreviousVendorTransaction
      ,COUNT(CASE WHEN TransactionDate >= '2008-08-01' THEN 1 END ) 
           OVER (PARTITION BY ProductVendor.BusinessEntityID) AS VendorAfterCutoff
   FROM
      Production.TransactionHistory
      -- Doesn't make the most sense, but I need a repeating relation
      INNER JOIN Purchasing.ProductVendor
         ON TransactionHistory.ProductID = ProductVendor.ProductID
),
Filtered AS (
   SELECT
      *
   FROM
      Base
   WHERE
      Base.TransactionDate >= '2008-08-01'
      OR (TransactionDate = PreviousVendorTransaction AND VendorAfterCutoff > 0)
)
SELECT DISTINCT
   TransactionID
   ,ProductID
   ,ReferenceOrderID
   ,ReferenceOrderLineID
   ,TransactionDate
   ,TransactionType
   ,Quantity
   ,ActualCost
   ,ModifiedDate
FROM
   Filtered

Edit:

Hmm, I think I may have to take back my comment on structuring it differently if there are indexes. The other suggestions that I have are probably fairly minor.

• Make sure the query is using the indexes you're expecting it to. Start and End date to build temp table, end date to drive the previous event loop.
• If the query to build the temp table is doing a lookup on the clustered index, it may be better to hold off and do that as part of the main query.
• Try using a cte instead of a temp table. I think that a cte might be more competitive with the way that the query is structured below.
• If you are returning a lot of events, it might be better to pull out the event table lookup to the main query to give the optimizer the option of doing a merge join.
• I don't see a way of optimizing the previous event lookup short of an indexed view.

Here's a query that combines a few of those ideas.

SELECT
    e.[EventID]
INTO #EventTemp
FROM
    [Events] AS e
WHERE
    ( e.[EventStart] >= @StartDate AND e.[EventStart] <= @EndDate )
    OR ( e.[EventEnd] >= @StartDate AND e.[EventEnd] <= @EndDate )

;
WITH PrevEvent AS (
    SELECT
        EmpPrevEvent.[EventID]
    FROM
    (
        SELECT DISTINCT
            ee.[EmployeeID]
        FROM
            #EventTemp
            INNER JOIN [EventEmployee] AS ee ON
                #EventTemp.[EventID] = ee.[EventID]
    ) AS Emp
    CROSS APPLY (
        SELECT TOP 1
            e.[EventID]
        FROM
            [Events] AS e
            INNER JOIN [EventEmployee] AS ee ON
                e.[EventID] = ee.[EventID]
        WHERE
            ee.[EmployeeID] = Emp.[EmployeeID]
            AND e.[EventEnd] < @StartDate
        ORDER BY 
            e.[EventEnd] DESC
    ) AS EmpPrevEvent
)
SELECT
    e.[EventID],
    e.[EventStart],
    e.[EventEnd],
    e.[EventTypeID]
FROM
    [Events] AS e
WHERE
    e.EventID IN (
        SELECT EventID
        FROM #EventTemp
        UNION
        SELECT EventID
        FROM PrevEvent
    )