有关时间相减问题

Question

create table test2 (idd int, ontime1 varchar(10),offtime1 varchar(10),ontime2 varchar(10),offtime2 varchar(10))
insert into test2 select 1,'8:00','11:00','19:00','23:30'
insert into test2 select 2,'8:00','12:40','20:00','02:40'
insert into test2 select 3,'8:20','12:40','20:00','01:20'
我想要两个时间offtime1-ontime1 ,offtime2-ontime2 相减,相减的结果小数部分>=30的算0.5的小时,<30的不算.
 得到下面结果
idd  Cha1  Cha2
1     3     4.5
2     4.5   6.5
3     4     5

Answer 1

create table test2 (idd int, ontime1 varchar(10),offtime1 varchar(10),ontime2 varchar(10),offtime2 varchar(10))
insert into test2 select 1,'08:00','11:00','19:00','23:30'
insert into test2 select 2,'08:00','12:40','20:00','02:40'
insert into test2 select 3,'08:20','12:40','20:00','01:20'select idd,
result1/60+case when result1%60>=30 then 0.5 else 0 end as result1,
result2/60+case when result2%60>=30 then 0.5 else 0 end as result2
from
(
select idd,
(case when datediff(minute,ontime1,offtime1)<0 then datediff(minute,ontime1,dateadd(day,1,offtime1)) else datediff(minute,ontime1,offtime1) end) as result1,
(case when datediff(minute,ontime2,offtime2)<0 then datediff(minute,ontime2,dateadd(day,1,offtime2)) else datediff(minute,ontime2,offtime2) end) as result2
from test2
)t
go
drop table test2
/*idd         result1        result2        
----------- -------------- -------------- 
1           3.0            4.5
2           4.5            6.5
3           4.0            5.0
*/

Answer 2

select idd,
((case when datediff(minute,ontime1,offtime1)<0 then datediff(minute,ontime1,dateadd(day,1,offtime1)) else datediff(minute,ontime1,offtime1) end)/30)*0.5,
((case when datediff(minute,ontime2,offtime2)<0 then datediff(minute,ontime2,dateadd(day,1,offtime2)) else datediff(minute,ontime2,offtime2) end)/30)*0.5 
from test2

Answer 3

多谢楼上两位的帮忙,我还有个小要求,我想查到 result1 + result2  的结果大过10的纪录该怎么做?
就是得到的结果为
idd
2

Answer 4

select * from 
(select idd,
((case when datediff(minute,ontime1,offtime1)<0 then datediff(minute,ontime1,dateadd(day,1,offtime1)) else datediff(minute,ontime1,offtime1) end)/30)*0.5 a,
((case when datediff(minute,ontime2,offtime2)<0 then datediff(minute,ontime2,dateadd(day,1,offtime2)) else datediff(minute,ontime2,offtime2) end)/30)*0.5 b 
from test2) A 
where  A.a + A.b > 10