Sql-server – How to SELECT an ‘entry’ that spans adjacent months

sql serversql-server-2008

I have an Event table (used in a calendar application I am working on) that holds the following columns:

EventName
StartDate
EndDate

The events stored in said table get shown one month at a time, and that is working great, except that it does not return any events that span over into an adjacent month. For example, an event that runs from 2/25 - 3/05.

I will only get events for the current month, provided the EndDate is also within the month under consideration.

I know that this has to do with the statements I am writing. This is what I currently have:

  SELECT * 
    FROM Events 
   WHERE StartDate >= '2/1/2011' 
     AND EndDate   <  '3/1/2011' 
ORDER BY StartDate

The dates above are dynamic.

Best Answer

I keep the following bit of ASCII art around that I drew a few years back as reference for dealing with matching ranges, reformatted, as it was originally in POD; in your case, you're using the second one, which only matches example 3:

Interval Matching

 .........A============B......... Search Range
 ...x--y..|............|......... (never match)
 .........|............|...x--y.. (never match)
 ......x--|--y.........|......... (1)
 ......x--|------------|---y..... (2)
 .........|...x-----y..|......... (3)
 .........|..........x-|----y.... (4)

Intersection ... matches examples 1,2,3,4

A <= y && B >= x
(item fully contained by search range) ... matches example 3 only.

A <= x && B >= y
(item fully includes search range) ... matches example 2 only.

A >= x && B <= y

Related Solutions

Sql-server – Date alignment and pair matching extraction best done with TSQL or C#

Here's something I was fiddling with based on some work I had laying around. It doesn't handle the events clustered at a single time well. It could theoretically be helpful anyhow...:)

;WITH ordered_rows AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY EventTime) AS Row,
        Identifier, Type, EventTime, DiscoveredDate, FileId FROM #EventDataTemp
)
,filtered_rows AS
(
    SELECT Row, Identifier, Type, EventTime, DiscoveredDate, FileId, 
        CAST(CASE Type WHEN 'Went Off' THEN 1 ELSE NULL END AS INT) 
            AS OffEventRow
    FROM ordered_rows
    WHERE Row = 1
    UNION ALL
    SELECT o.Row, o.Identifier, o.Type, o.EventTime, o.DiscoveredDate, o.FileId,
        CAST(CASE WHEN o.Type = 'Went Off' AND f.Type = 'Went Off' 
            OR o.Type = 'Came On' THEN f.OffEventRow ELSE o.Row END AS INT)
    FROM ordered_rows o INNER JOIN filtered_rows f 
        ON o.Row = f.Row + 1 AND o.Identifier = f.Identifier
)
,on_events AS
(
   SELECT Identifier, OffEventRow, MAX(Row) AS OnRow
   FROM filtered_rows
   WHERE Type = 'Came On' AND OffEventRow IS NOT NULL
   GROUP BY OffEventRow, Identifier
)
SELECT f.Identifier, f.Type, f.EventTime, f.DiscoveredDate, f.FileId 
FROM filtered_rows f LEFT JOIN on_events o 
    ON f.Identifier = o.Identifier 
    AND f.Row = o.OnRow
WHERE f.Type = 'Went Off' AND f.Row = f.OffEventRow
OR f.Type = 'Came On' AND o.OnRow IS NOT NULL
ORDER BY f.Identifier, f.EventTime

Sql-server – Suddenly Slow Execution Plan for Stored Proc

This issue is called parameter sniffing.

Later versions of SQL Server give you more options in dealing with it such as OPTION (RECOMPILE) or OPTIMIZE FOR hints.

You might try declaring variables in the stored procedure, assigning the parameter values to the variables and using the variables in place of the parameters as it sounds as though most of the time you are getting a reasonably satisfactory plan.

Normally the most catastrophically bad plans are those compiled for parameters with very high selectivity but ran with parameters with low selectivity.

Assuming the plan generated is more robust with this approach and satisfactory for all parameter values then the advantage of this approach over that suggested by JNK is that it does not incur a compilation cost for every call.

The disadvantage is that for some executions run time might be greater than for a plan tailored specifically for those parameter values so it is a trade off of compile time vs execution time.

Best Answer

Interval Matching

Related Solutions

Sql-server – Date alignment and pair matching extraction best done with TSQL or C#

Sql-server – Suddenly Slow Execution Plan for Stored Proc

Related Question