Merge time slots where there are no gaps greater than slot allocation

Transact-SQL
1

Hello ,

I have a query that generates time slots of 5 min between a day and i want to join the slot together where there isnt a gap of greater than 5 min, would like to group them by the user id.

i have some sample data

So based on the userid group the slots together where there is no gap.

thanks

2

For anyone else who wanted to know the solution that i have used --

-- solution
With T1 As
(
Select UserName, StartTime Time
From Sessions

Union All

Select UserName, EndTime
From Sessions
),
T2 As
(
Select Row_Number() Over(Partition By UserName Order By Time) Nm,
UserName, Time
From T1
),
T3 As
(
Select A.Nm-Row_Number() Over(Partition By A.UserName Order By A.Time,B.Time) Nm1,
A.UserName,
A.Time StartTime,
B.Time EndTime
From T2 A
Inner join T2 B
On A.UserName = B.UserName
And A.Nm=B.Nm - 1
Where Exists
( Select *
From Sessions S
Where S.UserName = A.UserName
And (S.StartTime = A.Time) )
Or A.Time=B.Time
)
Select UserName,
Min(StartTime) StartTime,
Max(EndTime) EndTime
From T3
Group By UserName, Nm1
Order By UserName, StartTime;

3

Any chance you could drop the create table statements in here and some sample inserts? I'm guessing this is unsolved as your lower query doesn't appear to reflect your requirements, though it looks pretty straight forward. May be a better way to solve it though :slight_smile: