现在有一下三个时间段:
开始 结束
2011-6-30 09:00:00 2011-6-30 10:00:00
2011-6-30 09:50:00 2011-6-30 12:00:00
2011-6-30 13:00:00 2011-6-30 18:00:00如何计算他们之间所包括的总时间?谢谢各位了先。
开始 结束
2011-6-30 09:00:00 2011-6-30 10:00:00
2011-6-30 09:50:00 2011-6-30 12:00:00
2011-6-30 13:00:00 2011-6-30 18:00:00如何计算他们之间所包括的总时间?谢谢各位了先。
--mi分钟 hh小时
简单点问就是如何去交叉取多个时间段之和。
select datediff(ss,min(开始),max(结束)) 总秒数 from tb
--ss秒 mi分钟 hh小时
create table A(id int identity(1,1) not null primary key,start_time datetime,end_time datetime)insert A(start_time,end_time)
select '2011-6-30 09:00:00','2011-6-30 10:00:00'
union all
select '2011-6-30 09:50:00','2011-6-30 12:00:00'
union all
select '2011-6-30 13:00:00','2011-6-30 18:00:00'select sum(case when b2.end_time>b1.start_time then datediff(ss,b2.end_time,b1.start_time)+
datediff(ss,b1.start_time,b1.end_time)
else datediff(ss,b2.start_time,b2.start_time)+
datediff(ss,b1.start_time,b1.end_time)
end
)
from A b1,A b2 where b1.id=b2.id+1
Create table tb(bdate datetime,edate datetime)
insert into tb select '2011-6-30 09:00:00','2011-6-30 10:00:00'
insert into tb select '2011-6-30 09:50:00','2011-6-30 12:00:00'
insert into tb select '2011-6-30 13:00:00','2011-6-30 18:00:00'select sum(datediff(minute,isnull(t2.edate,t1.bdate),t1.edate))
from tb t1 left join tb t2 on t1.bdate>t2.bdate and t1.bdate<t2.edate-- 480
create table #tb(startDate datetime,EndDate datetime)
insert #tb
select '2011-6-30 09:00:00','2011-6-30 10:00:00' union all
select '2011-6-30 09:50:00','2011-6-30 12:00:00' union all
select '2011-6-30 13:00:00','2011-6-30 18:00:00' union all
select '2011-6-30 17:30:00','2011-6-30 19:00:00' union all
select '2011-6-30 20:00:00','2011-6-30 21:00:00';with tempA as (select row_number()over(order by startdate) as num,* from #tb)
, tempB as(select t1.startdate,case when t1.enddate>isnull(t2.startdate,'9999-12-31')
then t2.startdate else t1.enddate end as enddate
from tempA as t1 left join tempA as t2 on t1.num=t2.num-1)
select sum(datediff(mi,startdate,enddate)) as sumTime from tempB
select sum(datediff(mi,t1.startdate,isnull(t2.startdate,t1.enddate))) as sumTime from #tb as t1
left join #tb as t2 on t1.enddate>t2.startdate and t1.enddate<t2.enddate这样也行..抄#7的..学习了..