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

Question

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

Answer 1

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 :

Answer 2

Alternatively, recursive CTE solution

with dat as (
    -- sample data
    select * from (
        values
            (0125,cast('20160306 12:24:01' as datetime))
            ,(0125,cast('20160306 12:34:06' as datetime))
            ,(0125,cast('20160306 13:22:02' as datetime))
            ,(0125,cast('20160306 16:24:10' as datetime))
            ,(0125,cast('20160306 17:10:08' as datetime)) 
            ,(0125,cast('20160306 18:24:10' as datetime))
            ,(0125,cast('20160306 19:10:08' as datetime)) 
        )t([User],TransactionTime)
), hdrs as (
    select [User], TransactionTime= min(TransactionTime), rn = cast(0 as bigint)
    from dat
    group by [User]
    union all
    select dat.[User], dat.TransactionTime
         , rn = hdrs.rn + row_number() over(partition by hdrs.[user], hdrs.TransactionTime order by dat.TransactionTime) - 1
    from dat
    join hdrs on dat.[User]= hdrs.[User] and
    dat.TransactionTime > dateadd(HOUR,1,hdrs.TransactionTime)    
)
select hdrs.[User],hdrs.TransactionTime, n = count(*) 
from hdrs
join dat on rn = 0 and dat.TransactionTime between hdrs.TransactionTime and dateadd(HOUR,1,hdrs.TransactionTime)
group by hdrs.[User],hdrs.TransactionTime 

EDIT

You can try if not exists within CTE will perform better than row_number() in the above query

with dat as (
    -- sample data
    select * from (
        values
            (0125,cast('20160306 12:24:01' as datetime))
            ,(0125,cast('20160306 12:34:06' as datetime))
            ,(0125,cast('20160306 13:22:02' as datetime))
            ,(0125,cast('20160306 16:24:10' as datetime))
            ,(0125,cast('20160306 17:10:08' as datetime)) 
            ,(0125,cast('20160306 18:24:10' as datetime))
            ,(0125,cast('20160306 19:10:08' as datetime)) 
        )t([User],TransactionTime)
), hdrs as (
    select [User], TransactionTime= min(TransactionTime), till=dateadd(HOUR,1,min(TransactionTime)) 
    from dat
    group by [User]
    union all
    select d1.[User], d1.TransactionTime, dateadd(HOUR,1, d1.TransactionTime)
    from dat d1
    join hdrs on hdrs.[User]=d1.[User] and d1.TransactionTime > hdrs.till
        and not exists( 
             select 1 
             from dat d2
             where hdrs.[User]=d2.[User] and d2.TransactionTime > hdrs.till and d2.TransactionTime <= d1.TransactionTime )
)
select hdrs.[User], hdrs.TransactionTime, count(*) n
from hdrs
join dat on hdrs.[User]=dat.[User] and dat.TransactionTime between hdrs.TransactionTime and hdrs.till
group by hdrs.[User], hdrs.TransactionTime
order by hdrs.[User], hdrs.TransactionTime