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.