SQLTeam.com | Weblogs | Forums

Gather few messages into one based on time difference


#1

I have a unit which sends text data + timestamp every 20 minutes. In case the text data is long the unit split it into some messages with close time stamps but not always exactly the same (few second difference). in that case, I need to update the timestamps for all bulk to the first time stamp of the bulk.
There is guaranty that time difference between last message of bulk and first message of the next bulk is at least 15 minutes.
example:
For this messages which were got:
2017/11/08 10:00:01 A
2017/11/08 10:00:02 B
2017/11/08 10:00:16 C
2017/11/08 10:00:22 D
2017/11/08 10:20:01 E
2017/11/08 10:20:10 F
2017/11/08 10:21:16 G
2017/11/08 10:21:22 H

I need have the following time stamps:
2017/11/08 10:00:01 A
2017/11/08 10:00:01 B
2017/11/08 10:00:01 C
2017/11/08 10:00:01 D
2017/11/08 10:20:01 E
2017/11/08 10:20:01 F
2017/11/08 10:20:01 G
2017/11/08 10:20:01 H

How to do it?


#2

This is one way of doing it:

with cte1
  as (select dt
            ,msg
            ,row_number() over(order by dt) as rn
        from yourtable
     )
    ,cte2
  as (select a.dt
        from cte1 as a
               left outer join cte1 as b
                            on b.rn=a.rn-1
                           and b.dt>=dateadd(minute,-5,a.dt)
         where b.rn is null
     )
update a
   set a.dt=b.dt
  from yourtable as a
       inner join cte2 as b
               on b.dt>=dateadd(minute,-5,a.dt)
              and b.dt<=dateadd(minute,5,a.dt)
;

#3

Thank you very much. It is working.
although while I waited for the answer I found my soultion:
create table #temp_1(TS datetime)
insert into #temp_1(TS) values('2017/11/08 10:00:01'),('2017/11/08 10:00:02'),('2017/11/08 10:00:16'),('2017/11/08 10:00:22')
,('2017/11/08 10:20:01'),('2017/11/08 10:20:10'),('2017/11/08 10:21:16'),('2017/11/08 10:21:22')

select * from #temp_1

create table #temp_2(TS datetime, prev_ts datetime, is_first bit)
insert into #temp_2(TS , prev_ts)
select TS, lag(ts,1,dateadd(day,-1,TS)) over(ORDER BY TS)
from #temp_1
--drop table #temp_2

update #temp_2
set is_first=case
when datediff(minute,prev_ts,ts)>5 then 1
else 0
end
select * from #temp_2

update A
set TS=B.TS
from #temp_1 A,
(select TS from #temp_2 where is_first=1 )B
where datediff(minute,B.TS, A.TS)<5

select * from #temp_1

drop table #temp_1
drop table #temp_2