gap – sqlsunday.com

Human-readable ranges of integers or dates

This is a real-world problem that I came across the other day. In a reporting scenario, I wanted to output a number of values in an easy, human-readable way for a report. But just making a long, comma-separated string of numbers doesn’t really make it very readable. This is particularly true when there are hundreds of values.

So here’s a powerful pattern to solve that task.

Working with intervals

At one point or another, you’re going to come across intervals when working in SQL Server. You could say that an interval is where you don’t have a single value, but actually a range of values, commonly delimited within a start and an end value. This range could be a group of accounts, versions of dimension members (in an SCD) or date/time intervals.