请教一个和日期计算有关的sql写法

Question

写了一个SQL，用于查询每个员工的任务超时时间的，即结束时间减开始时间>24小时的就属于超时，此版本没有考虑员工的休息日，语句如下：select 姓名,(case when DateDiff(hh, 开始时间,结束时间)>24 then DateDiff(hh,开始时间,结束时间)-24 else 0 end)as 超时小时数
FROM Table1
WHERE 开始时间>='2001-01-01' and 开始时间<='2011-12-31'现在想优化一下，首先我单独建了一个表Table2，用于存放该公司的工作日/休息日：
Table2（共2个字段）：日期、是否上班（是、否）现在希望把原sql语句中计算任务超时时间时要剔除员工的休息日，只计算员工工作日的时间。（提示：如果某任务的开始时间是在非工作日提出，则也从该日期以后第一个工作日开始计算）。请高手帮忙看看，谢谢。

Answer 1

create table tb(ygid int,startdate datetime,enddate datetime)
create table t1(dt datetime,flg int)
insert into tb select 1,'2011-06-22 20:20:00','2011-06-23 23:30:00' --起点在休息日
insert into tb select 2,'2011-06-21 21:00:00','2011-06-23 10:00:00' --跨休息日
insert into tb select 3,'2011-06-21 10:00:00','2011-06-21 20:00:00' --终点在休息日
insert into tb select 4,'2011-06-21 10:00:00','2011-06-22 10:00:00' --不在休息日
insert into tb select 4,'2011-06-22 10:00:00','2011-06-22 20:00:00' --全在休息日
--设置上面五种情况
insert into t1 select '2011-06-22',0
insert into t1 select '2011-06-15',0
select timeminute=datediff(mi,startdate,enddate)-
(case when exists(select 1 from t1 where flg=0 and dt>a.startdate and dateadd(d,1,dt)<a.enddate) then 1440
when exists(select 1 from t1 where flg=0 and dt>a.startdate and dt<a.enddate) then
(select datediff(mi,dt,a.enddate) from t1 where flg=0 and dt>a.startdate and dt<a.enddate)
when exists(select 1 from t1 where flg=0 and dateadd(d,1,dt)>a.startdate and dateadd(d,1,dt)<a.enddate) then
(select datediff(mi,a.startdate,dateadd(d,1,dt))from t1 where flg=0 and dateadd(d,1,dt)>a.startdate and dateadd(d,1,dt)<a.enddate)
when exists(select 1 from t1 where flg=0 and dt<a.startdate and dateadd(d,1,dt)>a.startdate) then
datediff(mi,a.startdate,a.enddate)
else 0
end)
from tb a
/*
timeminute
-----------
1410
780
600
840
0(5 行受影响)*/
go
drop table tb,t1

Answer 2

create table tb(ygid int,startdate datetime,enddate datetime)
create table t1(dt datetime,flg int)
insert into tb select 1,'2011-06-22 20:20:00','2011-06-23 23:30:00' --起点在休息日
insert into tb select 2,'2011-06-21 21:00:00','2011-06-23 10:00:00' --跨休息日
insert into tb select 3,'2011-06-21 10:00:00','2011-06-21 20:00:00' --终点在休息日
insert into tb select 4,'2011-06-21 10:00:00','2011-06-22 10:00:00' --不在休息日
insert into tb select 4,'2011-06-22 10:00:00','2011-06-22 20:00:00' --全在休息日
--设置上面五种情况
insert into t1 select '2011-06-22',0
insert into t1 select '2011-06-15',0
select timeminute=datediff(mi,startdate,enddate)-
(case 
--跨休息日,扣1440分钟
when exists(select 1 from t1 where flg=0 and dt>a.startdate and dateadd(d,1,dt)<a.enddate) then 1440
--终点在休息日,扣休息日0:0:0-enddate
when exists(select 1 from t1 where flg=0 and dt>a.startdate and dt<a.enddate) then
(select datediff(mi,dt,a.enddate) from t1 where flg=0 and dt>a.startdate and dt<a.enddate)
--起点在休息日,扣startdate-休息日后一天0:0:0
when exists(select 1 from t1 where flg=0 and dateadd(d,1,dt)>a.startdate and dateadd(d,1,dt)<a.enddate) then
(select datediff(mi,a.startdate,dateadd(d,1,dt))from t1 where flg=0 and dateadd(d,1,dt)>a.startdate and dateadd(d,1,dt)<a.enddate)
--全在休息日,扣全部时间
when exists(select 1 from t1 where flg=0 and dt<a.startdate and dateadd(d,1,dt)>a.startdate) then
datediff(mi,a.startdate,a.enddate)
--不在休息日
else 0
end)
from tb a
/*
timeminute
-----------
1410
780
600
840
0(5 行受影响)*/
go
drop table tb,t1

Answer 3

两个或多个休息日(可连续或不连续):
create table tb(ygid int,startdate datetime,enddate datetime)
create table t1(dt datetime,flg int)
insert into tb select 1,'2011-06-22 20:20:00','2011-06-23 23:30:00' --起点在休息日
insert into tb select 2,'2011-06-21 21:00:00','2011-06-23 10:00:00' --跨休息日
insert into tb select 3,'2011-06-20 10:00:00','2011-06-21 20:00:00' --在一个休息日
insert into tb select 4,'2011-06-21 10:00:00','2011-06-22 10:00:00' --全在两个休息日
insert into tb select 5,'2011-06-22 10:00:00','2011-06-22 20:00:00' --全在休息日
--设置上面五种情况
insert into t1 select '2011-06-21',0
insert into t1 select '2011-06-22',0
select ygid,timeminute-d timerminute from (
select a.ygid,timeminute=datediff(mi,startdate,enddate),
sum(case 
--跨休息日,扣1440分钟
when b.dt>a.startdate and dateadd(d,1,b.dt)<a.enddate then 1440
--终点在休息日,扣休息日0:0:0-enddate
when b.dt>a.startdate and b.dt<a.enddate then
   datediff(mi,b.dt,a.enddate)
--起点在休息日,扣startdate-休息日后一天0:0:0
when dateadd(d,1,b.dt)>a.startdate and dateadd(d,1,b.dt)<a.enddate then
    datediff(mi,a.startdate,dateadd(d,1,b.dt))
--全在休息日,扣全部时间
when b.dt<a.startdate and dateadd(d,1,b.dt)>a.startdate then
    datediff(mi,a.startdate,a.enddate)
--不在休息日
else 0
end)as d
from tb a,t1 b where b.flg=0 group by a.ygid,datediff(mi,a.startdate,a.enddate)
)t order by ygid
/*
ygid        timerminute
----------- -----------
1           1410
2           600
3           840
4           0
5           0(5 行受影响)*/
go
drop table tb,t1