Sql-server – MSSQL select count entities in date range

group byselectsql serversql-server-2016

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.

Best Answer

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;
Related Question