日期:2014-05-17 浏览次数:20515 次
create table dot
(设备名称 varchar(10),开机时间 datetime,关机时间 datetime)
insert into dot
select '设备1','2013-11-01 9:00:00','2013-11-02 15:00:00' union all
select '设备2','2013-11-02 18:00:00','2013-11-04 10:00:00'
with t as
(select a.设备名称,a.开机时间,a.关机时间,
convert(varchar,dateadd(d,b.number,convert(varchar,a.开机时间,23)),23) '日'
from (select 设备名称,开机时间,关机时间,datediff(d,开机时间,关机时间) 'dd'
from dot) a
cross apply (select number from master.dbo.spt_values
where type='P' and number<=a.dd) b
)
select 设备名称,日,
case when datediff(d,开机时间,日)=0 then 24-datepart(hh,开机时间)
when datediff(d,关机时间,日)=0 then datepart(hh,关机时间)
else 24 end '历时'
from t
/*
设备名称 日 历时