Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by (eMicrosoft Partne)
  • When: 7 Jul 2013 3:27 PM
  • Last revision by (eMicrosoft Partne)
  • When: 11 Sep 2013 11:52 AM
  • Revisions: 17
  • Comments: 18
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  T-SQL: Gaps and Islands Problem

T-SQL: Gaps and Islands Problem

T-SQL: Gaps and Islands Problem

This article will consider a simple classical Gaps & Islands problem asked recently in Transact-SQL Forum at MSDN with non original title "Query Help".

Table of Contents



Problem Definition


The thread originator was kind enough to provide DDL of the table and some data to describe the task:

Create table T1
(Id int identity primary key,
VoucherNo varchar(4),
TransNo varchar(10)
)
 
Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns1')

And he also provided the desired output:

TransNo  FirstVoucher  LastVoucher  Quantity 
 Trns1 V100 V104 5
 Trns1 V106 V106 1

The problem is to find consecutive vouchers (100-104, then 106).

Solution


As mentioned, this is a common problem in Transact SQL and it was described by Itzik Ben Gan here or by Plamen Ratchev in this easy to understand blog post
Refactoring Ranges. 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):

;WITH cte
AS (
    SELECT *
        ,CAST(SUBSTRING(VoucherNo, 2, 3) AS INT) - ROW_NUMBER() OVER (
            ORDER BY VoucherNo
            ) AS Grp
    FROM T1
    )
SELECT TransNo
    ,min(VoucherNo) AS FirstVoucherNo
    ,max(VoucherNo) AS LastVoucherNo
    ,count(*) AS Quantity
FROM cte
GROUP BY TransNo
    ,Grp

So, the idea of this solution is to group consecutive ranges first using ROW_NUMBER() function and then apply aggregate functions based on that group identifier.

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 Ronen Ariely in the original thread. 

See Also



This entry participated in the Technology Guru TechNet WiKi for July contest and won the gold prize.
, , , , , , , , , , , , , [Edit tags]
Leave a Comment
  • Please add 8 and 2 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 4 Sep 2013 3:12 PM

    Naomi  N edited Revision 14. Comment: Added See Also

  • 19 Jul 2013 7:34 AM

    Naomi  N edited Revision 5. Comment: Minor edit

  • 18 Jul 2013 4:06 PM

    Naomi  N edited Revision 4. Comment: Minor edit

  • 7 Jul 2013 8:07 PM

    Naomi  N edited Revision 3. Comment: Minor grammar corrections

  • 7 Jul 2013 4:37 PM

    Naomi  N edited Revision 2. Comment: Minor grammar corrections

  • 7 Jul 2013 3:34 PM

    Naomi  N edited Revision 1. Comment: Title case

  • 7 Jul 2013 3:32 PM

    Naomi  N edited Original. Comment: Links

Page 1 of 1 (7 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
Page 1 of 2 (18 items) 12