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.