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



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.

Leave a Comment
  • Please add 5 and 4 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Comments
  • Naomi  N edited Original. Comment: Links

  • Naomi  N edited Revision 1. Comment: Title case

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

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

  • Naomi  N edited Revision 4. Comment: Minor edit

  • Naomi  N edited Revision 5. Comment: Minor edit

  • Naomi  N edited Revision 14. Comment: Added See Also

Page 1 of 1 (7 items)
Wikis - Comment List
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Naomi  N edited Original. Comment: Links

  • Naomi  N edited Revision 1. Comment: Title case

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

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

  • Naomi  N edited Revision 4. Comment: Minor edit

  • Naomi  N edited Revision 5. Comment: Minor edit

  • Naomi  N edited Revision 14. Comment: Added See Also

  • Naomi  N edited Revision 15. Comment: Added link

  • Y3T7BWJD55WC

  • Y3T7BWJD55WC

  • Y3T7BWJD55WC

  • Y3T7BWJD55WC

  • Y3T7BWJD55WC

  • Congratulations on winning the gold medal! blogs.technet.com/.../technet-guru-awards-july-2013.aspx

  • Naomi, your article was featured on MSDN blogs here: blogs.msdn.com/.../transact-sql-guru-gaps-and-islands-problem.aspx

    Thank you for all your TechNet Guru contributions!

Page 1 of 2 (18 items) 12