流水表A

IP                       时间
192.168.1.1    2012-02-01 08:00:00
192.168.1.3    2012-02-01 08:00:01
192.168.1.3    2012-02-01 08:01:00
192.168.1.1    2012-02-01 08:01:00
192.168.1.1    2012-02-01 08:06:00
192.168.1.3    2012-02-01 08:07:00
192.168.1.3    2012-02-01 09:01:00
192.168.1.4    2012-02-01 09:07:00
192.168.1.4    2012-02-01 09:08:00

基础表B
IP                开始时间1  结束时间1 开始时间2 结束时间2
192.168.1.1    9:00      11:30      14:00      17:30
192.168.1.2    9:00      11:30      14:00      17:30
192.168.1.3    9:00      11:30      14:00      17:30
192.168.1.4    9:00      11:30      14:00      17:30
192.168.1.5    9:00      11:30      14:00      17:30

结果是

IP                  开始时间                         结束时间            差距
192.168.1.1    2012-02-01 09:00:00.000    2012-02-01 10:00:00.000    60
192.168.1.2    2012-02-01 09:00:00.000    2012-02-01 10:00:00.000    60
192.168.1.3    2012-02-01 09:01:00.000    2012-02-01 10:00:00.000    59
192.168.1.4    2012-02-01 09:00:00.000    2012-02-01 09:07:00.000    7
192.168.1.4    2012-02-01 09:00:00.000    2012-02-01 09:08:00.000    8
192.168.1.4    2012-02-01 09:08:00.000    2012-02-01 10:00:00.000    52
192.168.1.4    2012-02-01 09:07:00.000    2012-02-01 10:00:00.000    53
192.168.1.5    2012-02-01 09:00:00.000    2012-02-01 10:00:00.000    60


并没有解决到实际问题
所需要的结果是，请注意192.168.1.4的记录，也就是说如果【两个相近时间】差小于5分钟就不在结果集中

IP                  开始时间                         结束时间            差距
192.168.1.1    2012-02-01 09:00:00.000    2012-02-01 10:00:00.000    60
192.168.1.2    2012-02-01 09:00:00.000    2012-02-01 10:00:00.000    60
192.168.1.3    2012-02-01 09:01:00.000    2012-02-01 10:00:00.000    59
192.168.1.4    2012-02-01 09:00:00.000    2012-02-01 09:07:00.000    7
192.168.1.4    2012-02-01 09:08:00.000    2012-02-01 10:00:00.000    52
192.168.1.5    2012-02-01 09:00:00.000    2012-02-01 10:00:00.000    60

create table ta
(IP varchar(15), 时间 datetime)

insert into ta
select '192.168.1.1', '2012-02-01 08:00:00' union all
select '192.168.1.3', '2012-02-01 08:00:01' union all
select '192.168.1.3', '2012-02-01 08:01:00' union all
select '192.168.1.1', '2012-02-01 08:01:00' union all
select '192.168.1.1', '2012-02-01 08:06:00' union all
select '192.168.1.3', '2012-02-01 08:07:00' union all
select '192.168.1.3', '2012-02-01 09:01:00' union all
select '192.168.1.4', '2012-02-01 09:07:00' union all
select '192.168.1.4', '2012-02-01 09:08:00'

create table tb
(IP varchar(15), 开始时间1 varchar(6), 结束时间1 varchar(6), 开始时间2 varchar(6), 结束时间2 varchar(6))

insert into tb
select '192.168.1.1', '9:00', '11:30', '2:00', '5:30' union all
select '192.168.1.2', '9:00', '11:30', '2:00', '5:30' union all
select '192.168.1.3', '9:00', '11:30', '2:00', '5:30' union all
select '192.168.1.4', '9:00', '11:30', '2:00', '5:30' union all
select '192.168.1.5', '9:00', '11:30', '2:00', '5:30'


declare @c datetime
select @c='2012-02-01 10:00:00'

;with 
t1 as
(select IP,cast(convert(varchar,@c,23)+' '+tb.开始时间1 as datetime) dt from tb
 union all
 select IP,cast(convert(varchar,@c,23)+' '+tb.结束时间1 as datetime) dt from tb
),
t2 as
(select ta.IP, ta.时间 dt
from ta
inner join tb on ta.IP=tb.IP
where ta.时间 between convert(varchar,@c,23)+' '+tb.开始时间1 and convert(varchar,@c,23)+' '+tb.结束时间1
),
t4 as
(select t3.IP,t3.dt,row_number() over(partition by t3.IP order by t3.dt) rn 
from (select * from t1 union all select * from t2) t3
),
t5 as
(select t41.IP,t41.dt dt1,t42.dt dt2,@c currenttime
from (select * from t4 where dt<=@c) t41
left join (select * from t4 where dt<=@c) t42 
on t41.IP=t42.IP and t41.rn=t42.rn-1
)
select IP,dt1 '开始时间',isnull(dt2,current