SQL Query - SQL Server Forums

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)

SQL Query

Author

Topic

scott_hanebutt
Starting Member

10 Posts

Posted - 2009-05-14 : 08:06:08

I have a table that contains two fields.

FPARTNO – unique part number
FADJUST1 – price of part

My part numbers contain two types of parts, the 21 and 23 series. The part numbers in each series match each other except for the series number.

For example these are some of my part numbers
TM2172L-5ML-P - 21 series
TM2372L-5ML-P - 23 series
UM216630R-EHFM-3L-P - 21 series
UM236630R-EHFM-3L-P - 23 series

The 21 series part should always cost the same or less then the corresponding 23 series part.

How can I write a query that would check this and then give me a list of parts where this was not true?
What query would give me a list of parts that did not have a corresponding part in the other series?

Thank you,
Scott

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-14 : 09:33:08

Try this

select FPARTNO from your_table
Group by FPARTNO
Having min(FADJUST1)='21 series' and max(FADJUST1)='21 series'

Madhivanan

Failing to plan is Planning to fail

scott_hanebutt
Starting Member

10 Posts

Posted - 2009-05-14 : 09:56:44

Thanks for the reply.

I made a few typo’s in my post.

The table name is INPRSC

Fields:
FSCHECODE – is the field name for the unique part number, not FPARTNO
FADJUST1 – price of part

The example part numbers I listed do not contain the word ‘series’. I but the series name after the part number so you would know what series they are in. The following are examples of part numbers.

TM2172L-5ML-P
TM2372L-5ML-P
UM216630R-EHFM-3L-P
UM236630R-EHFM-3L-P
PCUMBT2172LC-EHFP-4L
PCUMBT2372LC-EHFP-4L

I tried modifying your query to the following two but neither one returned any results.

select FSCHECODE from INPRSC
Group by FSCHECODE
Having min(FADJUST1)='23' and max(FADJUST1)='21'

select FSCHECODE from INPRSC
Group by FSCHECODE
Having min(FADJUST1)='21' and max(FADJUST1)='23'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-14 : 09:59:58

select FSCHECODE from INPRSC
Group by FSCHECODE
Having min(FADJUST1)='21' and max(FADJUST1)='21'

Madhivanan

Failing to plan is Planning to fail

scott_hanebutt
Starting Member

10 Posts

Posted - 2009-05-14 : 10:12:43

I tried your query and got no results.

Is your query to answer my first or second question?

I am not sure what your query is doing. It seems to be grouping by the part number, but the part number is always unique because of '21' or '23' being incorporated into the part number.

yonabout
Posting Yak Master

112 Posts

Posted - 2009-06-11 : 11:11:33

Hi,

First off, it doesn't look like your database design is set up to deal with this query at all.

I've come up with something that might work, but its really dodgy.

Basically I created a couple of temp tables with the parts code, price and a 'newcode' column, one for the 21 codes and one for the 23 codes.

I selected all the 21 codes into the #21s table and all the 23 codes into the #23s table, and stripped out the 21 and 23 values from the part code and stuck that in the newcode column.

Once you've got that, the values in the newcode column should be the same in both the temp tables so you can use it as a key to join the tables and do comparisons.

You may have issues with this if 21 or 23 appears more than once in a part number, and If possible I'd recommend revisiting your database design, but here's the code anyway, it might help.

create table #temp (FSCHECODE varchar(50), FADJUST1 decimal(10,2)) 
insert into #temp
select 'TM2172L-5ML-P', 50.00 union
select 'TM2372L-5ML-P', 10.00 union
select 'UM216630R-EHFM-3L-P', 10.00 union
select 'UM236630R-EHFM-3L-P', 10.00 union
select 'PCUMBT2172LC-EHFP-4L', 10.00 


create table #21s (FSCHECODE varchar(50), Newcode varchar(50), FADJUST1 decimal(10,2)) 

insert into #21s
select
FSCHECODE,
replace(FSCHECODE,'21', ''),
FADJUST1
from #temp 
where FSCHECODE like '%21%'

create table #23s (FSCHECODE varchar(50), Newcode varchar(50), FADJUST1 decimal(10,2)) 
insert into #23s
select
FSCHECODE,
replace(FSCHECODE,'23', ''),
FADJUST1
from #temp 
where FSCHECODE like '%23%'

--gets parts where the 21 version costs more than the 23 version

select
#21s.fschecode,
#21s.fadjust1
from 
#21s join #23s
on #21s.newcode = #23s.newcode
where #21s.fadjust1 > #23s.fadjust1 

--gets parts where there is a 21 version but no 23 version

select
#21s.fschecode,
#23s.fschecode,
#21s.fadjust1
from 
#21s left join #23s
on #21s.newcode = #23s.newcode
where #23s.fschecode is null


drop table #21s
drop table #23s
drop table #temp

Cheers,

Yonabout

scott_hanebutt
Starting Member

10 Posts

Posted - 2009-06-11 : 13:20:26

Thanks for the Post. I modified your sugestion and got it to work. Below is the query I used.

SELECT LINE21.PART
FROM (SELECT REPLACE(REPLACE(RTRIM(INPRSC.FSCHECODE),'21','-LINE-'),'-P','-MATERIAL') AS PART, INPRSC.FADJUST1 AS Z1
FROM INPRSC, INMAST
WHERE INPRSC.FSCHECODE = INMAST.FPARTNO
AND INMAST.FPRODCL = '21'
AND INMAST.FCSTSCODE = 'A') AS LINE21,
(SELECT REPLACE(REPLACE(RTRIM(INPRSC.FSCHECODE),'23','-LINE-'),'-W','-MATERIAL') AS PART, INPRSC.FADJUST1 AS Z1
FROM INPRSC, INMAST
WHERE INPRSC.FSCHECODE = INMAST.FPARTNO
AND INMAST.FPRODCL = '23'
AND INMAST.FCSTSCODE = 'A') AS LINE23
WHERE LINE21.PART = LINE23.PART
AND LINE21.Z1 > LINE23.Z1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources