Linq2SQL or SQL: Find dates that don't have events

Question

What would be the best way to find dates that dont have events in a given time interval given that different events can overlap, span multiple days, start before the interval, and end after the interval.

ie:

event   start        end
e1      01/01/2009   02/01/2009
e2      01/15/2009   01/31/2009
e3      08/15/2008   01/16/2009
e4      02/03/2009   02/15/2009

with that data we can see that there is no event on 2/2/2009.

Answer 1

While this doesn't limit by a time interval, this will give you all available gaps within your events:

declare @temp table (evt varchar(10), start datetime, [end] datetime)

insert into @temp values('e1', '1/1/2009', '2/1/2009')
insert into @temp values('e2', '1/15/2009', '1/31/2009')
insert into @temp values('e3', '8/15/2008', '1/16/2009')
insert into @temp values('e4', '2/3/2009', '2/15/2009');

with NextEvent as (select
    t.evt,
    tafter.evt nextEvt, 
    tafter.start start,
    tafter.[end] [end],
    ROW_NUMBER() over (order by t.evt, tafter.start) - RANK() over (order by t.evt) as number

from @temp t

left join @temp tafter on tafter.[end] >= t.[end] and tafter.evt <> t.evt)

select
    t.evt,
    t.start,
    t.[end],
    ne.nextEvt [next],
    ne.start,
    ne.[end]

from @temp t

left join NextEvent ne on ne.evt = t.evt and ne.number = 0

where ne.start > t.[end]

Answer 2

I'm not sure I understand your question. For a given date, you want to see the number of rows (events) returned??

So for the 2/1/09 you should see 1, for the 2/2/09 you should see 0??? SQL:

declare @temp table (evt varchar(10), start datetime, [end] datetime)

insert into @temp values('e1', '1/1/2009', '2/1/2009')
insert into @temp values('e2', '1/15/2009', '1/31/2009')
insert into @temp values('e3', '8/15/2008', '1/16/2009')
insert into @temp values('e4', '2/3/2009', '2/15/2009');


select * from @temp where start < '2/1/2009' and [end] >= '2/1/2009'
select * from @temp where start < '2/2/2009' and [end] >= '2/2/2009'

C# / linq:

public class Event
        {
            public string eventID;
            public DateTime start;
            public DateTime end;
        }

        static void Main(string[] args)
        {
            IList<Event> events = new List<Event>();
            events.Add(new Event { eventID = "e1", start = new DateTime(2009, 1, 1), end = new DateTime(2009, 2, 1) });
            events.Add(new Event { eventID = "e2", start = new DateTime(2009, 1, 15), end = new DateTime(2009, 1, 31) });
            events.Add(new Event { eventID = "e3", start = new DateTime(2008, 8, 15), end = new DateTime(2009, 1, 16) });
            events.Add(new Event { eventID = "e4", start = new DateTime(2009, 2, 3), end = new DateTime(2009, 2, 15) });

            DateTime eventDate = new DateTime(2009, 2, 1);
            var available = events.Where(e => e.start.CompareTo(eventDate) < 1 && e.end.CompareTo(eventDate) > -1);
            Console.WriteLine(available.Count());

            eventDate = new DateTime(2009, 2, 2);
            available = events.Where(e => e.start.CompareTo(eventDate) < 1 && e.end.CompareTo(eventDate) > -1);
            Console.WriteLine(available.Count());

            eventDate = new DateTime(2009, 1, 16);
            available = events.Where(e => e.start.CompareTo(eventDate) < 1 && e.end.CompareTo(eventDate) > -1);
            Console.WriteLine(available.Count());
            Console.ReadLine();
        }

EDIT: It's not pretty, but this SQL will give you the result you have asked for:

declare @temp table (evt varchar(10), start datetime, [end] datetime)
declare @result table (available datetime)

insert into @temp values('e1', '1/1/2009', '2/1/2009')
insert into @temp values('e2', '1/15/2009', '1/31/2009')
insert into @temp values('e3', '8/15/2008', '1/16/2009')
insert into @temp values('e4', '2/3/2009', '2/15/2009');

declare @start datetime
declare @end datetime
set @start = '1/1/2009'
set @end = '2/16/2009'

while @start < dateadd(day, 1, @end)
begin
    declare @rowCount int
    select @rowCount = count(*) from @temp where start <= @start and [end] >= @start
    if @rowCount = 0
     insert into @result values(@start)

    set @start = dateadd(day, 1, @start)
end
select * from @result

Answer 3

This type of query is easy if you use a date or calendar type table. These are utility tables that are used so that you have a pre-populated table with fields that would be tedious to calulate in queries (for instance, IsWeekDay, IsHoliday, FiscalMonth). Below I used a very simple calendar table. The query to get the dates without events ends up being very straightforward.

This was created on SQL Server 2005

-- Create the #Calendar table
Create table #Calendar (CalendarDate datetime)
Set nocount on
Declare @Date smalldatetime
Set @Date = '1/1/2000'
While @Date  < '1/1/2015'
Begin
   Insert #Calendar select @Date
   Set @Date = dateadd(dd, 1, @Date)   
End

-- Create the #Event table
Create Table #Event (EventName varchar(10), StartDate datetime, EndDate datetime)

Insert Into #Event 
Select 'e1', '1/1/2009', '2/1/2009'
Union Select 'e2', '1/15/2009', '1/31/2009'
Union Select 'e3', '8/15/2008', '1/16/2009'
Union Select 'e4', '2/3/2009', '2/15/2009'

-- Return all the dates that do not have events
Select #Calendar.CalendarDate 
From #Calendar 
Left Join #Event
   on #Calendar.CalendarDate between #Event.StartDate and #Event.EndDate
Where 
   #Event.StartDate is null
   and CalendarDate between 
      (Select min(StartDate) from #Event) 
      and 
      (Select max(EndDate) from #Event)