Resources For IT Professionals

# 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".

# 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.

This entry participated in the Technology Guru TechNet WiKi for July contest and won the gold prize.

Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful

• Naomi  N edited Revision 5. Comment: Minor edit

• Naomi  N edited Revision 4. Comment: Minor edit

• Naomi  N edited Revision 3. Comment: Minor grammar corrections

• Naomi  N edited Revision 2. Comment: Minor grammar corrections

• Naomi  N edited Revision 1. Comment: Title case

• 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.