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)
You will have a following output:
Logic of the algorithms is like this:
Following this logic we have built at least three different queries, where the
with source as
select * from @values
(l.value - 1 not in (select value from source)) and
(h.value + 1 not in (select value from source)) and
(h.value >= l.value)
Looking at this query it's hard to understand why it took so
write so simple code...
a@href@title, b, blockquote@cite, em, i, strike, strong, sub, super, u