Find gaps and dups per product query

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

General SQL Server Forums

New to SQL Server Programming

Find gaps and dups per product query

Author

Topic

nnogueira
Starting Member

18 Posts

Posted - 2009-05-16 : 11:47:45

I need find a way to get integer gaps and dup SequentialNumbers (Integers) per products(Nchar´s).

Example:

PNum SeqNum
100 | 1
100 | 2
100 | 4
100 | 5
200 | 1
200 | 2
200 | 3

In this case, missed number 3 for product 100.

And i need find dupped SequentialNumbers Like:

PNum SeqNum
100 | 1
100 | 2
100 | 3
100 | 3
100 | 4
200 | 1
200 | 2
200 | 3

In this case, number 3 has two times

Someome can help me ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-16 : 12:15:21

SELECT p.SeqNum, v.Number AS SeqNum
FROM (SELECT PNum, MAX(SeqNum) AS SeqNum FROM Table1 GROUP BY PNum) AS p
INNER JOIN master..spt_values AS v ON v.Type = 'P' AND Number BETWEEN 1 AND p.SeqNum
LEFT JOIN Table1 AS t1 ON t1.PNum = p.PNum AND t1.SeqNum = v.Number
WHERE t1.PNum IS NULL

E 12°55'05.63"
N 56°04'39.26"

nnogueira
Starting Member

18 Posts

Posted - 2009-05-16 : 12:58:37

quote:
Originally posted by Peso

SELECT p.SeqNum, v.Number AS SeqNum
FROM (SELECT PNum, MAX(SeqNum) AS SeqNum FROM Table1 GROUP BY PNum) AS p
INNER JOIN master..spt_values AS v ON v.Type = 'P' AND Number BETWEEN 1 AND p.SeqNum
LEFT JOIN Table1 AS t1 ON t1.PNum = p.PNum AND t1.SeqNum = v.Number
WHERE t1.PNum IS NULL

E 12°55'05.63"
N 56°04'39.26"

Thanks Peso, it help me a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 03:38:27

[code]
CREATE TABLE #TALLY
(Number INT,
CONSTRAINT PK_Tally_ Number PRIMARY KEY CLUSTERED (Number))

DECLARE @MaxLimit int,@Start int
SELECT @MaxLimit=MAX(SeqNum),@Start=1
FROM YourTable

WHILE @Start <=@MaxLimit
BEGIN
INSERT INTO #TALLY(Number)
SELECT @Start
SET @Start=@Start+1
END

SELECT PNum,MAX(SeqNum) AS Limit
INTO #MAXSEQ
FROM YourTable
GROUP BY PNum

SELECT m.PNum,m.Number
FROM
(
SELECT ms.PNum,t.Number
FROM #MAXSEQ ms
JOIN #TALLY t
ON t.Number BETWEEN 1 AND ms.Limit
)m
LEFT JOIN YourTable tbl
ON tbl.PNum=m.PNum
AND tbl.SeqNum=m.Number
GROUP BY m.PNum,m.Number
HAVING COUNT(tbl.PNum)<>1
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-17 : 04:14:41

For the dupes cases, add a DISTINCT keyword to my suggestion.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources