请教一个关于两个时间表的问题
有两个表，一个是员工实际考勤表，一个特别时段表，要统计员工特别时段的工作时间
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)
想得到如下结果
A00330012后夜班90
A00330011前夜班300
[解决办法]
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 <> 0
select * from v_wordtime
A0033001前夜班2007-09-11 20:00:00.0002007-09-12 02:00:00.00009/11/2007 22:00:0009/12/2007 01:00:00
A0033001前夜班2007-09-12 23:00:00.0002007-09-13 01:30:00.00009/12/2007 22:00:0009/13/2007 01:00:00
A0033001后夜班2007-09-11 20:00:00.0002007-09-12 02:00:00.00009/12/2007 01:00:0009/12/2007 02:00:00
A0033001后夜班2007-09-12 23:00:00.0002007-09-13 01:30:00.00009/13/2007 01:00:0009/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
结果：
A0033001后夜班290
A0033001前夜班2300
为了简写SQL语句，使用了视图，LZ可以还原回去，但是会相当难看
