Easy Select (hopefully) Help....

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

Transact-SQL (2000)

Easy Select (hopefully) Help....

Author

Topic

sharkb8
Starting Member

12 Posts

Posted - 2003-01-06 : 14:20:01

Ok...
1st off let me thank rrb and Valter for their excellent help on my last issue. With some minor modifications it worked like a charm. Now to my new issue, I can accomplish this in .Net looping through the dataset, and then performing the various functions, but since the application doesn't have any forward facing usage, I'd like to do this solely in the database. Here's the rundown....

I have 3 tables.
1. Product table, Product Id (ProdId, Varchar 11), Product Name (Product, Varchar 100), other product descriptive data....

2. Product Prices table, ProdId (ProdId, Varchar 11), Price (int), Price_eff_dt(smalldatetime), Price2 (int), Price2_eff_dt (smalldatetime)..etc(more price data)..

3. Category Table, Product Id(Varchar 11), Category (VarChar 50)

(I inherited this table mess and will soon be modeling a new DB with the appropriate PK, FK relationships which will clearup a lot of this, but until I can...I'm stuck....)

I want to select the lowest price from the product Prices table by Category for each product in the product table.

Example:

Product TBL

ProdId Product
------ -------
1234 Name Brand Ping Pong Balls
2345 Generic Ping Pong Balls

Price TBL

ProdId Price Price_eff_dt Price2 Price2_eff_dt
------ ----- ------------ ------ -------------
1234 10.50 1/1/2000 10.00 1/1/1998
2345 5.00 1/1/2000 5.10 1/1/1998

Category TBL

ProdId Category
------ ---------
1234 Ping Pong Balls
2345 Ping Pong Balls

I want to get:
Orig_Item# Price New_Item# Price Savings
---------- ------ --------- ------ -------
1234 10.50 2345 5.00 5.50

Any ideas?? Thanks for all your help (in advance).....

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-01-06 : 14:27:33

Everyone always takes the time to laboriously type out a human description of a table. Post the actual DDL for your tables, and include sample DML to place the sample data you've posted into them. Then we'll be happy to help

.

Jonathan
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-06 : 15:06:17

Not sure where you are getting original item#, then new item #.... in your sample data, they are both the same category and the dates are exactly the same, and they are different items.

- Jeff

sharkb8
Starting Member

12 Posts

Posted - 2003-01-06 : 16:20:44

Sorry,
I should have been more descriptive(in the mean time, I'll work up sample DDL/DML and post)......

I want to get ALTERNATIVE products (finding the one with the lowest price) to the each product in the Product table. That's what I meant by Original Item #, New Item #. I want to list the Original product orders and then find the lowest cheaper alternative (if any), as long as it's in the same Category...

Thanks....

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-06 : 19:03:22

CREATE TABLE #Product (ProdId int, Product varchar(100))
CREATE TABLE #Price (ProdID int, Price money, Price_eff_dt smalldatetime, Price2 money, Price2_eff_dt smalldatetime)
CREATE TABLE #Category (ProdId varchar(11), Category varchar(50))

INSERT INTO #Product (ProdId, Product) VALUES (1234,'Name Brand Ping Pong Balls')
INSERT INTO #Product (ProdId, Product) VALUES (2345,'Generic Brand Ping Pong Balls')

INSERT INTO #Price(ProdID, Price, Price_eff_dt, Price2, Price2_eff_dt) VALUES (1234,10.50,'1/1/2000',10.00,'1/1/1998')
INSERT INTO #Price(ProdID, Price, Price_eff_dt, Price2, Price2_eff_dt) VALUES (2345,5.00,'1/1/2000',5.10,'1/1/1998')

INSERT INTO #Category (ProdId, Category) VALUES ('1234','Ping Pong Balls')
INSERT INTO #Category (ProdId, Category) VALUES ('2345','Ping Pong Balls')

SELECT AAA.ProdId As Orig_Item#, BBB.Price, DDD.ProdID As New_Item#, DDD.Price, BBB.Price - DDD.Price As Savings
FROM #Product AAA
INNER JOIN #Price BBB ON AAA.ProdId = BBB.ProdID
INNER JOIN #Category CCC ON AAA.ProdId = CCC.ProdId
INNER JOIN
(
SELECT AA.Category, BB.ProdID, BB.Price
FROM #Category AA
INNER JOIN #Price BB ON AA.ProdId = BB.ProdId
INNER JOIN
(
SELECT A.Category, MIN(B.Price) As MinPrice
FROM #Category A INNER JOIN #Price B ON A.ProdId = B.ProdID
GROUP BY A.Category
) CC ON CC.Category = AA.Category AND CC.MinPrice = BB.Price
)DDD ON CCC.Category = DDD.Category

DROP TABLE #Product
DROP TABLE #Price
DROP TABLE #Category

Slight deviation in the price type because you described it as int
but used floating point values in example so I used money type.

Hope this doesn't discourage you from posting ddl next time.

Edited by - ValterBorges on 01/06/2003 19:06:47

sharkb8
Starting Member

12 Posts

Posted - 2003-01-07 : 17:28:02

Valter,
Once again...a big thank you.....it works! Only issue is that I am getting duplicate records. I have added Distinct to the Selects and there are still duplicates coming back. But, I can work with it....

Thanks again...

sharkb8
Starting Member

12 Posts

Posted - 2003-01-07 : 17:41:50

Sorry, (my bad) it's not generating duplicates per se, it's generating more than 1 transaction when it finds products in the same category, with the same price. So, if I have 13 manufacturers of ping pong balls all charging the same price, 13 transactions are posted to the table.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-01-07 : 17:55:48

Valter must be snoozing again. Why all these americans go to sleep in the middle of the day is beyond me

so - I'm confused. Can you give us another "INSERT INTO" line which will produce the problem? (so we can add it to our example data...)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

sharkb8
Starting Member

12 Posts

Posted - 2003-01-07 : 18:06:28

rrb,
Replace the insert section with this....

INSERT INTO #Product (ProdId, Product) VALUES (1234,'Name Brand Ping Pong Balls')
INSERT INTO #Product (ProdId, Product) VALUES (2345,'Generic Brand Ping Pong Balls')
INSERT INTO #Product (ProdId, Product) VALUES (3456,'Generic Brand Ping Pong Balls')
INSERT INTO #Product (ProdId, Product) VALUES (4567,'Generic Brand Ping Pong Balls')

INSERT INTO #Price(ProdID, Price, Price_eff_dt, Price2, Price2_eff_dt) VALUES (1234,10.50,'1/1/2000',10.00,'1/1/1998')
INSERT INTO #Price(ProdID, Price, Price_eff_dt, Price2, Price2_eff_dt) VALUES (2345,5.00,'1/1/2000',5.10,'1/1/1998')

INSERT INTO #Category (ProdId, Category) VALUES ('1234','Ping Pong Balls')
INSERT INTO #Category (ProdId, Category) VALUES ('2345','Ping Pong Balls')
INSERT INTO #Category (ProdId, Category) VALUES ('3456','Ping Pong Balls')
INSERT INTO #Category (ProdId, Category) VALUES ('4567','Ping Pong Balls')

INSERT INTO #Price(ProdID, Price, Price_eff_dt, Price2, Price2_eff_dt) VALUES (3456,5.00,'1/1/2000',5.10,'1/1/1998')

INSERT INTO #Price(ProdID, Price, Price_eff_dt, Price2, Price2_eff_dt) VALUES (4567,5.00,'1/1/2000',5.10,'1/1/1998')

See what I mean...when there are more than 1 manufacturer with the same price, they all show up for that item....

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-01-07 : 18:58:46

OK sharky

Valter's code with your data produces:
Orig_Item# Price New_Item# Price Savings
1234 10.50 2345 5.00 5.50
1234 10.50 3456 5.00 5.50
1234 10.50 4567 5.00 5.50
2345 5.00 2345 5.00 0.00
2345 5.00 3456 5.00 0.00
2345 5.00 4567 5.00 0.00
3456 5.00 2345 5.00 0.00
3456 5.00 3456 5.00 0.00
3456 5.00 4567 5.00 0.00
4567 5.00 2345 5.00 0.00
4567 5.00 3456 5.00 0.00
4567 5.00 4567 5.00 0.00

Now these are all distinct as far as I can see, but maybe what you're trying to say is that you only want the first value from each category?

Also, do you want to get rid of results like
Orig_Item# Price New_Item# Price Savings
4567 5.0000 4567 5.0000 .0000
?

Or are you saying that you don't want any items where the saving is $0.00?

Edited by - rrb on 01/07/2003 19:12:20

sharkb8
Starting Member

12 Posts

Posted - 2003-01-07 : 19:13:32

rrb,
Yes I recanted my post to state that they are not duplicates....

And yes, I just want to return 1 only (the 1st one)....I want to tell the client that they have another a cheaper option (if available) on a product they ordered. If they come back and ask for additional detail, I can run that report. But for this instance I just want 1 returned. You are correct....

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-01-07 : 19:18:58

OK and what about where there is no saving possible? return nothing?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

sharkb8
Starting Member

12 Posts

Posted - 2003-01-07 : 19:19:02

and I can go back and recursively delete any (or just not show) any time where savings = 0

(I must have been typing while you were posting.....)

Edited by - sharkb8 on 01/07/2003 19:20:02

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-01-07 : 19:30:27

OK - I think you want this:

SELECT AAA.ProdId As Orig_Item#, BBB.Price, DDD.ProdID As New_Item#, DDD.Price, (BBB.Price - DDD.Price) As Savings
FROM #Product AAA
INNER JOIN #Price BBB ON AAA.ProdId = BBB.ProdID
INNER JOIN #Category CCC ON AAA.ProdId = CCC.ProdId
INNER JOIN
(

SELECT AA.Category, Min(BB.ProdID) as ProdId, BB.Price
FROM #Category AA
INNER JOIN #Price BB ON AA.ProdId = BB.ProdId
INNER JOIN
(
SELECT A.Category, MIN(B.Price) As MinPrice
FROM #Category A INNER JOIN #Price B ON A.ProdId = B.ProdID
GROUP BY A.Category
) CC ON CC.Category = AA.Category AND CC.MinPrice = BB.Price
group by aa.category, bb.price

)DDD ON CCC.Category = DDD.Category
where BBB.Price > DDD.Price

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Edited by - rrb on 01/07/2003 19:31:25

sharkb8
Starting Member

12 Posts

Posted - 2003-01-07 : 20:05:07

Once again...many thanks....

rrb, works like a Charm!!!!

Thanks a million Valter, rrb......

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-01-07 : 20:15:55

Good! glad to help.

My last question then - doesn't this strategy unfairly bias producers of items with the smallest ProdID?

I mean, it always preferences the MIN(ProdId). Have you considered the contractual implications and is this OK?

Perhaps a better solution would "share around" the recommended "New Items" from different suppliers on an equal basis...

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

sharkb8
Starting Member

12 Posts

Posted - 2003-01-07 : 20:29:31

We are not passing the client the "actual" manufacturer's name. We are passing them the fact that there are alternative cost saving measures. If they ask who and what product will give them the less expensive product, we will provide the names and product info for all the rest of the manufacturers and then the client makes the call.....

BIG THANKS!!!!!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-07 : 21:35:34

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources