SQL Server 查询时间段

 

create table ABSWorkLog
(
id int identity(1,1),
MachineCode varchar(50),
startTime smalldatetime,
endTime smalldatetime
)
go

insert absworklog values ('abs1','2008-02-02','2008-02-09')
insert absworklog values ('abs1','2008-02-13','2008-02-26')
insert absworklog values ('abs1','2008-03-02','2008-03-26')
insert absworklog values ('abs2','2008-01-26','2008-02-06')
insert absworklog values ('abs2','2008-02-24','2008-03-08')
insert absworklog values ('abs3','2008-02-07','2008-02-25')
go

select * from absworklog

select * into #abs1worklog from absworklog where machinecode='abs1'
select * from #abs1worklog

select * into #otherabsworklog from absworklog where machinecode<>'abs1'
select * from #otherabsworklog

select DISTINCT machinecode into #otherabsname from absworklog where machinecode<>'abs1'
select * from #otherabsname absworklog

create table #temp
(
machinecode varchar(50),
stopTime int
)
go
select * from #temp

declare @startTime smalldatetime,@endTime smalldatetime

DECLARE MyCursor CURSOR FOR
SELECT startTime,endTime FROM #abs1worklog
open MyCursor
FETCH NEXT FROM MyCursor INTO @startTime, @endTime
WHILE @@fetch_status = 0
BEGIN
select a.machinecode,
t=(case when b.startTime is null then convert(int,@endTime)-convert(int,@startTime)
  when b.startTime<@startTime and b.endTime<@endTime then convert(int,@endTime)-convert(int,b.endTime)
  when b.startTime>@startTime and b.endTime>@endTime then convert(int,b.startTime)-convert(int,@startTime)
  when b.startTime>@startTime and b.endTime<@endTime then convert(int,b.startTime)-convert(int,@startTime)+convert(int,@endTime)-convert(int,b.endTime)
  end)
into #mid from #otherabsname a
right join #otherabsworklog b on b.machinecode=a.machinecode
where (b.startTime<@startTime and b.endTime>@startTime and b.endTime<@endTime)
 or (b.startTime>@startTime and b.endTime<@endTime)
 or (b.startTime>@startTime and b.startTime<@endTime and b.endTime>@endTime)
 or (b.starttime<@startTime and b.endTime>@endTime)

declare @name varchar(50),@t int
declare MidCurrsor CURSOR FOR
select machinecode,t from #mid
open MidCurrsor

FETCH NEXT FROM MidCurrsor INTO @name, @t
WHILE @@fetch_status = 0
BEGIN

insert #temp values (@name,@t)

FETCH NEXT FROM MidCurrsor INTO @name, @t
end
DEALLOCATE MidCurrsor
drop table #mid

FETCH NEXT FROM MyCursor INTO @startTime, @endTime
END
DEALLOCATE MyCursor
GO

select machinecode,sum(stoptime) from #temp GROUP BY machinecode