请教一个关于两个时间表的问题

Question

有两个表，一个是员工实际考勤表，一个特别时段表，要统计员工特别时段的工作时间
CREATE TABLE [dbo].[SpecialTimeSet](
[TimeID] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[SpecialTimeDesc] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[StartTime] [datetime] NOT NULL,
[EndTime] [datetime] NOT NULL,
[CreateUser] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[CreateTime] [datetime] NULL,
[ModifyUser] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[ModifyTime] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[AttendFact](
[ShopNo] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[EmployeeNo] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
[WorkDate] [datetime] NULL,
[FactTimeIn] [datetime] NULL,
[FactTimeOut] [datetime] NULL,
[WorkTime] [int] NULL
) ON [PRIMARY]
insert SpecialTimeset values(
1,'前夜班','1899-12-30 22:00:00.000','1899-12-30 01:00:00.000','ADMIN','2007-08-29 16:23:52.000','ADMIN','2007-09-07 09:37:34.000')
insert SpecialTimeset values(
2,'后夜班','1899-12-30 01:00:00.000','1899-12-30 02:00:00.000','ADMIN','2007-08-29 16:25:14.000','ADMIN','2007-08-29 16:25:14.000')
以下为测试数据
insert AttendFact values(
'A003','3001','2007-09-11 00:00:00.000','2007-09-11 10:00:00.000','2007-09-11 13:00:00.000',180)
insert AttendFact values(
'A003','3001','2007-09-11 00:00:00.000','2007-09-11 20:00:00.000','2007-09-12 02:00:00.000',360)
insert AttendFact values(
'A003','3001','2007-09-12 00:00:00.000','2007-09-12 10:00:00.000','2007-09-12 13:00:00.000',180)
insert AttendFact values(
'A003','3001','2007-09-12 00:00:00.000','2007-09-12 23:00:00.000','2007-09-13 01:30:00.000',360)想得到如下结果
A003 3001 2 后夜班 90
A003 3001 1 前夜班 300

Answer 1

--后夜班不跨天,我帮你算出来了.
--前夜班跨天,太麻烦了.select ShopNo,EmployeeNo,count(*) cnt , t = '后夜班' , sum(datediff(minute,cast(convert(varchar(10),FactTimeOut,120) + ' ' + convert(varchar(10),starttime,114) as datetime),FactTimeOut)) 分钟
from SpecialTimeSet,AttendFact
where SpecialTimeDesc = '后夜班' and DATEPART(Hour,FactTimeOut) >= DATEPART(Hour,starttime) and DATEPART(Hour,FactTimeOut) <= DATEPART(Hour,endTime)
group by ShopNo,EmployeeNodrop table SpecialTimeSet,AttendFact
/*
ShopNo               EmployeeNo           cnt         t      分钟          
-------------------- -------------------- ----------- ------ ----------- 
A003                 3001                 2           后夜班    90（所影响的行数为 1 行）
*/

Answer 2

dawugui(潇洒老乌龟) 
多谢
跨天的话给思路，怎么处理。还有，我可能有多个特殊时段，两个表直接连，不行么？

Answer 3

create view v_wordtime
as select Shopno,employeeno,specialtimedesc,facttimein,facttimeout--,starttime,endtime
,case when ltrim(rtrim(specialtimedesc)) = '前夜班' then convert(varchar(10),facttimein,101) + ' ' + convert(varchar(10),starttime,8)
else convert(varchar(10),dateadd(day,1,facttimein),101) +' '+ convert(varchar(10),starttime,8)
end as starttime
,case when ltrim(rtrim(specialtimedesc)) = '前夜班' then convert(varchar(10),dateadd(day,1,facttimein),101) + ' ' + convert(varchar(10),endtime,8)
else convert(varchar(10),dateadd(day,1,facttimein),101) +' '+ convert(varchar(10),endtime,8)
end as endtime

from AttendFact a , SpecialTimeSet b
where case when ltrim(rtrim(specialtimedesc)) = '前夜班' then case when convert(varchar(10),facttimein,101) + ' ' + convert(varchar(10),starttime,8) between facttimein and facttimeout
or facttimein >= convert(varchar(10),facttimein,101) + ' ' + convert(varchar(10),starttime,8) then 1 else 0 end

else case when convert(varchar(10),dateadd(day,1,facttimein),101) +' '+ convert(varchar(10),starttime,8) between facttimein and facttimeout
or facttimein >= convert(varchar(10),dateadd(day,1,facttimein),101) +' '+ convert(varchar(10),starttime,8) then 1 else 0 end
end <> 0select * from v_wordtime
A003 3001 前夜班 2007-09-11 20:00:00.000 2007-09-12 02:00:00.000 09/11/2007 22:00:00 09/12/2007 01:00:00
A003 3001 前夜班 2007-09-12 23:00:00.000 2007-09-13 01:30:00.000 09/12/2007 22:00:00 09/13/2007 01:00:00
A003 3001 后夜班 2007-09-11 20:00:00.000 2007-09-12 02:00:00.000 09/12/2007 01:00:00 09/12/2007 02:00:00
A003 3001 后夜班 2007-09-12 23:00:00.000 2007-09-13 01:30:00.000 09/13/2007 01:00:00 09/13/2007 02:00:00--SQL语句
select Shopno,employeeno,specialtimedesc
,count(specialtimedesc) as [次数]
,sum(case when facttimeout between starttime and endtime and starttime >= facttimein then datepart(hour,facttimeout - starttime)*60 + datepart(minute,facttimeout - starttime)
      when facttimeout between starttime and endtime and starttime < facttimein then datepart(hour,facttimeout - facttimein)*60 + datepart(minute,facttimeout - facttimein)
      when facttimein between starttime and endtime and facttimeout > endtime then datepart(hour,endtime - facttimein )*60 + datepart(minute,endtime - facttimein )*60
else datepart(hour,convert(datetime,endtime) - starttime)*60 + datepart(minute,convert(datetime,endtime) - starttime)
end) as [分钟]from v_wordtime
group by Shopno,employeeno,specialtimedesc
结果：
A003 3001 后夜班 2 90
A003 3001 前夜班 2 300为了简写SQL语句，使用了视图，LZ可以还原回去，但是会相当难看

Answer 4

LPQ8306586() ( ) 信誉：100  2007-09-07 15:55:33  得分: 0  
 
 
   为了这20分，一天的时间几乎没了，LZ呀
  
 
--------
呵呵，你還真有趣

Answer 5

再有LZ的前夜班的结果是有误的吧？
上班时间同时跨越了两个特殊上班时间，所以两个特殊的上班时间应该各算一次才合适吧！
不然好像没法处理噢。
比如：
insert AttendFact values(
'A003','3001','2007-09-11 00:00:00.000','2007-09-11 20:00:00.000','2007-09-12 02:00:00.000',360)
insert AttendFact values(
'A003','3001','2007-09-12 00:00:00.000','2007-09-12 23:00:00.000','2007-09-13 01:30:00.000',360)
这两条记录都跨越了两个特殊上班时间，所以同时计算了两次，所以才有上面的2，2结果！