sql查询工作时间的问题
我想请教一个sql查询的问题,请大家帮帮忙!!

我们每天的工作时间是8点开始到19点结束,我有一个工作开始时间(starttime),一个结束时间(endtime),我想计算一次工作开始时间和结束时间之间经历了多少个小时的工作时间(一次工作可能经过几天)?(工作的开始和结束都可能在每天24小时中的任何一个小时,不考虑周末).

表
table_time

列
ID         starttime           endtime
--------------------------------------


------解决方案--------------------
/*
ID starttime endtime 结果
----------------------------------------------------- -----------------
1 2007-01-01 10:00:00 2007-01-01 14:00:00 4
2 2007-01-01 5:00:00 2007-01-01 9:00:00 4
3 2007-01-01 18:00:00 2007-01-02 10:00:00 3
4 2007-01-01 18:00:00 2007-01-03 10:00:00 14
5 2007-01-01 20:00:00 2007-01-01 21:00:00 0
6 2007-01-01 5:00:00 2007-01-01 6:00:00 0
*/

declare @ta table(ID int, starttime datetime, endtime datetime)
insert @ta select 1, '2007-01-01 10:00:00 ', '2007-01-01 14:00:00 '
union all select 2, '2007-01-01 5:00:00 ', '2007-01-01 9:00:00 '
union all select 3, '2007-01-01 18:00:00 ', '2007-01-02 10:00:00 '
union all select 4, '2007-01-01 18:00:00 ', '2007-01-03 10:00:00 '
union all select 5, '2007-01-01 20:00:00 ', '2007-01-01 21:00:00 '
union all select 6, '2007-01-01 5:00:00 ', '2007-01-01 6:00:00 '

select id,starttime,endtime,
[工作时间]=
case when convert(varchar(10),starttime,114) < '08:00 '
and convert(varchar(10),endtime,114) between '08:00 ' and '19:00 '
then
datediff(hour,starttime,
case when convert(varchar(10),endtime,114)> '19:00 '
then cast(convert(varchar(10),endtime,120)+ ' 19:00 ' as datetime)
when convert(varchar(10),endtime,114) < '08:00 '
then cast(convert(varchar(10),endtime,120)+ ' 08:00 ' as datetime)
else endtime end)
-datediff(day,starttime,endtime)*13
else
datediff(hour,case
when convert(varchar(10),starttime,114) < '08:00 ' then cast(convert(varchar(10),starttime,120)+ ' 08:00 ' as datetime)
when convert(varchar(10),starttime,114)> '19:00 ' then cast(convert(varchar(10),starttime,120)+ ' 19:00 ' as datetime)
else starttime end,
case when convert(varchar(10),endtime,114)> '19:00 '
then cast(convert(varchar(10),endtime,120)+ ' 19:00 ' as datetime)
when convert(varchar(10),endtime,114) < '08:00 '
then cast(convert(varchar(10),endtime,120)+ ' 08:00 ' as datetime)
else endtime end)
-datediff(day,starttime,endtime)*13 end
from @ta


(6 行受影响)
id starttime endtime 工作时间
----------- ----------------------- ----------------------- -----------
1 2007-01-01 10:00:00.000 2007-01-01 14:00:00.000 4
2 2007-01-01 05:00:00.000 2007-01-01 09:00:00.000 4
3 2007-01-01 18:00:00.000 2007-01-02 10:00:00.000 3
4 2007-01-01 18:00:00.000 2007-01-03 10:00:00.000 14
5 2007-01-01 20:00:00.000 2007-01-01 21:00:00.000 0
6 2007-01-01 05:00:00.000 2007-01-01 06:00:00.000 0

(6 行受影响)