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.