Editing: T-SQL: Gaps and Islands Problem

Title
<html> <body> This article will consider a simple classical <a href="http://blogs.msdn.com/b/samlester/archive/2012/09/04/tsql-solve-it-your-way-gaps-and-islands-with-a-twist.aspx"> Gaps & Islands problem</a> asked recently in <a href="http://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql"> Transact-SQL Forum</a> at MSDN with non original title <a href="http://social.msdn.microsoft.com/Forums/sqlserver/en-US/cdda79db-8775-4823-93d6-bd1cb820bbb3/query-help#d71158a3-d396-46bb-9572-018f15d4ad70"> "Query Help"</a>.<br> <br> [toc]<br> <br> <h1><a name="Problem_Definition"></a>Problem Definition</h1> <br> The thread originator was kind enough to provide <a href="http://msdn.microsoft.com/en-us/library/ff848799.aspx"> DDL</a> of the table and some data to describe the task:<br> <br> <div class="reCodeBlock" style="border:1px solid #7f9db9; overflow-y:auto"> <div style="background-color:#ffffff"><span style="margin-left:0px!important"><code style="color:#006699; font-weight:bold">Create</code> <code style="color:#006699; font-weight:bold">table</code> <code style="color:#000000"> T1</code></span></div> <div style="background-color:#f8f8f8"><span style="margin-left:0px!important"><code style="color:#000000">(Id </code><code style="color:#006699; font-weight:bold">int</code> <code style="color:#000000"> identity </code><code style="color:#006699; font-weight:bold">primary</code> <code style="color:#006699; font-weight:bold"> key</code><code style="color:#000000">,</code></span></div> <div style="background-color:#ffffff"><span style="margin-left:0px!important"><code style="color:#000000">VoucherNo </code><code style="color:#006699; font-weight:bold">varchar</code><code style="color:#000000">(4),</code></span></div> <div style="background-color:#f8f8f8"><span style="margin-left:0px!important"><code style="color:#000000">TransNo </code><code style="color:#006699; font-weight:bold">varchar</code><code style="color:#000000">(10)</code></span></div> <div style="background-color:#ffffff"><span style="margin-left:0px!important"><code style="color:#000000">)</code></span></div> <div style="background-color:#f8f8f8"><span style="margin-left:0px!important"> </span></div> <div style="background-color:#ffffff"><span style="margin-left:0px!important"><code style="color:#006699; font-weight:bold">Insert</code> <code style="color:#006699; font-weight:bold">into</code> <code style="color:#000000"> T1 </code><code style="color:#006699; font-weight:bold">values</code> <code style="color:#000000"> (</code><code style="color:blue">'V100'</code><code style="color:#000000">,</code><code style="color:blue">'Trns1'</code><code style="color:#000000">),(</code><code style="color:blue">'V101'</code><code style="color:#000000">,</code><code style="color:blue">'Trns1'</code><code style="color:#000000">),(</code><code style="color:blue">'V102'</code><code style="color:#000000">,</code><code style="color:blue">'Trns1'</code><code style="color:#000000">),(</code><code style="color:blue">'V103'</code><code style="color:#000000">,</code><code style="color:blue">'Trns1'</code><code style="color:#000000">),(</code><code style="color:blue">'V104'</code><code style="color:#000000">,</code><code style="color:blue">'Trns1'</code><code style="color:#000000">),(</code><code style="color:blue">'V106'</code><code style="color:#000000">,</code><code style="color:blue">'Trns1'</code><code style="color:#000000">)</code></span></div> </div> <br> And he also provided the desired output:<br> <br> <table> <tbody> <tr> <td>TransNo </td> <td>FirstVoucher </td> <td>LastVoucher </td> <td>Quantity </td> </tr> <tr> <td> Trns1</td> <td>V100</td> <td>V104</td> <td>5</td> </tr> <tr> <td> Trns1</td> <td>V106</td> <td>V106</td> <td>1</td> </tr> </tbody> </table> <br> The problem is to find consecutive vouchers (100-104, then 106).<br> <br> <h1><a name="Solution"></a>Solution</h1> <br> <span class="Apple-tab-span" style="white-space:pre"></span>As mentioned, this is a common problem in Transact SQL and it was described by Itzik Ben Gan <a href="http://www.manning.com/nielsen/SampleChapter5.pdf">here</a> or by Plamen Ratchev in this easy to understand blog post<br> <a href="http://pratchev.blogspot.com/2010/02/refactoring-ranges.html">Refactoring Ranges</a>. Knowing main idea of the solution it is easy to provide it assuming that all voucher numbers come in the following format (letter V following by the 3 digit number):<br> <br> <div class="reCodeBlock" style="border:1px solid #7f9db9; overflow-y:auto"> <div style="background-color:#ffffff"><span style="margin-left:0px!important"><code style="color:#000000">;</code><code style="color:#006699; font-weight:bold">WITH</code> <code style="color:#000000">cte</code></span></div> <div style="background-color:#f8f8f8"><span style="margin-left:0px!important"><code style="color:#006699; font-weight:bold">AS</code> <code style="color:#000000">(</code></span></div> <div style="background-color:#ffffff"><span><code>    </code><span style="margin-left:12px!important"><code style="color:#006699; font-weight:bold">SELECT</code> <code style="color:#000000">*</code></span></span></div> <div style="background-color:#f8f8f8"><span><code>        </code><span style="margin-left:24px!important"><code style="color:#000000">,</code><code style="color:#ff1493">CAST</code><code style="color:#000000">(</code><code style="color:#ff1493">SUBSTRING</code><code style="color:#000000">(VoucherNo, 2, 3) </code><code style="color:#006699; font-weight:bold">AS</code> <code style="color:#006699; font-weight:bold"> INT</code><code style="color:#000000">) - ROW_NUMBER() OVER (</code></span></span></div> <div style="background-color:#ffffff"><span><code>            </code><span style="margin-left:36px!important"><code style="color:#006699; font-weight:bold">ORDER</code> <code style="color:#006699; font-weight:bold">BY</code> <code style="color:#000000"> VoucherNo</code></span></span></div> <div style="background-color:#f8f8f8"><span><code>            </code><span style="margin-left:36px!important"><code style="color:#000000">) </code><code style="color:#006699; font-weight:bold">AS</code> <code style="color:#000000"> Grp</code></span></span></div> <div style="background-color:#ffffff"><span><code>    </code><span style="margin-left:12px!important"><code style="color:#006699; font-weight:bold">FROM</code> <code style="color:#000000">T1</code></span></span></div> <div style="background-color:#f8f8f8"><span><code>    </code><span style="margin-left:12px!important"><code style="color:#000000">)</code></span></span></div> <div style="background-color:#ffffff"><span style="margin-left:0px!important"><code style="color:#006699; font-weight:bold">SELECT</code> <code style="color:#000000">TransNo</code></span></div> <div style="background-color:#f8f8f8"><span><code>    </code><span style="margin-left:12px!important"><code style="color:#000000">,</code><code style="color:#006699; font-weight:bold">min</code><code style="color:#000000">(VoucherNo) </code><code style="color:#006699; font-weight:bold">AS</code> <code style="color:#000000"> FirstVoucherNo</code></span></span></div> <div style="background-color:#ffffff"><span><code>    </code><span style="margin-left:12px!important"><code style="color:#000000">,</code><code style="color:#006699; font-weight:bold">max</code><code style="color:#000000">(VoucherNo) </code><code style="color:#006699; font-weight:bold">AS</code> <code style="color:#000000"> LastVoucherNo</code></span></span></div> <div style="background-color:#f8f8f8"><span><code>    </code><span style="margin-left:12px!important"><code style="color:#000000">,</code><code style="color:#ff1493">count</code><code style="color:#000000">(*) </code><code style="color:#006699; font-weight:bold">AS</code> <code style="color:#000000"> Quantity</code></span></span></div> <div style="background-color:#ffffff"><span style="margin-left:0px!important"><code style="color:#006699; font-weight:bold">FROM</code> <code style="color:#000000">cte</code></span></div> <div style="background-color:#f8f8f8"><span style="margin-left:0px!important"><code style="color:#006699; font-weight:bold">GROUP</code> <code style="color:#006699; font-weight:bold">BY</code> <code style="color:#000000"> TransNo</code></span></div> <div style="background-color:#ffffff"><span><code>    </code><span style="margin-left:12px!important"><code style="color:#000000">,Grp</code></span></span></div> </div> <br> So, the idea of this solution is to group consecutive ranges first using <a href="http://msdn.microsoft.com/en-us/library/ms186734.aspx"> ROW_NUMBER()</a> function and then apply aggregate functions based on that group identifier.<br> <br> Note, it is easy to modify this query to work with different formats of the voucher number (say, some combinations of letters following by any length number). This article is concentrating on the problem posted by the thread originator and is solving it for the particular voucher number format. You may want to see some modifications of my solution suggested by <a href="http://social.msdn.microsoft.com/profile/pituach/?ws=usercard-mini">Ronen Ariely</a> in the original thread. <br> <hr> <h1><a name="See_Also"></a>See Also</h1> <ul> <li><a href="http://social.technet.microsoft.com/wiki/contents/articles/17785.sql-server-query-language-transact-sql.aspx">SQL Server Query Language - Transact-SQL</a> </li><li>[[T-SQL Useful Links]] </li></ul> <hr> <br> <span style="background-color:#ffffff; font-family:'Segoe UI','Lucida Grande',Verdana,Arial,Helvetica,sans-serif; color:#2a2a2a"><br> This entry participated in the <a href="http://social.technet.microsoft.com/wiki/contents/articles/18211.technet-guru-contributions-july-2013.aspx" style="color:#00749e; outline:none">Technology Guru TechNet WiKi for July</a> contest and <a href="http://blogs.technet.com/b/wikininjas/archive/2013/08/11/technet-guru-awards-july-2013.aspx"> won </a>the gold prize.</span><br> </body> </html>
Comment
Tags
Please add 7 and 7 and type the answer here:

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?