Skip to content
Advertisement

SUM over datedifference?

Lets say I have the following records with column datetime

dtTime quantity
2020-12-10 19:21:52.293 1
2020-12-10 19:21:52.323 2
2020-12-10 19:21:53.293 1
2020-12-10 19:21:58.293 1
2020-12-10 19:21:59.193 1

How can I make this to return rows that are in range of let’s say 1.5second of eachother in the same rownumber? So sum the group by over:

Row_Number dtTime qauntity
1 2020-12-10 19:21:52.293 1
1 2020-12-10 19:21:52.323 2
1 2020-12-10 19:21:53.293 1
2 2020-12-10 19:21:58.293 1
2 2020-12-10 19:21:59.193 1

So desired sum result:

Row quantity
1 4
2 2

To clarify further based on the comments: The situation is that there is a trigger, which will insert 10 to 20 records. The difference between dtTime of record 1 and record 20 will be MAX 2 seconds. The time interval between trigger 1 and trigger 2 will always be bigger than this 2 seconds. I hope this claries the question on how to group!

Advertisement

Answer

This is a type of gaps-and-islands problem. Use lag() and a cumulative sum to define the groups:

select min(dttime), max(dttime), count(*)
from (select t.*,
             sum(case when prev_dttime > dateadd(millisecond, -1500, dttime) then 0 else 1 end) over (order by dttime) as grp
      from (select t.*,
                   lag(dttime) over (order by dttime) as prev_dttime 
            from t
           ) t
     ) t
group by grp;

Here is a db<>fiddle.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement