设定的两个时间段如下:
2011-08-01 09:00:00 2011-08-01 12:00:00
2011-08-01 13:00:00 2011-08-01 18:00:00目标时间段:
2011-08-01 08:00:00 2011-08-01 15:00:00期望结果为
在时间段【2011-08-01 08:00:00 2011-08-01 15:00:00】中有5个小时在设定的时间段内。==================================================================
2011-08-01 09:00:00 2011-08-01 12:00:00
2011-08-01 00:00:00 2011-08-01 00:00:00目标时间段:
2011-08-01 12:00:00 2011-08-01 15:00:00期望结果为
在时间段【2011-08-01 12:00:00 2011-08-01 15:00:00】中有0个小时在设定的时间段内。
请问这个查询如何实现?谢谢各位了先。
2011-08-01 09:00:00 2011-08-01 12:00:00
2011-08-01 13:00:00 2011-08-01 18:00:00目标时间段:
2011-08-01 08:00:00 2011-08-01 15:00:00期望结果为
在时间段【2011-08-01 08:00:00 2011-08-01 15:00:00】中有5个小时在设定的时间段内。==================================================================
2011-08-01 09:00:00 2011-08-01 12:00:00
2011-08-01 00:00:00 2011-08-01 00:00:00目标时间段:
2011-08-01 12:00:00 2011-08-01 15:00:00期望结果为
在时间段【2011-08-01 12:00:00 2011-08-01 15:00:00】中有0个小时在设定的时间段内。
请问这个查询如何实现?谢谢各位了先。
insert into tb select '2011-08-01 09:00:00','2011-08-01 12:00:00'
insert into tb select '2011-08-01 13:00:00','2011-08-01 18:00:00'
go
declare @dt1 datetime,@dt2 datetime
set @dt1='2011-08-01 08:00:00'
set @dt2='2011-08-01 15:00:00' --@dt2>@dt1
select sum(case when @dt2<dt1 or @dt1>dt2 then 0
when @dt1<dt1 and @dt2>dt2 then datediff(mi,dt1,dt2)
when @dt1<dt1 and @dt2>dt1 then datediff(mi,dt1,@dt2)
when @dt2>dt2 and @dt1<dt2 then datediff(mi,@dt1,dt2) end)/60
from tb
/*
-----------
5(1 行受影响)*/
go
drop table tb
能得出新的目标时间段吗?
比如:
2011-08-01 09:00:00 2011-08-01 12:00:00
insert into tb select '2011-08-01 09:00:00','2011-08-01 12:00:00'
insert into tb select '2011-08-01 13:00:00','2011-08-01 18:00:00'
--如果设定时间段不同,则把上面的改掉.
go
declare @dt1 datetime,@dt2 datetime
set @dt1='2011-08-01 09:00:00'
set @dt2='2011-08-01 12:00:00' --把目标时间段赋给这两个参数,注意@dt2>@dt1
select sum(case when @dt2<=dt1 or @dt1>=dt2 then 0
when @dt1<=dt1 and @dt2>=dt2 then datediff(mi,dt1,dt2)
when @dt1<=dt1 and @dt2>=dt1 then datediff(mi,dt1,@dt2)
when @dt2>=dt2 and @dt1<=dt2 then datediff(mi,@dt1,dt2) end)/60
from tb
--前面程序的<,>都改成<=,>=
/*
-----------
3(1 行受影响)*/
go
drop table tb
create table tb(dt1 datetime,dt2 datetime)
insert into tb select '2011-08-01 09:00:00','2011-08-01 12:00:00'
insert into tb select '2011-08-01 13:00:00','2011-08-01 18:00:00'
--如果设定时间段不同,则把上面的改掉.
go
declare @dt1 datetime,@dt2 datetime
set @dt1='2011-08-01 09:00:00'
set @dt2='2011-08-01 12:00:00' --把目标时间段赋给这两个参数,注意@dt2>@dt1
select (case when dt1<@dt1 then @dt1 else dt1 end)dt1,(case when dt2>@dt2 then @dt2 else dt2 end)dt2 from(
select min(dt1)dt1,max(dt2)dt2 from tb)t
/*
dt1 dt2
----------------------- -----------------------
2011-08-01 09:00:00.000 2011-08-01 12:00:00.000(1 行受影响)*/
go
drop table tb
要加上这个条件才行