find part# the same but rev different (subquery? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

find part# the same but rev different (subquery?

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-06-21 : 11:17:48

Morning,

I've been asked to find out in our part number convention which part numbers are the same but the rev #'s are different.

I've got this so far:


select material,LEFT(material, PATINDEX('% R%', material))PartNumber,
SUBSTRING(material,PATINDEX('% R%', material),20)Rev
from Material
where Material like '%REV%'
order by Material

That gives me this:


0032460-000 REV AE	0032460-000 	 REV AE
0050466-000 REV AJ	0050466-000 	 REV AJ
0110115-000 REV AE	0110115-000 	 REV AE
0110115-000 REV AF	0110115-000 	 REV AF
0110116-000 REV AE	0110116-000 	 REV AE
0110116-000 REV AF	0110116-000 	 REV AF
054-716800 REV C	054-716800 	 REV C
10010017 REV A	10010017 	 REV A
10010026 REV A	10010026 	 REV A
10020001 REV A	10020001 	 REV A
10020002 REV A	10020002 	 REV A
10020003 REV 2	10020003 	 REV 2
10020003 REV A	10020003 	 REV A
10020004 REV 1	10020004 	 REV 1
10020004 REV A	10020004 	 REV A
10020005 REV 2	10020005 	 REV 2
10020005 REV A	10020005 	 REV A
10020006 REV 1	10020006 	 REV 1

I think there is going to be some kind of subquery involved but any thoughts would be great.

Thanks

Laura

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-06-21 : 11:50:07

What are the desired results? Are you trying to eliminate the part numbers that only have a single rev?

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-21 : 11:54:19

[code]
select m.* from Material m
join
(
select LEFT(material, PATINDEX('% R%', material)) PartNumber
from Material
where Material like '%REV%'
group by LEFT(material, PATINDEX('% R%', material))
having count(*) > 1
)dt
on dt.PartNumber = LEFT(m.material, PATINDEX('% R%', m.material))
order by m.Material[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-06-21 : 12:02:29

Nice. I had worked out something similar.


select distinct list1.Part# from  (
				select LEFT(a.material, PATINDEX('% R%', a.material))Part#,
				SUBSTRING(a.material,PATINDEX('% R%',a.material),20)Rev#
				from Material a
				where a.Material like '%REV%') list1
inner join (select LEFT(a.material, PATINDEX('% R%', a.material))Part#,
				SUBSTRING(a.material,PATINDEX('% R%',a.material),20)Rev#
				from Material a
				where a.Material like '%REV%') list2
on list1.Part# = list2.Part#
where list1.Rev# <> list2.Rev#
order by list1.Part#

But your is more elegant.

Thanks
Laura

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-21 : 12:04:55

No, you're never too old to Yak'n'Roll if you're too young to die.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-06-21 : 16:09:07

What are the desired results? Are you trying to eliminate the part numbers that only have a single rev?

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-22 : 01:45:51

The desired result was
Show material numbers with occurrence > 1 but with different revs.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources