I have a strage case of grouping query.

I need to write a sql server query that groups rows based on the difference between datetime column value from a previous row.

That's the query:

select
   [id],
   [timestamp],
   LAG([timestamp]) OVER (ORDER BY [id]) as [prev_timestamp],
   datediff(second, [timestamp], LAG([timestamp]) OVER (ORDER BY [id])) as [timestamp_diff]
from orders
order by Id

Which returns results something like this:

• the last column is what i'm trying to achieve
• clarification - the count column should be per group. not aggregative and independent from the other counts.

I have a strage case of grouping query.

I need to write a sql server query that groups rows based on the difference between datetime column value from a previous row.

That's the query:

select
   [id],
   [timestamp],
   LAG([timestamp]) OVER (ORDER BY [id]) as [prev_timestamp],
   datediff(second, [timestamp], LAG([timestamp]) OVER (ORDER BY [id])) as [timestamp_diff]
from orders
order by Id

Which returns results something like this:

• the last column is what i'm trying to achieve
• clarification - the count column should be per group. not aggregative and independent from the other counts.

Couple of things to notice (also regarding the real situation):

1. A new group starts when the time difference between two consecutive rows is between -1 and -10 seconds. The group terminates at the last row where this condition applies.
2. Certain time differences should be ignored:
   • 0 seconds
   • Greater than 1 second
   • Less than -11 seconds
3. The count should be recorded in the same row where the group starts.
4. The id values are not sequential, as they belong to a specific user. The final query should also group results by userId.
5. In reality differences can be anything from a few seconds to several day long - in the past or in the future.

I've attempted various approaches using LEAD(), LAG(), and cursors, but due to the dynamic nature of the groups, I haven't been able to get it to work correctly.

Also i saw some questions already here on this, some on datediff, some on grouping dates, but none of them are what i'm looikng for.

• sql-server
• t-sql
• gaps-and-islands

• I create a fiddle that might help us collaborate on your issue dbfiddle.uk/M2fcT2Gz – Bart McEndree Commented Mar 11 at 13:32
• The row with ID 10059729 with a diff of -5 should also have a count yes? see dbfiddle.uk/hg0BYzGi – Bart McEndree Commented Mar 11 at 13:39
• @BartMcEndree - yes that's correct. the last column ("count") is what i'm the result i want. -6 and -5 both are betwenn -1 and -10 that's why the count = 2. – yaya Commented Mar 11 at 13:51
• Is this closer to what you need? dbfiddle.uk/xSp1Bbxk – Bart McEndree Commented Mar 11 at 13:57
• @GertArnold - i can look at it of course from the "other" side and do the same with lead instead of lag. 2 possible solutions are based on recursive (iterative) cte's and cursor - to loop through each row. That's not helping much because i'm still not able to "see" other rows except the previous / next one. – yaya Commented Mar 11 at 13:57

1 Answer 1

This feels a little heavy handed, but I believe it captures your criteria:

with cte AS 
(
  select
   [id],
   [timestamp],
   LAG([timestamp]) OVER (ORDER BY [id]) as [prev_timestamp],
   datediff(second, [timestamp], LAG([timestamp]) OVER (ORDER BY [id])) as [timestamp_diff]
  from orders

)
,group_start AS 
(
  SELECT cte.*, 
   --establish the start of a group
   CASE WHEN timestamp_diff between -10 and -1 
        and (LAG(timestamp_diff) OVER (ORDER BY id) < -10
          or LAG(timestamp_diff) OVER (ORDER BY id) > -1)
      THEN 1 
    END as group_start_ind
  FROM cte
)
,group_ind AS
(
  SELECT group_start.*,
   --give each group an id for final partitioning  
   sum(group_start_ind) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as group_id
  FROM group_start
)
SELECT group_ind.*,
  --for the first member of each group/partition, write the count of records meeting the condition
  CASE WHEN group_start_ind IS NOT NULL
     THEN count(CASE WHEN timestamp_diff BETWEEN -10 AND -1 THEN 1 END) OVER (PARTITION BY group_id)
     END as outpt
FROM group_ind
ORDER BY id

dbfiddle here