Get list of ranges of consecutive numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get list of ranges of consecutive numbers

Author

Topic

MoDee
Starting Member

2 Posts

Posted - 2011-09-29 : 13:29:53

Hi!

I'm working with a MS Access (2010) database on a SQL Server 2005. I'm trying to solve as much as I can in T-SQL at the server before I work in Access-SQL or in VBA, although I'm no SQL expert.

I hoped to solve the following problem in T-SQL, but couldn't work out a solution (despite getting help from the "SQL Cookbook" and lots of forums across the net). Do I really have to work it out in VBA?

From a query I get results like:

PartA    PartB    SN
1        2        20
1        2        21
1        2        22
1        2        23
1        2        29
1        2        30
1        2        31
1        5        24
1        5        25
1        5        26
2        2        27
2        2        32

So, there are groups of serial numbers (SN) containing the same part types A and B. The SNs with the same PartA/PartB combination do not have to be consecutive. There may be spaces in the SN list.

I'm trying to get this output:

PartA    PartB    SNfrom   SNto
1        2        20       23
1        2        29       31
1        5        24       26
2        2        27       27
2        2        32       32

I'm asking for your help on this - I don't want VBA to win!

TIA & regards,
MoDee

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-09-29 : 14:46:53

Visakh will come along and clean this up a bit, but never let VBA win!

DECLARE @Table Table(partA tinyint,PartB tinyint,SN tinyint)

INSERT INTO @Table(PartA , PartB , SN)
SELECT 1 , 2 , 20 UNION ALL
SELECT 1 , 2 , 21 UNION ALL
SELECT 1 , 2 , 22 UNION ALL
SELECT 1 , 2 , 23 UNION ALL
SELECT 1 , 2 , 29 UNION ALL
SELECT 1 , 2 , 30 UNION ALL
SELECT 1 , 2 , 31 UNION ALL
SELECT 1 , 5 , 24 UNION ALL
SELECT 1 , 5 , 25 UNION ALL
SELECT 1 , 5 , 26 UNION ALL
SELECT 2 , 2 , 27 UNION ALL
SELECT 2 , 2 , 32

;with seq
as
(
select PartA,PartB,SN,[Row] = SN-row_number() over(partition by PartA,partB order by SN)
from @table
)
,
grp as
(
select row ,[SNFrom] = min(sn),[SNTo] = max(sn) from seq
group by row
)

select t.PartA,t.PartB,grp.SNFrom,grp.SNTo
FROM @Table t
INNER JOIN grp ON t.sn = grp.snfrom

Jim

Everyday I learn something that somebody else already knew

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-09-29 : 16:03:28

group by row

Should be : group by parta, partb, row

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-29 : 17:10:08

quote:
Originally posted by namman

group by row

Should be : group by parta, partb, row

Not necessary in this case.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-09-29 : 23:23:00

So, there are groups of serial numbers (SN) containing the same part types A and B. The SNs with the same PartA/PartB combination do not have to be consecutive. There may be spaces in the SN list.

With the bold requirement, if the GROUP BY not including parta and partb, output data will be seriously wrong.

MoDee
Starting Member

2 Posts

Posted - 2011-09-30 : 04:54:37

Hallelujah, praise the jimf!

I will need to take 2 days off to understand your code

(didn't use windowing functions up to now), but it does exactly* what I was searching for!

Maybe then I can also answer why I get the same results using different GROUP BY clauses (with or without "PartA, PartB").

Thank you very much!
MoDee

* well, except for the case insensitive column names

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-30 : 09:36:52

quote:
Originally posted by namman

So, there are groups of serial numbers (SN) containing the same part types A and B. The SNs with the same PartA/PartB combination do not have to be consecutive. There may be spaces in the SN list.

With the bold requirement, if the GROUP BY not including parta and partb, output data will be seriously wrong.

Take as closer look at Jim's code. Or, even better, run it. The partition by handles the grouping initially, then he groups by row_number in the final output. It works.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-09-30 : 11:54:00

(Assume SN is unique)

Jim creates row by row_number and SN, with partition by combination of parta and partb (lest's call AB). So row can be the same for different AB.

Later he group by row to find min(SN) and max(SN) for AB. By that way, SN in AB1 may become Max in AB2 and records may be missing. It should group by parta, partb, row.

Jim's solution work fine with the sample data, but not the requirement. The real data may be different, then it will return wrong result.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources