SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

SQL and contiguous data ranges.

As the saying goes "work smarter, not harder", this is definitely the case for SQL. If you have a poorly performing query, then you are not only affecting that one user but diverting precious machine resources needlessly. This is doubly true if its part of the 20% of code executed 80% of the time. Here's one smart solution to a common problem, and after the umpteenth newsgroup question which could be solved by this , I thought id take a little time out to dissect the theory and give a few good examples. Steve Kass of Drew University , seems to be the man to credit for this idea, although i cant find any specific details.

The problem is given a list of numbers we need to output the covered ranges. The key is to simply generate an unique number to group your contiguous ranges by. The value of this number is completely irrelevant , it just has to be the same for each item within a range.

So lets start simply , if we have a range of numbers (1,2,3,5,6,7,8), what we need returned is:
1 - 3
5 - 8

Lets generate some test data

`Drop table Contiggocreate table Contig(Id integer)goinsert into contig values(1)insert into contig values(2)insert into contig values(3)insert into contig values(5)insert into contig values(6)insert into contig values(7)insert into contig values(8)`

If we then execute

`Select Id,row_number() over (order by Id desc)from Contig`

we get :

``Id          RowNum----------- --------------------8           17           26           35           43           52           61           7``

What we are going to do is generate our 'grouping number' by adding Id and Rownum. So now execute

`with cteContigOrder(Id,RowNum)as(Select Id,RowNum = row_number() over (order by Id desc) from Contig)Select Id,RowNum,Grouping = Id+RowNum from cteContigOrder`

which returns
``Id          RowNum               Grouping----------- -------------------- --------------------8           1                    97           2                    96           3                    95           4                    93           5                    82           6                    81           7                    8``

Looking at this its easy to see how id 1 thru 3 have a grouping of 8 and id 5 thru 8 have a grouping of 9.

So to generate our ranges we need do this :

`;with cteContigOrder(Id,RowNum)as(Select Id,RowNum = row_number() over (order by Id desc) from Contig)Select Min(Id),Max(Id) from cteContigOrdergroup by Id+RowNum order by 1`

which gives us

``----------- -----------1           35           8``

exactly the ranges we wanted.

Using this it very easy to apply to Dates.

`Drop Table ContigDatesgoCreate Table ContigDates(DateCol smalldatetime)goinsert into ContigDates(DateCol) values('01jan2009')insert into ContigDates(DateCol) values('02jan2009')insert into ContigDates(DateCol) values('03jan2009')insert into ContigDates(DateCol) values('01feb2009')insert into ContigDates(DateCol) values('02feb2009')insert into ContigDates(DateCol) values('03feb2009')insert into ContigDates(DateCol) values('10feb2009')gowith cteDateList(DateCol,Grouping)as(Select DateCol,DateCol + row_number() over (order by datecol desc)from ContigDates)Select Min(DateCol),Max(DateCol)from cteDateListgroup by Groupingorder by 1go`

If you have a table which itself is a range of dates , i find it simplest to 'explode' the range and then re-summarize. Here I have used a Calendar table to find all the days within the ranges.

`Drop Table ContigDateRangesgoCreate Table ContigDateRanges(DateCollo smalldatetime,DateColHi smalldatetime)goinsert into ContigDateRanges(Datecollo,Datecolhi) values('01jan2009','03jan2009')insert into ContigDateRanges(Datecollo,Datecolhi) values('04jan2009','10jan2009')insert into ContigDateRanges(Datecollo,Datecolhi) values('01feb2009','01feb2009')insert into ContigDateRanges(Datecollo,Datecolhi) values('02feb2009','05feb2009')gowith cteDateList(DateCol,Grouping)as(Select Calendar.Dte,       Calendar.Dte + row_number() over (order by Calendar.Dte desc)  from ContigDateRanges,       Calendar where Calendar.Dte between DateColLo and DateColHi)Select Min(DateCol),Max(DateCol)from cteDateListgroup by Groupingorder by 1`

I hope that this gives you at least one more option to improve your system and to remove a few more evil cursors.