处理时间段SQL
请大家帮我想想下面的这个怎么处理
原始表
设备名称 开机时间 关机时间
设备1 2013-11-01 9：00：00 2013-11-02 15：00：00
设备2 2013-11-02 18：00：00 2013-11-04 10：00：00
期望得到的结果是
设备名称 日 历时
设备1 2013-11-01 15
设备1 2013-11-02 15
设备2 2013-11-02 6
设备2 2013-11-03 24
设备2 2013-11-04 10
历时的单位是小时，也就是要统计出每个设备每一天的所有运行时间累加起来 时间段 按天累计历时
[解决办法]
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
/*
设备名称 日 历时
---------- ------------------------------ -----------
设备1 2013-11-01 15
设备1 2013-11-02 15
设备2 2013-11-02 6
设备2 2013-11-03 24
设备2 2013-11-04 10
(5 row(s) affected)
*/
create table
#tb(设备名称 nvarchar(10), 开机时间 datetime,关机时间 datetime)
insert into #tb
select N'设备1' ,'2013-11-01 9:00:00' ,'2013-11-02 15:00:00'union
select N'设备2' ,'2013-11-02 18:00:00','2013-11-04 10:00:00'union
select * from #tb
;WITH CTE AS
(
SELECT [设备名称],[开机时间] ,
case when CONVERT(varchar(12) , [开机时间], 112 )=CONVERT(varchar(12) , [关机时间], 112 ) then [关机时间]
else CAST ( CONVERT(varchar(12) , dateadd(dd,1,开机时间), 111 )+' 00:00:00' as datetime) end as '日期',
[关机时间]
FROM #tb
union all
select a.[设备名称],a.日期,
case when CONVERT(varchar(12) , a.日期 , 112 )=CONVERT(varchar(12) , b.[关机时间], 112 ) then b.[关机时间]
else CAST ( CONVERT(varchar(12) , DATEADD (dd,1,a.日期 ), 111 )+' 00:00:00' as datetime) end as '日期',
a.关机时间
from cte a join #tb b on a.设备名称 =b.设备名称 and a.关机时间 =b.关机时间
where a.日期 <b.[关机时间]
)
SELECT *,CONVERT (VARCHAR(10),[开机时间],120) '日' ,datediff(HOUR ,[开机时间] ,[日期]) '历时'FROM CTE
order by 设备名称
drop table #tb
CREATE TABLE [dbo].[device](
[device_name] [varchar](50) NULL,
[start_time] [datetime] NULL,
[end_time] [datetime] NULL
) ON [PRIMARY]
GO
insert into device
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' union all
select '设备3','2013-11-29 09:20:30.000','2013-12-02 08:30:45.000'
select tt.[device_name],
convert(varchar(10),dateadd(day,t.number,tt.[start_time]),120) as 日,
case when dateadd(day,t.number,tt.[start_time]) = tt.[start_time]
then datediff(second,tt.[start_time],convert(varchar(10),dateadd(day,t.number,tt.[start_time]),120)+' 23:59:59')+1
when convert(varchar(10),dateadd(day,t.number,tt.[start_time]),120) = CONVERT(varchar(10),tt.[end_time],120)
then datediff(second,CONVERT(varchar(10),tt.[end_time],120)+' 00:00:00',tt.[end_time])
else 24*2600
end as 历时
from [device] tt
inner join master..spt_values t
on convert(varchar(10),dateadd(day,t.number,tt.[start_time]),120) >=CONVERT(varchar(10),tt.[start_time],120)
and convert(varchar(10),dateadd(day,t.number,tt.[start_time]),120) <=CONVERT(varchar(10),tt.[end_time],120)
where t.type = 'P'
/*
device_name日 历时
设备12013-11-0154000
设备12013-11-0254000
设备22013-11-0221600
设备22013-11-0362400
设备22013-11-0436000
设备32013-11-2952770
设备32013-11-3062400
设备32013-12-0162400
设备32013-12-0230645
*/
