请教一个关于两个时间表的问题
有两个表，一个是员工实际考勤表，一个特别时段表，要统计员工特别时段的工作时间
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

------解决方案--------------------

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),star