-1

Given a input date range, such as 11/1/2015 - 11/15/2015, what is the most efficient way to determine which events(CalendarEvents) are going on during that given date range.

event         eventStart     eventEnd
==================================
expo          10/25/2015    11/4/2015       //This should be selected.

concert       11/4/2014      11/5/2015      //This should be selected.

exhibit       11/15/2015     12/1/2015      //this should be selected.

display       10/26/2015    10/29/2015      //this should NOT be selected.

Linq or SQL server would be awesome. Basically given a date range, find events that overlap within that range.

I know I could "brute force" with a bit of code, just wondering if I'm missing something more elegant?

Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
Phillip Wells
  • 119
  • 1
  • 3

1 Answers1

3

You can use StartA <= EndB AND EndA >= StartA to get the overlapping dates:

DECLARE @startDate  DATE = '20151101',
        @endDate    DATE = '20151115'
SELECT * 
FROM CalendarEvents
WHERE
    eventStart <= @endDate
    AND eventEnd >= @startDate

SQL Fiddle

Reference

Community
  • 1
  • 1
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
  • Awesome!! Thanks so much! I really have to cut myself off after 12 hours of coding from now on ;) – Phillip Wells Oct 30 '15 at 01:11
  • Don't forget the case where you have an event that completely covers your window. (e.g. 10/31-11/16 completely covers 11/1-11/15) – mikurski Oct 30 '15 at 01:19