Skip to content
June 26, 2013 / Rodrigo Chiolas

Packing / merge / combine date and time intervals in t-sql

Challenge

On a recent assignment, I was working with date intervals in T-SQL very heavily and had to define various operations with those intervals. On one of those operations, I had to merge all intervals that overlap into one contiguous interval.

For example, users that can be logged in different aplications at the same time:

USERNAME STARTTIME ENDTIME
Bill 2013-06-22 08:00:00.000 2013-06-22 08:30:00.000
Bill 2013-06-22 08:30:00.000 2013-06-22 09:00:00.000
Bill 2013-06-22 09:00:00.000 2013-06-22 09:30:00.000
Bill 2013-06-22 10:00:00.000 2013-06-22 11:00:00.000
Bill 2013-06-22 10:30:00.000 2013-06-22 12:00:00.000
Bill 2013-06-22 11:30:00.000 2013-06-22 12:30:00.000
John 2013-06-22 08:00:00.000 2013-06-22 10:30:00.000
John 2013-06-22 08:30:00.000 2013-06-22 10:00:00.000
John 2013-06-22 09:00:00.000 2013-06-22 09:30:00.000
John 2013-06-22 11:00:00.000 2013-06-22 11:30:00.000
John 2013-06-22 11:32:00.000 2013-06-22 12:00:00.000
John 2013-06-22 12:04:00.000 2013-06-22 12:30:00.000
Anna 2013-06-22 08:00:00.000 2013-06-22 09:00:00.000
Anna 2013-06-22 08:00:00.000 2013-06-22 08:30:00.000
Anna 2013-06-22 08:30:00.000 2013-06-22 09:00:00.000
Anna 2013-06-22 09:30:00.000 2013-06-22 09:30:00.000

And we need to know login times for each user, regardless of the  applications they are logged.

Solution

The folowing code uses CTE (common table expression) to achieve the result.

; WITH CTE1 AS
(
  SELECT UserName, StartTime Time
  FROM Logins
  UNION ALL
  SELECT UserName, EndTime
  FROM Logins
),
CTE2 AS
(
  SELECT Row_Number() Over(Partition BY UserName ORDER BY Time) Nm,
    UserName, Time
  FROM CTE1
),
CTE3 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 CTE2 A
  INNER JOIN CTE2 B
    ON A.UserName = B.UserName
    AND A.Nm=B.Nm - 1
  WHERE EXISTS
   ( 
     SELECT *
     FROM Logins L
     WHERE L.UserName = A.UserName
     AND (L.StartTime < B.Time AND L.EndTime > A.Time) )
     OR A.Time=B.Time
   )
  SELECT UserName,
    Min(StartTime) StartTime,
    Max(EndTime) EndTime
  FROM CTE3
  GROUP BY UserName, Nm1
  ORDER BY UserName, StartTime;

The final result:

USERNAME STARTTIME ENDTIME
Anna 2013-06-22 08:00:00.000 2013-06-22 09:00:00.000
Anna 2013-06-22 09:30:00.000 2013-06-22 09:30:00.000
Bill 2013-06-22 08:00:00.000 2013-06-22 09:30:00.000
Bill 2013-06-22 10:00:00.000 2013-06-22 12:30:00.000
John 2013-06-22 08:00:00.000 2013-06-22 10:30:00.000
John 2013-06-22 11:00:00.000 2013-06-22 11:30:00.000
John 2013-06-22 11:32:00.000 2013-06-22 12:00:00.000
John 2013-06-22 12:04:00.000 2013-06-22 12:30:00.000

References:

Here is the original post used to solve this issue: Original Link

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: