MSSQL select count entities in date range

Question

I have a task to find how often a user performs actions per a minute. I have found the solution with grouping by date part:

CREATE TABLE UserAction(Id int, Date datetime, Action nvarchar(200))
Go
INSERT INTO UserAction VALUES
 (111, '2000-01-01 15:30:10', 'Action Made'),
 (555, '2000-01-01 15:30:10', 'Action Made'), 
 (111, '2000-01-01 15:30:20', 'Action Made'),
 (111, '2000-01-01 15:31:05', 'Action Made'),
 (111, '2000-01-01 15:31:10', 'Action Made'),
 (555, '2000-01-01 15:31:10', 'Action Made'),
 (111, '2000-01-01 15:32:05', 'Action Made'),
 (555, '2000-01-01 15:32:05', 'Action Made'),
 (555, '2000-01-03 15:35:10', 'Action Made'),
 (555, '2000-01-03 15:35:20', 'Action Made'),
 (111, '2000-01-03 15:36:05', 'Action Made'),
 (555, '2000-01-03 15:36:05', 'Action Made')
GO
-- query 
SELECT 
 Id,
 MIN(Date) as [From],
 MAX(Date) as [To],
 COUNT(*) as [Count]
FROM 
 UserAction
GROUP BY 
 ID,
 DATEADD(MINUTE, 1 + (DATEDIFF(MINUTE, 0, Date) / 1) * 1, 0)
ORDER BY
 Id
-- should be
SELECT 
 * 
FROM 
 (VALUES
 (111, '2000-01-01 15:30:10.000', '2000-01-01 15:31:05.000', 3),
 (111, '2000-01-01 15:31:10.000', '2000-01-01 15:32:05.000', 2),
 (111, '2000-01-03 15:36:05.000', '2000-01-03 15:36:05.000', 1),
 (111, '2000-01-01 15:30:10.000', '2000-01-01 15:30:10.000', 1),
 (111, '2000-01-01 15:31:10.000', '2000-01-01 15:32:05.000', 2),
 (111, '2000-01-03 15:35:10.000', '2000-01-03 15:36:05.000', 3)
 )
as _shouldBe(Id, [From], [To], [Count])

But I cannot find out how to group by datediff in a minute(60 seconds). The result now:

Id From To Count
----------- ----------------------- ----------------------- -----------
111 2000年01月01日 15:30:10.000 2000年01月01日 15:30:20.000 2
111 2000年01月01日 15:31:05.000 2000年01月01日 15:31:10.000 2
111 2000年01月01日 15:32:05.000 2000年01月01日 15:32:05.000 1
111 2000年01月03日 15:36:05.000 2000年01月03日 15:36:05.000 1
555 2000年01月01日 15:30:10.000 2000年01月01日 15:30:10.000 1
555 2000年01月01日 15:31:10.000 2000年01月01日 15:31:10.000 1
555 2000年01月01日 15:32:05.000 2000年01月01日 15:32:05.000 1
555 2000年01月03日 15:35:10.000 2000年01月03日 15:35:20.000 2
555 2000年01月03日 15:36:05.000 2000年01月03日 15:36:05.000 1

How it should be:

Id From To Count
----------- ----------------------- ----------------------- -----------
111 2000年01月01日 15:30:10.000 2000年01月01日 15:31:05.000 3
111 2000年01月01日 15:31:10.000 2000年01月01日 15:32:05.000 2
111 2000年01月03日 15:36:05.000 2000年01月03日 15:36:05.000 1
111 2000年01月01日 15:30:10.000 2000年01月01日 15:30:10.000 1
111 2000年01月01日 15:31:10.000 2000年01月01日 15:32:05.000 2
111 2000年01月03日 15:35:10.000 2000年01月03日 15:36:05.000 3

I would be grateful if you could help me with finding the best solution here.

• Ihor Shubin: Microsoft SQL Server 2016

Answer 1

You should group by the result of division by 60 starting by the minimal date per Id:

declare @UserAction table (Id int, Date datetime, Action nvarchar(200))
INSERT INTO @UserAction VALUES
 (111, '2000-01-01 15:30:10', 'Action Made'),
 (555, '2000-01-01 15:30:10', 'Action Made'), 
 (111, '2000-01-01 15:30:20', 'Action Made'),
 (111, '2000-01-01 15:31:05', 'Action Made'),
 (111, '2000-01-01 15:31:10', 'Action Made'),
 (555, '2000-01-01 15:31:10', 'Action Made'),
 (111, '2000-01-01 15:32:05', 'Action Made'),
 (555, '2000-01-01 15:32:05', 'Action Made'),
 (555, '2000-01-03 15:35:10', 'Action Made'),
 (555, '2000-01-03 15:35:20', 'Action Made'),
 (111, '2000-01-03 15:36:05', 'Action Made'),
 (555, '2000-01-03 15:36:05', 'Action Made');
with cte AS
(
select *, 
 min(Date) over(partition by id) as min_dt
from @UserAction 
)
,cte_gr AS
(
select *,
 datediff(ss, min_dt, Date) / 60 as gr
from cte
)
select id, 
 min(date) as [From],
 max(date) as [To],
 count(*) as cnt
from cte_gr
group by id, gr;

• Ihor Shubin: Thank you for the answer. I am checking it now and it looks good!