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.
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.
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
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
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