Price Comparison 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)

Price Comparison Query

Author

Topic

scott_hanebutt
Starting Member

10 Posts

Posted - 2009-06-23 : 08:11:33

I work for a furniture manufacturer that makes similar desks in multiple sizes. Often the only difference between the desks are their width or depth. Our part numbers reflect this difference by including the dimension in them.

Table: INPRSC
Field: FSCHECODE - This is a unique part number
Field: FADJUST1 - Price of part

The following are some example part numbers

The following parts are identical except for the width.
D2272L-7MC-P - Has a width of 72 inches
D2266L-7MC-P - Has a width of 66 inches
D2254L-7MC-P - Has a width of 54 inches

The following parts are identical except for the width
CR2272-2MC-P - Has a width of 72 inches
CR2266-2MC-P - Has a width of 66 inches
CR2254-2MC-P - Has a width of 54 inches

I want to compare the prices of the similar desks to make sure that the larger desk always cost at least the as much as the smaller version. When they don’t the query would return the part number of at least one of the two desks. I can then manually figure out what is wrong on a case by case basis.

For example a CR2272-7MC should cost at least as much as CR2266-7MC-P. However a CR2266-7MC-P might cost more or less then the other two because it is a different type of desk.

The number of characters and/or numbers before and after the size dimension very depending on the type of desk. However all desks of the same type will have identical part numbers except for the dimension.

Any suggestions would be greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 08:56:42

[code]DECLARE @InPrices TABLE
(
Code VARCHAR(200),
Adjust MONEY
)

INSERT @InPrices
SELECT 'D2272L-7MC-P', 200 UNION ALL
SELECT 'D2266L-7MC-P', 150 UNION ALL
SELECT 'D2254L-7MC-P', 100 UNION ALL
SELECT 'CR2272-2MC-P', 200 UNION ALL
SELECT 'CR2266-2MC-P', 125 UNION ALL
SELECT 'CR2254-2MC-P', 130

SELECT Code,
Adjust
FROM (
SELECT Code,
Adjust,
DENSE_RANK() OVER (PARTITION BY RIGHT(Code, 6) ORDER BY Code) AS Size,
DENSE_RANK() OVER (PARTITION BY RIGHT(Code, 6) ORDER BY Adjust) AS Prize
FROM @InPrices
) AS d
WHERE Size <> Prize[/code]

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

scott_hanebutt
Starting Member

10 Posts

Posted - 2009-06-23 : 09:40:25

Thanks for the response. Your query is much more advance then anything I have written. When I ran it I got the following error.

'DENSE_RANK' is not a recognized function name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 09:44:26

Oh, my bad. I didn'r recognize this is a SQL Server 2000 forum.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 09:46:57

Here is a version for SQL Server 2000.

SELECT	ip.Code,
	ip.Adjust
FROM	@InPrices AS ip
WHERE	(SELECT COUNT(*) FROM @InPrices AS x WHERE RIGHT(x.Code, 6) = RIGHT(ip.Code, 6) AND ip.Code <= x.Code)
	<> (SELECT COUNT(*) FROM @InPrices AS x WHERE RIGHT(x.Code, 6) = RIGHT(ip.Code, 6) AND ip.Adjust <= x.Adjust)

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

scott_hanebutt
Starting Member

10 Posts

Posted - 2009-06-23 : 09:54:07

Your query works perfectly now except that it is only comparing the example parts I listed. My database has thousands of parts that I want to compare.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-23 : 10:24:24

Yes?
You have to realize I don't have access to your data, so the first part is only for mimicing your environment.

This is the part you need.

SELECT	ip.Code,
	ip.Adjust
FROM	INPRSC AS ip
WHERE	(SELECT COUNT(*) FROM INPRSC AS x WHERE RIGHT(x.Code, 6) = RIGHT(ip.Code, 6) AND ip.Code <= x.Code)
	<> (SELECT COUNT(*) FROM INPRSC AS x WHERE RIGHT(x.Code, 6) = RIGHT(ip.Code, 6) AND ip.Adjust <= x.Adjust)

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