This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

TSQL Challenge: Packing Date and Time Intervals

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: