create table tb(日期 datetime,状态 nvarchar(10),持续时间 int)
insert into tb select '2011-6-9 9:10','工作',60
insert into tb select '2011-6-9 10:10','坏机',61
insert into tb select '2011-6-9 11:11','换型',61
insert into tb select '2011-6-9 12:12','计划停机',58
insert into tb select '2011-6-9 13:10','损失',60
insert into tb select '2011-6-9 14:10','停产',4058
go
declare @dt1 datetime,@dt2 datetime
set @dt1='2011-6-9 9:30'
set @dt2='2011-6-9 16:00'
;with c1 as(
select top 1 日期,状态,持续时间-datediff(mi,日期,@dt1)持续时间 from tb where 日期<=@dt1 order by 日期 desc
union all
select 日期,状态,持续时间 from tb where 日期>@dt1 and 日期<@dt2
),c2 as(
select top 1 日期,状态,datediff(mi,日期,@dt2)持续时间 from c1 order by 日期 desc
union all
select top 1 @dt2,状态,null from c1 order by 日期 desc
)
select * from c1 a where not exists(select 1 from c2 where 日期=a.日期)
union all
select * from c2 order by 日期
/*
日期                      状态         持续时间
----------------------- ---------- -----------
2011-06-09 09:10:00.000 工作         40
2011-06-09 10:10:00.000 坏机         61
2011-06-09 11:11:00.000 换型         61
2011-06-09 12:12:00.000 计划停机       58
2011-06-09 13:10:00.000 损失         60
2011-06-09 14:10:00.000 停产         110
2011-06-09 16:00:00.000 停产         NULL

(7 行受影响)

*/
go
drop table tb