Wednesday, February 1, 2012

A customer have a table with data stored by dates, and asked us to present data from this table by sequential date ranges.

This query sounded trivial but took us half a day to create such a select.

For simplicity consider a table of integer numbers, and try to build a select that returns pairs of continuous ranges of values.

So, for an input like this:

```declare @values table (   value int not null primary key ); insert into @values(value) select  1 union all select  2 union all select  3 union all select  5 union all select  6 union all select  8 union all select 10 union all select 12 union all select 13 union all select 14; ```

You will have a following output:

```low  high
---- ----
1    3
5    6
8    8
10   10
12   14```

Logic of the algorithms is like this:

1. get a low bound of each range (a value without value - 1 in the source);
2. get a high bound of each range (a value without value + 1 in the source);
3. combine low and high bounds.

Following this logic we have built at least three different queries, where the shortest one is:

``` with source as (   select * from @values ) select   l.value low,   min(h.value) high from   source l   inner join   source h   on     (l.value - 1 not in (select value from source)) and     (h.value + 1 not in (select value from source)) and     (h.value >= l.value) group by   l.value;```

Looking at this query it's hard to understand why it took so long to write so simple code...

Wednesday, February 1, 2012 8:34:09 PM UTC      Comments [0] -
SQL Server puzzle | Tips and tricks
All comments require the approval of the site owner before being displayed.
 Name E-mail Home page Remember Me Comment (Some html is allowed: `a@href@title, b, blockquote@cite, em, i, strike, strong, sub, super, u`) where the @ means "attribute." For example, you can use or
. Enter the code shown (prevents robots): Live Comment Preview
Archive
 < May 2024 >
SunMonTueWedThuFriSat
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678
Categories
 .NET AI Angular AngularJS Announce ASP.NET Azure BizTalk Server C++ Incremental Parser Java javascript JSF and Facelets kendoui ML.NET SCCBridge SQL Server puzzle Thinking aloud Tips and tricks Window Search xslt
Blogroll
Statistics
Total Posts: 387
This Year: 3
This Month: 0
This Week: 0