查找出某一天没有工作
表名incident：编号，员工名称，工作名称，开始时间，结束时间。
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
[解决办法]
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*/
[解决办法]
GO/****** Object: Table [dbo].[incident] Script Date: 04/01/2012 13:43:49 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[incident]( [id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](20) NULL, [WorkName] [varchar](50) NULL, [RBTime] datetime NULL, [RFTime] datetime NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGOinsert into incident values( '王八' , '写代码' , '2012-4-3' , '2012-4-12')insert into incident values( '六偢' , '跑龙套' , '2012-4-4' , '2012-4-20')insert into incident values( '鳖蛋' , '看片子' , '2012-3-3' ,'2012-3-11')insert into incident values( '王八' , '跑龙套' , '2012-4-14' , '2012-4-18')insert into incident values( '六偢' , '看片子' , '2012-4-22' , '2012-4-24')insert into incident values( '鳖蛋' , '写代码' , '2012-3-13' , '2012-3-21')insert into incident values( '王八' , '看片子' , '2012-4-17' , '2012-4-30')insert into incident values( '六偢' , '写代码' , '2012-4-24' , '2012-4-30') insert into incident values( '鳖蛋' , '跑龙套' , '2012-3-22' , '2012-3-31')select Name, DD FROM (select *,rn=ROW_NUMBER()over(partition by Name,dd order by dd) from (select n.*,DATEPART(DD,n.Times)as dd from (select i.Name,i.WorkName,DATEADD(DD,number-1,i.RBTime)as Times from incident i , master..spt_values s where s.type='p' and s.number between 1 and datediff(DD,RBTime,RFTime)+1 )m full join ( select i.Name,i.WorkName,DATEADD(DD,number-1,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))as Times from incident i , master..spt_values s where s.type='p' and s.number between 1 and day(dateadd(month,1,getdate()) - day(getdate())) )n on m.Name=n.Name and m.Times=n.Times and m.WorkName=n.WorkName where m.Name is null )a )F where rn=3 --SELECT * FROM incident drop table incident(1 行受影响)(1 行受影响)Name DD-------------------- -----------鳖蛋 1鳖蛋 2鳖蛋 3鳖蛋 4鳖蛋 5鳖蛋 6鳖蛋 7鳖蛋 8鳖蛋 9鳖蛋 10鳖蛋 11鳖蛋 12鳖蛋 13鳖蛋 14鳖蛋 15鳖蛋 16鳖蛋 17鳖蛋 18鳖蛋 19鳖蛋 20鳖蛋 21鳖蛋 22鳖蛋 23鳖蛋 24鳖蛋 25鳖蛋 26鳖蛋 27鳖蛋 28鳖蛋 29鳖蛋 30六偢 1六偢 2六偢 3六偢 21王八 1王八 2王八 13(37 行受影响)
[解决办法]
10 楼的下面 BETWEEN AND 很好使，但是看不明白。谁给解释一下。select 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
[解决办法]
明了。
看来 还需修行呀。
[解决办法]
那你可以把null替换成当前时间，这样按照相应算法出来的结果应该是一样
isnull(结束时间,getdate()) as 结束时间