help with converting output data to series

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 2000 Forums

SQL Server Development (2000)

help with converting output data to series

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2009-08-31 : 16:43:45

declare @test table
(prodid int, serialno int)

insert into @test
select 1, 2220 union all
SELECT 1, 2221 UNION ALL
SELECT 1, 2222 UNION ALL
SELECT 1, 2223 UNION ALL
SELECT 1, 2224 UNION ALL
SELECT 1, 3331 UNION ALL
SELECT 1, 3332 UNION ALL
SELECT 1, 3333 UNION ALL
SELECT 2, 4442 UNION ALL
SELECT 2, 4443 UNION ALL
SELECT 2, 4444 UNION ALL
SELECT 2, 4445 UNION ALL
SELECT 2, 4446 UNION ALL
SELECT 2, 8776 UNION ALL
SELECT 2, 8777 UNION ALL
SELECT 3, 9995 UNION ALL
SELECT 3, 9996 UNION ALL
SELECT 3, 9997 UNION ALL
SELECT 3, 445 UNION ALL
SELECT 3, 446 UNION ALL
SELECT 3, 447

SELECT * FROM @TEST

ProdID, SerialNoFrom, SerialNoTo
1 2220 2224
1 3331 3333
2 4442 4446
2 8776 8777
3 9995 9997
3 445 447

so in the serialnofrom we should have Minimum serial number and in serialnoto we should have maximum, but only for the serial numbers
which are consective and have a difference of 1. as soon as the series breaks a new row should be created and
have the new minimum serial number and maximum serial number.

-----------------------------------------------------------------------------------------------
Ashley Rhodes

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-09-01 : 00:18:22

Hi, Try this once

SELECT PRODID,MIN(SERIALNO) AS SerialNoFrom, MAX(SERIALNO) AS SerialNoTo FROM
(
SELECT PRODID,
ROW_NUMBER() OVER ( PARTITION BY LEFT(SERIALNO,2),LEN(SERIALNO) ORDER BY SERIALNO ) AS RID,
SERIALNO
FROM @TEST
) T
GROUP BY LEFT(SERIALNO,2),LEN(SERIALNO),PRODID
ORDER BY PRODID

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-01 : 03:19:15

If I add
SELECT 1, 2298 UNION ALL
SELECT 1, 2299 UNION ALL
SELECT 1, 2300 UNION ALL
these records to above data,
Then above query is giving result as
1, 2220, 2299
1, 2300, 2300
1, 3331, 3333
2, 4442, 4446
2, 8776, 8777
3, 445, 447
3, 9995, 9997.

which is not as expected

Rahul Shinde

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-01 : 03:52:02

SELECT PRODID, MIN(SERIALNO) AS MINSNO, MAX(SERIALNO) AS MAXSNO
FROM
(
SELECT (ROW_NUMBER() OVER (ORDER BY PRODID, SERIALNO)+1)/2 AS RN, PRODID, SERIALNO
FROM
(
SELECT * FROM @TEST WHERE SERIALNO NOT IN ( SELECT SERIALNO + 1 FROM @TEST)
UNION
SELECT * FROM @TEST WHERE SERIALNO NOT IN ( SELECT SERIALNO - 1 FROM @TEST)
) T
) TB GROUP BY PRODID, RN

Rahul Shinde

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources