查找出某一天没有工作解决思路

select ID,Name,WorkName,RBTime,RFTime from incident编号，员工名称，工作名称，开始时间，结束时间1      王八     写代码    2012-3-3  2012-3-122      六偢     跑龙套    2012-3-4  2012-3-203      鳖蛋     看片子    2012-3-3  2012-3-114      王八     跑龙套    2012-3-14  2012-3-185      六偢     看片子    2012-3-22  2012-3-246      鳖蛋     写代码    2012-3-13  2012-3-217      王八     看片子    2012-3-17  2012-3-318      六偢     写代码    2012-3-24  2012-3-319      鳖蛋     跑龙套    2012-3-22  2012-3-31

----创建测试数据表drop table #incidentcreate table  #incident(ID int,Name varchar(10),WorkName varchar(10),RBTime datetime,RFTime datetime) ----建立测试数据insert into #incident (ID,Name,WorkName,RBTime,RFTime)values ('1','王八','写代码','2012-3-3','2012-3-12')insert into #incident (ID,Name,WorkName,RBTime,RFTime)values ('2','六偢','跑龙套','2012-3-4','2012-3-20')insert into #incident (ID,Name,WorkName,RBTime,RFTime)values ('3','鳖蛋','看片子','2012-3-3','2012-3-11')insert into #incident (ID,Name,WorkName,RBTime,RFTime)values ('4','王八','跑龙套','2012-3-14','2012-3-18')insert into #incident (ID,Name,WorkName,RBTime,RFTime)values ('5','六偢','  看片子','2012-3-22','2012-3-24')insert into #incident (ID,Name,WorkName,RBTime,RFTime)values ('6','鳖蛋','写代码','2012-3-13 ','2012-3-21')insert into #incident (ID,Name,WorkName,RBTime,RFTime)values ('7','王八','看片子','2012-3-17 ',' 2012-3-31')insert into #incident (ID,Name,WorkName,RBTime,RFTime)values ('8','六偢','写代码','2012-3-24 ',' 2012-3-31')insert into #incident (ID,Name,WorkName,RBTime,RFTime)values ('9','鳖蛋','跑龙套','2012-3-22 ',' 2012-3-31')---select * from #incident----创建连续时间临时表drop table #dtcreate table #dt(c_dt datetime)delete  from #dt---定义日期段变量并赋值declare @dt_st datetimedeclare @dt_en datetimedeclare @dt_emp datetimeselect @dt_st = '20120301'select @dt_en = '20120331'set @dt_emp = @dt_st---建立连续日期数据while DATEDIFF(DAY,@dt_en,@dt_emp) <=0begin    insert into #dt(c_dt)         values (@dt_emp)    set @dt_emp = DATEADD(DAY,1,@dt_emp)end----从所有人员的数据比对出没有工作的时间select distinct all_work.Name,all_work.c_dt from (select * from #incident,#dt) all_work    where not exists    (select * from (select *    from #incident,#dt    where DATEDIFF(DAY,#incident.RBTime,#dt.c_dt)>=0    and DATEDIFF(DAY,#incident.RFTime,#dt.c_dt)<=0) do_work    where all_work.Name = do_work.Name    and all_work.c_dt = do_work.c_dt)    order by all_work.Name,all_work.c_dt    /***************此过程有一个缺点，就是如果某人一天都不工作，就不会在这个表出现所以建议all_work用一个用户表和连续时间表创建数据************/----创建用户表drop table #ucreate table #u(c_user varchar(10))---插入用户数据delete from #uinsert into #u(c_user)select distinct name from #incident---插入另一个人员 insert into #u(c_user)values('懒蛋')----从所有人员的数据比对出没有工作的时间select distinct all_work.c_user,all_work.c_dt from (select * from #u,#dt) all_work    where not exists    (select * from (select *    from #incident,#dt    where DATEDIFF(DAY,#incident.RBTime,#dt.c_dt)>=0    and DATEDIFF(DAY,#incident.RFTime,#dt.c_dt)<=0) do_work    where all_work.c_user = do_work.Name    and all_work.c_dt = do_work.c_dt)    order by all_work.c_user,all_work.c_dt
------解决方案--------------------
SQL code
select ID,Name,WorkName,RBTime,RFTime from incident编号，员工名称，工作名称，开始时间，结束时间1      王八     写代码    2012-3-3  2012-3-122      六偢     跑龙套    2012-3-4  2012-3-203      鳖蛋     看片子    2012-3-3  2012-3-114      王八     跑龙套    2012-3-14  2012-3-185      六偢     看片子    2012-3-22  2012-3-246      鳖蛋     写代码    2012-3-13  2012-3-217      王八     看片子    2012-3-17  2012-3-318      六偢     写代码    2012-3-24  2012-3-319      鳖蛋     跑龙套    2012-3-22  2012-3-31create table #t (id int, name nvarchar(10), start date, stop date)insert into #tselect 1,N'王八',cast('2012-3-3' as date),  cast('2012-3-12' as date) union allselect 2,N'六偢',cast('2012-3-4' as date),  cast('2012-3-20' as date) union allselect 3,N'鳖蛋',cast('2012-3-3' as date),  cast('2012-3-11' as date) union allselect 4,N'王八',cast('2012-3-14' as date),  cast('2012-3-18' as date) union allselect 5,N'六偢',cast('2012-3-22' as date),  cast('2012-3-24' as date) union allselect 6,N'鳖蛋',cast('2012-3-13' as date),  cast('2012-3-21' as date) union allselect 7,N'王八',cast('2012-3-17' as date),  cast('2012-3-31' as date) union allselect 8,N'六偢',cast('2012-3-24' as date),  cast('2012-3-31' as date) union allselect 9,N'鳖蛋',cast('2012-3-22' as date),  cast('2012-3-31' as date) -- create march calendar tabledeclare @d as datecreate table #c(d date)set @d = CAST('2012-3-1' as date)while(@d<CAST('2012-4-1' as date))begin  insert into #c values(@d)  set @d = DATEADD(d,1,@d)endselect d.name,d.d from #t t join #c c on c.d between t.start and t.stop right join(select distinct name, d from #t , #c) don c.d = d.d and t.name = d.namewhere id is null/* resultname    d六偢    2012-03-01六偢    2012-03-02六偢    2012-03-03六偢    2012-03-21王八    2012-03-01王八    2012-03-02王八    2012-03-13鳖蛋    2012-03-01鳖蛋    2012-03-02鳖蛋    2012-03-12*/