The relational model deals with date and time intervals and defines various operations on those. One of the fundamental operations is packing date and time intervals. Packing of intervals means that you want to merge all intervals that overlap into one contiguous interval. SQL Server doesn’t support a native interval type, so most represent an interval with two attributes holding the start and end points of the interval, or one of the points and a duration.
The challenge at hand involves addressing the task of packing intervals using TSQL efficiently, with a standard, set-based solution, that is reusable in other platforms—namely, use only standard SQL constructs.
I’ll provide both small and large sets of rows for sample data. Use the small set to test the validity of your solution, and the large one to test its optimality.
Here’s the DDL and sample data for the small set to test the validity of your solution:
-- DDL and sample data, small
SET NOCOUNT ON;
USE tempdb;
IF OBJECT_ID('dbo.Sessions') IS NOT NULL DROP TABLE dbo.Sessions;
CREATE TABLE dbo.Sessions
(
id INT NOT NULL IDENTITY(1, 1),
username VARCHAR(14) NOT NULL,
starttime DATETIME2(3) NOT NULL,
endtime DATETIME2(3) NOT NULL,
CONSTRAINT PK_Sessions PRIMARY KEY(id),
CONSTRAINT CHK_endtime_gteq_starttime
CHECK (endtime >= starttime)
);
GO
INSERT INTO dbo.Sessions VALUES('User1', '20111201 08:00:00.000', '20111201 08:30:00.000');
INSERT INTO dbo.Sessions VALUES('User1', '20111201 08:30:00.000', '20111201 09:00:00.000');
INSERT INTO dbo.Sessions VALUES('User1', '20111201 09:00:00.000', '20111201 09:30:00.000');
INSERT INTO dbo.Sessions VALUES('User1', '20111201 10:00:00.000', '20111201 11:00:00.000');
INSERT INTO dbo.Sessions VALUES('User1', '20111201 10:30:00.000', '20111201 12:00:00.000');
INSERT INTO dbo.Sessions VALUES('User1', '20111201 11:30:00.000', '20111201 12:30:00.000');
INSERT INTO dbo.Sessions VALUES('User2', '20111201 08:00:00.000', '20111201 10:30:00.000');
INSERT INTO dbo.Sessions VALUES('User2', '20111201 08:30:00.000', '20111201 10:00:00.000');
INSERT INTO dbo.Sessions VALUES('User2', '20111201 09:00:00.000', '20111201 09:30:00.000');
INSERT INTO dbo.Sessions VALUES('User2', '20111201 11:00:00.000', '20111201 11:30:00.000');
INSERT INTO dbo.Sessions VALUES('User2', '20111201 11:32:00.000', '20111201 12:00:00.000');
INSERT INTO dbo.Sessions VALUES('User2', '20111201 12:04:00.000', '20111201 12:30:00.000');
INSERT INTO dbo.Sessions VALUES('User3', '20111201 08:00:00.000', '20111201 09:00:00.000');
INSERT INTO dbo.Sessions VALUES('User3', '20111201 08:00:00.000', '20111201 08:30:00.000');
INSERT INTO dbo.Sessions VALUES('User3', '20111201 08:30:00.000', '20111201 09:00:00.000');
INSERT INTO dbo.