Author 
Topic 

pattonjo
Starting Member
USA
11 Posts 
Posted  06/05/2013 : 10:55:30

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
3873 Posts 
Posted  06/05/2013 : 11:32:27

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


SwePeso
Patron Saint of Lost Yaks
Sweden
30421 Posts 
Posted  06/08/2013 : 03:44:40

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" 


bitsmed
Aged Yak Warrior
545 Posts 
Posted  06/08/2013 : 05:03:04

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"



MuMu88
Aged Yak Warrior
549 Posts 
Posted  06/08/2013 : 16:43:39

"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 


bitsmed
Aged Yak Warrior
545 Posts 
Posted  06/08/2013 : 18:44:40

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 


MuMu88
Aged Yak Warrior
549 Posts 
Posted  06/08/2013 : 21:03:05

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
)




Topic 


