start_time end_time
2013-09-11 17:26:02.382 2013-09-24 10:38:01.41
2013-09-18 17:02:40.444 2013-09-22 15:27:58.984
2013-09-18 08:21:32.036 2013-09-22 15:31:52.499
2013-09-13 16:28:29.832 2013-09-16 09:41:47.988
2013-09-09 10:59:59.835 2013-09-10 14:06:21.223
要求计算这两个列的时间差 但是要去除9月份的正常休假并且只计算正常工作时间（上午8:30--12:00 下午14:00--18:00）
计算结果如下：
start_time end_time diff_time（小时）
2013-09-11 17:26:02.382 2013-09-24 10:38:01.41 55.1
2013-09-18 17:02:40.444 2013-09-22 15:27:58.984 5.9
2013-09-18 08:21:32.036 2013-09-22 15:31:52.499 12.5
2013-09-13 16:28:29.832 2013-09-16 09:41:47.988 2.7
2013-09-09 10:59:59.835 2013-09-10 14:06:21.223 1.1
请各位大大帮忙看看这个时间差应该怎么计算 谢谢了
时间差
------解决方案--------------------
对的 周末是可以排除的。
------解决方案--------------------
use test
if object_id('tab') is not null
drop table tab
if object_id('holiday') is not null
drop table holiday
go
create table tab(start_time datetime,end_time datetime)
insert into tab
select '2013-09-11 17:26:02.382','2013-09-24 10:38:01.41' union
select '2013-09-18 17:02:40.444','2013-09-22 15:27:58.984' union
select '2013-09-18 08:21:32.036','2013-09-22 15:31:52.499' union
select '2013-09-13 16:28:29.832','2013-09-16 09:41:47.988' union
select '2013-09-09 10:59:59.835','2013-09-09 14:06:21.223'
create table holiday(h_date datetime)
insert into holiday
select '2013-09-01' union
select '2013-09-07' union
select '2013-09-08' union
select '2013-09-14'union
select '2013-09-15'union
select '2013-09-19'union
select '2013-09-20'union
select '2013-09-21'union
select '2013-09-29'
--;with sel as(select start_time oldstart,end_time oldend,case when exists(select 1 from holiday where
--h_date=convert(varchar(10),t.start_time,114)) then convert(varchar(10),t.start_time,121)+' 19:00' end as start_time,
--case when exists(select 1 from holiday where h_date=convert(varchar(10),t.end_time,114)) then
-- convert(varchar(10),t.end_time,121)+' 19:00' end as end_time
--from tab t)
select start_time,end_time ,diff_time=
case when datediff(d,start_time,end_time)=0
then (
case when convert(varchar(10),start_time,114)<='08:30' then 7.5
when convert(varchar(10),start_time,114) between '08:31' and '12:00' then 4+datediff(n,start_time,
convert(varchar(10),start_time,121)+' 12:00')/60.0
when convert(varchar(10),start_time,114) between '12:01' and '14:00' then 4
when convert(varchar(10),start_time,114) between '14:01' and '18:00' then
datediff(n,start_time,convert(varchar(10),start_time,121)+' 18:00')/60.0
else 0 end)-
(case when convert(varchar(10),end_time,114)<='08:30' then 7.5
when convert(varchar(10),end_time,114) between '08:31' and '12:00' then 4+datediff(n,end_time,
convert(varchar(10),end_time,121)+' 12:00')/60.0
when convert(varchar(10),end_time,114) between '12:01' and '14:00' then 4
when convert(varchar(10),end_time,114) between '14:01' and '18:00' then
datediff(n,end_time,convert(varchar(10),end_time,121)+' 18:00')/60.0
else 0 end)
when datediff(d,start_time,end_time)>0
then (case when convert(varchar(10),start_time,114)<='08:30' then 7.5