sql 有效时间计算

有效的时间是：星期一至星期五，早上8:30到11:45,下午14:00-17:30.
  开始和结束时间之会在这两个时间段里面。通过存储过程计算里面的时间差。还要排除周末的是时间

  如：开始时间是：2011-06-10 16:00 17：30-16：00 = `1：30
  结束时间是:2011-06-13 9:00 9：00-8：30=0：30
  但是除去了周末时间
  那么实际花费时间出去周末，工作时间仅为2小时。存储过程怎么写

你说什么？

SQL code

declare @dt1 datetime,@dt2 datetime
set @dt1='2011-06-10 16:00'
set @dt2='2011-06-13 9:00'
select sum(
case when convert(varchar(10),dateadd(d,number,@dt1),120)=convert(varchar(10),@dt1,120)
then
    case when convert(varchar(5),@dt1,8)>'14:00' then datediff(mi,@dt1,convert(varchar(10),@dt1,120)+' 17:30')
    else datediff(mi,@dt1,convert(varchar(10),@dt1,120)+' 11:45')+210 end
when convert(varchar(10),dateadd(d,number,@dt1),120)=convert(varchar(10),@dt2,120)
then
    case when convert(varchar(5),@dt2,8)<'11:45' then datediff(mi,convert(varchar(10),@dt2,120)+' 08:30',@dt2)
    else datediff(mi,convert(varchar(10),@dt2,120)+' 14:00',@dt2)+195 end
else 405 end
)
from master..spt_values 
where type='p' and dateadd(d,number-1,@dt1)<=@dt2 and 
datepart(dw,dateadd(d,number,@dt1))<>7 and datepart(dw,dateadd(d,number,@dt1))<>1
/*
-----------
120

(1 行受影响)

*/

注释一下吧:
SQL code

declare @dt1 datetime,@dt2 datetime
set @dt1='2011-06-10 16:00'
set @dt2='2011-06-13 9:00'
select sum(
case when convert(varchar(10),dateadd(d,number,@dt1),120)=convert(varchar(10),@dt1,120)
--如果是第一天
then
    case when convert(varchar(5),@dt1,8)>'14:00' then datediff(mi,@dt1,convert(varchar(10),@dt1,120)+' 17:30')
--如果是第一天下午,则算@dt1与下午下班时间之差,@dt1>14:00为下午
    else datediff(mi,@dt1,convert(varchar(10),@dt1,120)+' 11:45')+210 end
--如果是第一天上午,则算@dt1与上午下班时间之差,并加上下午的210分钟
when convert(varchar(10),dateadd(d,number,@dt1),120)=convert(varchar(10),@dt2,120)
--如果是最后一天
then
    case when convert(varchar(5),@dt2,8)<'11:45' then datediff(mi,convert(varchar(10),@dt2,120)+' 08:30',@dt2)
--如果是上午,则算上午上班时间与@dt2之差
    else datediff(mi,convert(varchar(10),@dt2,120)+' 14:00',@dt2)+195 end
--如果是下午,则算下午上班时间与@dt2之差,并加上上午的195分钟
else
--如果不是第一天和最后一天,则算405分钟
 405 end
)
from master..spt_values 
--获得时间范围
where type='p' and dateadd(d,number-1,@dt1)<=@dt2 and 
--扣除周六和周日
datepart(dw,dateadd(d,number,@dt1))<>7 and datepart(dw,dateadd(d,number,@dt1))<>1
/*
-----------
120

(1 行受影响)

*/