# Query help

### Question

• Hi,
I need help in getting this type of output:

Create table T1
(Id int identity primary key,
VoucherNo varchar(10),
TransNo varchar(10)
)

Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns1')

Resultant output:
TransNo FirsvoucherNo  LastVoucherNo Quantity
trans1    V100     V104  5
trans1   V106      V106   1

Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

Saturday, June 29, 2013 10:12 AM

• This is a 'Gaps & Islands' problem. See this solution:

```;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```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Sunday, June 30, 2013 5:37 AM
• I feel kind of bad to comment it as this already been mark as the answer and used as a base for WIKI, but actually Neomi's query does not work if the value number is less then 100 or more then 999 (In other words, if the number of characters is different from 3)

Explanation and Working Query:

```/****************** DDL+DML */
Create table T1
(Id int identity primary key,
VoucherNo varchar(10),
TransNo varchar(10)
)

Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns1')

select * from T1

/****************** Very Limited query: will work only if the vnumbers have exactly 3 characters */
;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

/*
there is a mistake in the desired output on the WIKI:
TransNo 	FirstVoucher 	LastVoucher 	Quantity
trans1	V100	V104	5
trans2	V106	V106	1

last row should be
trans1	V106	V106	1
*/

/*
why the query is Very Limited and how to fix it:
-------------------------------------------------
1. using "SUBSTRING(VoucherNo, 2, 3)" is hardcoded for numbers with exactly 3 charators but what if we get:
Insert into T1 values ('V1000','Trns1')

Naomi's query output will be:
Trns1	V1000	V104	5
Trns1	V100	V106	2

This is wrong!

2. using "ORDER BY VoucherNo" inside the OVER clue is incurrect as this is using order by a String type and not number type.
if we get the value v10 and the value v9 then the order by as string will say v9 > v10 but we want to order by as numbers and get v9 < v10. try to add:
Insert into T1 values ('V9','NotWorkin'), ('V10','NotWorkin')

Naomi's query output will be:
NotWorkin	V9	V9	1
NotWorkin	V10	V10	1
Trns1	V1000	V104	5
Trns1	V100	V106	2

This is wrong!
The currect output that we want is:
NotWorkin	v9	v10	2
Trns1	v100	v104	5
Trns1	v106	v106	1
Trns1	v1000	v1000	1

the sulotion is to use numbers.

*/

-- if the VoucherNo column always use the format of v[number] then we just need to clean the first char and get the number.
-- if the format is more complex and it is combine with text and then number [text][number], then we can clean any char that is not a number
-- and the format can be more complax...

-- let's deal with the format v[number] for now and we will fix the query a bit to become more flexible (to use numbers that do not have 3 char exacly as the original question was)

;WITH
cte01 AS (
SELECT id, TransNo, CAST(SUBSTRING(VoucherNo, 2, LEN(VoucherNo) - 1) AS INT) AS NumberVoucherNo
FROM T1
),
cte02 as (
select id, TransNo, NumberVoucherNo, (NumberVoucherNo - ROW_NUMBER() over (order by NumberVoucherNo)) as Grp
from cte01
)
select
TransNo
, 'v' + CAST(MIN(NumberVoucherNo) as varchar(10)) AS FirstVoucherNo
,'v' + CAST(MAX(NumberVoucherNo) as varchar(10)) AS LastVoucherNo
,COUNT(*) AS Quantity
-- or the "Quantity" results we can use this
-- , (MAX(NumberVoucherNo) - MIN(NumberVoucherNo) + 1)
from cte02
GROUP BY TransNo, Grp

/****************** Clean */
drop table T1
go
```
* Note: I write the query in such a way so that it will be clear to understand. You can write the query in a better way without using two CTE's tables.

Wednesday, July 17, 2013 9:59 AM
• Hi Kapil,

Can you try this:

```SELECT TempID,TransNo,MIN(VoucherNo) AS FirsvoucherNo,MAX(VoucherNo) AS LastVoucherNo,COUNT(TempID) AS Quantity
FROM
(
SELECT *
,CASE WHEN EXISTS (SELECT TOP 1 VoucherNo FROM T1 tmp2 where RIGHT(tmp2.VoucherNo,3) - 1 = RIGHT(tmp.VoucherNo,3) OR RIGHT(tmp2.VoucherNo,3) + 1 = RIGHT(tmp.VoucherNo,3))
THEN 1 ELSE 0 END AS TempID
FROM T1 tmp
) TEST
GROUP BY TempID,TransNo
ORDER BY TempID DESC```

Regards Harsh

Saturday, June 29, 2013 11:05 AM

### All replies

• Hi Kapil,

As per your query you need to identify the FirsvoucherNo and LastVoucherNo, which not there.

You can try this if it works for you:

```SELECT TransNo,
MIN(VoucherNo) AS FirsvoucherNo,
MAX(VoucherNo) AS LastVoucherNo
FROM T1
GROUP BY TransNo```

Regards Harsh

Saturday, June 29, 2013 10:20 AM
• Hi Kapil,

As per your query you need to identify the FirsvoucherNo and LastVoucherNo, which not there.

You can try this if it works for you:

```SELECT TransNo,
MIN(VoucherNo) AS FirsvoucherNo,
MAX(VoucherNo) AS LastVoucherNo
FROM T1
GROUP BY TransNo```

Regards Harsh

I already tried with MIN  MAX function it will not working with that..

it will give output as

Trns1 V100 V106

Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

Saturday, June 29, 2013 10:23 AM
• Hi Kapil,

You will not get trans2 because you do not have a value "Trans2" for TransNo in the table T1.

I have tired this query which give exact result what you want by changing the last insert value from trans1 to trans2.

```Create table T1
(Id int identity primary key,
VoucherNo varchar(10),
TransNo varchar(10)
)

Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns2')

SELECT TransNo,
MIN(VoucherNo) AS FirsvoucherNo,
MAX(VoucherNo) AS LastVoucherNo,
COUNT(VoucherNo) AS Quantity
FROM T1
GROUP BY TransNo```

Regards Harsh

Saturday, June 29, 2013 10:36 AM
• Hi Kapil,

You will not get trans2 because you do not have a value "Trans2" for TransNo in the table T1.

I have tired this query which give exact result what you want by changing the last insert value from trans1 to trans2.

```Create table T1
(Id int identity primary key,
VoucherNo varchar(10),
TransNo varchar(10)
)

Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns2')

SELECT TransNo,
MIN(VoucherNo) AS FirsvoucherNo,
MAX(VoucherNo) AS LastVoucherNo,
COUNT(VoucherNo) AS Quantity
FROM T1
GROUP BY TransNo```

Regards Harsh

Sorry, that was not trans2  I have changed that to Trans1...

After running your query I am getting this output:

Trns1 V100 V106 6

which does not match with my desired output

Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

Saturday, June 29, 2013 10:41 AM
• Or if you want work just with the number and if you have a costant V before the number you can use:

`SELECT max(RIGHT(VoucherNo,3)) AS maxNU, min(RIGHT(VoucherNo,3)) AS minNU, TransNo FROM T1 group by TransNo`

Saturday, June 29, 2013 10:45 AM
• Hi Kapil,

Can you try this:

```SELECT TempID,TransNo,MIN(VoucherNo) AS FirsvoucherNo,MAX(VoucherNo) AS LastVoucherNo,COUNT(TempID) AS Quantity
FROM
(
SELECT *
,CASE WHEN EXISTS (SELECT TOP 1 VoucherNo FROM T1 tmp2 where RIGHT(tmp2.VoucherNo,3) - 1 = RIGHT(tmp.VoucherNo,3) OR RIGHT(tmp2.VoucherNo,3) + 1 = RIGHT(tmp.VoucherNo,3))
THEN 1 ELSE 0 END AS TempID
FROM T1 tmp
) TEST
GROUP BY TempID,TransNo
ORDER BY TempID DESC```

Regards Harsh

Saturday, June 29, 2013 11:05 AM
• This is a 'Gaps & Islands' problem. See this solution:

```;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```

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Sunday, June 30, 2013 5:37 AM
• I feel kind of bad to comment it as this already been mark as the answer and used as a base for WIKI, but actually Neomi's query does not work if the value number is less then 100 or more then 999 (In other words, if the number of characters is different from 3)

Explanation and Working Query:

```/****************** DDL+DML */
Create table T1
(Id int identity primary key,
VoucherNo varchar(10),
TransNo varchar(10)
)

Insert into T1 values ('V100','Trns1'),('V101','Trns1'),('V102','Trns1'),('V103','Trns1'),('V104','Trns1'),('V106','Trns1')

select * from T1

/****************** Very Limited query: will work only if the vnumbers have exactly 3 characters */
;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

/*
there is a mistake in the desired output on the WIKI:
TransNo 	FirstVoucher 	LastVoucher 	Quantity
trans1	V100	V104	5
trans2	V106	V106	1

last row should be
trans1	V106	V106	1
*/

/*
why the query is Very Limited and how to fix it:
-------------------------------------------------
1. using "SUBSTRING(VoucherNo, 2, 3)" is hardcoded for numbers with exactly 3 charators but what if we get:
Insert into T1 values ('V1000','Trns1')

Naomi's query output will be:
Trns1	V1000	V104	5
Trns1	V100	V106	2

This is wrong!

2. using "ORDER BY VoucherNo" inside the OVER clue is incurrect as this is using order by a String type and not number type.
if we get the value v10 and the value v9 then the order by as string will say v9 > v10 but we want to order by as numbers and get v9 < v10. try to add:
Insert into T1 values ('V9','NotWorkin'), ('V10','NotWorkin')

Naomi's query output will be:
NotWorkin	V9	V9	1
NotWorkin	V10	V10	1
Trns1	V1000	V104	5
Trns1	V100	V106	2

This is wrong!
The currect output that we want is:
NotWorkin	v9	v10	2
Trns1	v100	v104	5
Trns1	v106	v106	1
Trns1	v1000	v1000	1

the sulotion is to use numbers.

*/

-- if the VoucherNo column always use the format of v[number] then we just need to clean the first char and get the number.
-- if the format is more complex and it is combine with text and then number [text][number], then we can clean any char that is not a number
-- and the format can be more complax...

-- let's deal with the format v[number] for now and we will fix the query a bit to become more flexible (to use numbers that do not have 3 char exacly as the original question was)

;WITH
cte01 AS (
SELECT id, TransNo, CAST(SUBSTRING(VoucherNo, 2, LEN(VoucherNo) - 1) AS INT) AS NumberVoucherNo
FROM T1
),
cte02 as (
select id, TransNo, NumberVoucherNo, (NumberVoucherNo - ROW_NUMBER() over (order by NumberVoucherNo)) as Grp
from cte01
)
select
TransNo
, 'v' + CAST(MIN(NumberVoucherNo) as varchar(10)) AS FirstVoucherNo
,'v' + CAST(MAX(NumberVoucherNo) as varchar(10)) AS LastVoucherNo
,COUNT(*) AS Quantity
-- or the "Quantity" results we can use this
-- , (MAX(NumberVoucherNo) - MIN(NumberVoucherNo) + 1)
from cte02
GROUP BY TransNo, Grp

/****************** Clean */
drop table T1
go
```
* Note: I write the query in such a way so that it will be clear to understand. You can write the query in a better way without using two CTE's tables.

Wednesday, July 17, 2013 9:59 AM
• Here are a few more solutions and explanations for the Gaps & Islands problem:

http://blogs.msdn.com/b/samlester/archive/2012/09/04/tsql-solve-it-your-way-gaps-and-islands-with-a-twist.aspx

Thanks,
Sam Lester (MSFT)

http://blogs.msdn.com/b/samlester

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Friday, August 02, 2013 9:01 AM