Get transactions done in an hour from each other (not group by hour)

I have a problem that I have been struggling with for a while now. If someone can please help me, that would be great. It is on SQL Server 2012.

I have a table with a set number of transactions and user Id's in. I need to count all the transactions that is in an hour of each other and group by user id. It cannot be grouped by datepart(hh,1,SomeColumn) as well because then it would only take transactions that happened at 16:00 - 16:59.

So I need to group it by the first transaction that happened + 1 hour, and then if another set of transactions happened later, I need to group it by that also.

Example:

The first transaction was 13:45 - I need a count of all transactions that happened from 13:45 - 14:45. Grouped by user ID.

Then I need to have a count of all the transactions that happened at 16:09 - 17:09 grouped by that same user Id.

I apologize if it is a bit confusing.

Table:

User | TransactionTime

0125 | 03/06/2016 12:24:01

0125 | 03/06/2016 12:34:06

0125 | 03/06/2016 13:22:02

0125 | 03/06/2016 16:24:10

0125 | 03/06/2016 17:10:08

Output:

User | TransactionTimeStart | TransactionTimeEnd | Transactions

0125 | 03/06/2016 12:24:01 | 03/06/2016 13:22:02 | 3

0125 | 03/06/2016 16:24:10 | 03/06/2016 17:10:08 | 2

Solution

Try this query (I test on SQL server 2012)

CREATE TABLE #tmp (usr INT,TransactionTime DATETIME)
CREATE TABLE #result (startTime DATETIME , endTime DATETIME)

INSERT INTO #tmp VALUES 
    (0125,'03/06/2016 12:24:01'),(0125,'03/06/2016 12:34:06')
    ,(0125,'03/06/2016 13:22:02'),(0125,'03/06/2016 16:24:10')
    ,(0125,'03/06/2016 17:10:08')

DECLARE @minTime DATETIME = (SELECT MIN(TransactionTime) FROM #tmp)
DECLARE @maxTime DATETIME = (SELECT MAX(TransactionTime) FROM #tmp)

DECLARE @tmp DATETIME = @minTime

WHILE @tmp < @maxTime
BEGIN
    IF @tmp > @maxTime 
        INSERT INTO #result VALUES (@tmp, DATEADD(HOUR,1,@maxTime))
    ELSE
        INSERT INTO #result VALUES (@tmp, DATEADD(HOUR,1,@tmp))
    SET @tmp = DATEADD(HOUR,1,@tmp)
END

SELECT DISTINCT t.usr
    ,r.startTime
    ,r.endTime
    ,COUNT(1) OVER (PARTITION BY r.startTime,r.endTime,t.usr) AS [cnt]
FROM #result r
LEFT JOIN #tmp t ON t.TransactionTime BETWEEN r.startTime AND r.endTime
WHERE t.usr IS NOT NULL


DROP TABLE #tmp
DROP TABLE #result

Result :