请教一条计算时间差的sql语句

Question

我的是：sql 2000 数据库有下面的表，如何动态新增一个字段，例如名字叫做 OT ，能够根据以下要求来显示数据要求：
如果判断date当天是星期天，根据当天打卡第一次和最后一次的时间差，如果小于等于4小时，那么ot字段显示0.5，如果大于4小时小于等于8小时，显示1，同时要求这个ot字段仅显示在date字段为星期天的最后一次打卡的记录上面name       date  time张三    2010-11-27 7:59:28
张三    2010-11-27 12:00:19
李四    2010-11-28 7:58:21
张三    2010-11-28 12:00:37
李四   2010-11-28 13:30:09
张三    2010-11-28 17:32:34
 

Answer 1

create table tb(name varchar(10),date varchar(10),time varchar(8))
insert into tb values('张三', '2010-11-27', '07:59:28')
insert into tb values('张三', '2010-11-27', '12:00:19')
insert into tb values('李四', '2010-11-28', '07:58:21')
insert into tb values('张三', '2010-11-28', '12:00:37')
insert into tb values('李四', '2010-11-28', '13:30:09')
insert into tb values('张三', '2010-11-28', '17:32:34')
goselect m.* , n.ot from tb m left join
(select name , date , max(time) time , ot = (case when datediff(hh,min(time) ,max(time)) <=4 then 0.5 else 1 end) from tb where datepart(weekday,date) = 1 group by name , date) n
on m.name = n.name and m.date = n.date and m.time = n.timedrop table tb/*
name       date       time     ot   
---------- ---------- -------- ---- 
张三         2010-11-27 07:59:28 NULL
张三         2010-11-27 12:00:19 NULL
李四         2010-11-28 07:58:21 NULL
张三         2010-11-28 12:00:37 NULL
李四         2010-11-28 13:30:09 1.0
张三         2010-11-28 17:32:34 1.0（所影响的行数为 6 行）
*/

Answer 2

create table t1
(
cname varchar(10),
ddate datetime,
dtime datetime
)
insert into t1
select '张三', '2010-11-27', '7:59:28' union all
select '张三', '2010-11-27', '12:00:19' union all
select '李四', '2010-11-28', '7:58:21' union all
select '张三', '2010-11-28', '12:00:37' union all
select '李四', '2010-11-28', '13:30:09' union all
select '张三', '2010-11-28', '17:32:34'set datefirst 1
;with abc as
(
select cname,convert(varchar,ddate,23) as ddate,convert(varchar,max(dtime),8) as dtime,(case when datediff(hh,min(dtime),max(dtime))<4 then 0.5 else 1 end) as ot from t1 group by cname,ddate 
having datepart(weekday,ddate)=7
)
select t1.cname,convert(varchar,t1.ddate,23),convert(varchar,t1.dtime,8),abc.ot from t1 left join abc
on t1.cname=abc.cname and t1.ddate=abc.ddate and t1.dtime=abc.dtime