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:

Here is a db<>fiddle.

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