linq2sql或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');

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]

我不确定我理解你的问题。对于给定日期,要查看返回的行(事件)数吗??

所以对于2009年2月1日,你应该看到1,对于2009年2月2日,你应该看到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();
        }

编辑: 它不漂亮,但是这个SQL将提供您要求的结果:

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

如果使用日期或日历类型表,则此类型的查询很容易。这些是使用的实用程序表,这样您就有了一个预填充的表,其中的字段在查询中计算起来很繁琐(例如,isweekday、isholiday、fiscalmonth)。下面我使用了一个非常简单的日历表。获取没有事件的日期的查询结果非常简单。

这是在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)