SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Broken Sequence in Column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pattonjo
Starting Member

USA
11 Posts

Posted - 06/05/2013 :  10:55:30  Show Profile  Reply with Quote
I have a table that has rows by PartNo for each step (ex. 10, 20, 30, etc.) How can I query this to only return PartNos that have a BROKEN sequence (ex. Block #2 and #3 below)

PartNo StepNo
PartABC 10
PartABC 20
PartABC 30

PartDEF 10
PartDEF 20
PartDEF 40

PartGHI 10
PartGHI 30
PartGHI 60

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 06/05/2013 :  11:32:27  Show Profile  Reply with Quote
Is the numbering always starting with ten and incrementing by ten if there are no breaks in the sequence? If so
SELECT * FROM PartsTable a
WHERE PartNo IN 
( 
	SELECT b.PartNo FROM PartsTable b
	GROUP BY b.PartNo
	HAVING COUNT(*)*10 < MAX(StepNo)
);
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 06/08/2013 :  03:44:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No, it is not. What about the sequence {20, 20, 20}

Apply proper math! Or use ROW_NUMBER() function.
DECLARE	@Sample TABLE
	(
		PartNo CHAR(7) NOT NULL,
		StepNo TINYINT NOT NULL
	);

INSERT	@Sample
	(
		PartNo,
		StepNo
	)
VALUES	('PartABC', 10),
	('PartABC', 20),
	('PartABC', 30),
	('PartDEF', 10),
	('PartDEF', 20),
	('PartDEF', 40),
	('PartGHI', 20),
	('PartGHI', 20),
	('PartGHI', 20);

-- Applied Math
SELECT		PartNo
FROM		@Sample
WHERE		StepNo > 0
GROUP BY	PartNo
HAVING		NOT	(
				10 * COUNT(*) * (COUNT(*) - 1) = SUM(StepNo)
				AND MIN(StepNo) = 10
			)

-- ROW_NUMBER()
;WITH cteSource(Yak, StepNo, PartNo)
AS (
	SELECT	10 * ROW_NUMBER() OVER (PARTITION BY PartNo ORDER BY StepNo) AS Yak,
		StepNo,
		PartNo
	FROM	@Sample
)
SELECT DISTINCT PartNo
FROM		cteSource
WHERE		Yak <> StepNo



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

422 Posts

Posted - 06/08/2013 :  05:03:04  Show Profile  Reply with Quote
SwePerso's rownumber answer is the correct solution (nice one SwePerso).
But I couldn't wrap my head around the formula in "Applied math" sql. Ex. having partno "PartABC" with stepno 10,20,30,40 would not work, so I allowed myself editing the formula (sorry SwePerso):
quote:
Originally posted by SwePeso

No, it is not. What about the sequence {20, 20, 20}

Apply proper math! Or use ROW_NUMBER() function.
DECLARE	@Sample TABLE
	(
		PartNo CHAR(7) NOT NULL,
		StepNo TINYINT NOT NULL
	);

INSERT	@Sample
	(
		PartNo,
		StepNo
	)
VALUES	('PartABC', 10),
	('PartABC', 20),
	('PartABC', 30),
	('PartDEF', 10),
	('PartDEF', 20),
	('PartDEF', 40),
	('PartGHI', 20),
	('PartGHI', 20),
	('PartGHI', 20);

-- Applied Math
SELECT		PartNo
FROM		@Sample
WHERE		StepNo > 0
GROUP BY	PartNo
HAVING		NOT	(
				10 * COUNT(*) * (COUNT(*) - 1) (count(*)+1)*count(*)/2 = SUM(StepNo)
				AND MIN(StepNo) = 10
			)

-- ROW_NUMBER()
;WITH cteSource(Yak, StepNo, PartNo)
AS (
	SELECT	10 * ROW_NUMBER() OVER (PARTITION BY PartNo ORDER BY StepNo) AS Yak,
		StepNo,
		PartNo
	FROM	@Sample
)
SELECT DISTINCT PartNo
FROM		cteSource
WHERE		Yak <> StepNo



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


Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/08/2013 :  16:43:39  Show Profile  Reply with Quote
"Applied math" query still will not work on the following data:
('PartAAI', 10),
('PartAAI', 10),
('PartAAI', 40);

Edited by - MuMu88 on 06/08/2013 17:02:13
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

422 Posts

Posted - 06/08/2013 :  18:44:40  Show Profile  Reply with Quote
SwePerso's "row number" solution, it the most elegant. But just for the fun of it, I think this might work:

select s1.partno
  from @sample as s1
       inner join (select partno
                         ,stepno
                         ,count(*)-1 as c
                     from @sample
                    group by partno
                            ,stepno
                  ) as s2
               on s2.partno=s1.partno
 where s1.stepno>0
 group by s1.partno
 having not (10*(count(*)+1)*count(*)/2=sum(s1.stepno)
         and sum(s1.stepno%10)=0
         and sum(s2.c)=0
            )

Edited by - bitsmed on 06/08/2013 18:45:46
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/08/2013 :  21:03:05  Show Profile  Reply with Quote
Yes with a minor correction:
quote:
Originally posted by bitsmed

SwePerso's "row number" solution, it the most elegant. But just for the fun of it, I think this might work:

select s1.partno
  from @sample as s1
       inner join (select partno
                         ,stepno
                         ,count(*)-1 as c
                     from @sample
                    group by partno
                            ,stepno
                  ) as s2
               on s2.partno=s1.partno and s2.stepno=s1.stepno
 where s1.stepno>0
 group by s1.partno
 having not (10*(count(*)+1)*count(*)/2=sum(s1.stepno)
         and sum(s1.stepno%10)=0
         and sum(s2.c)=0
            )


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.39 seconds. Powered By: Snitz Forums 2000