Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Show highest value from different suppliers

Posted on 2012-08-29
13
Medium Priority
?
348 Views
Last Modified: 2012-09-03
I have a table with different prices from different suppliers for the same product. The suppliers can send price updates.
 
I need 2 queries:

1# shows the best price (highest) from the latest update (create date) and which suppliers has that price.

price, supplier, VE_ProductID

2# shows the best price pr supplier:

VE_ProductID, price

 
price      CreateDate      SupplierID      VE_ProductID
32.33000      2012-08-28 15:20:38.520      4      457
32.99000      2012-08-28 15:18:29.763      1      457
32.62000      2012-08-28 14:57:01.837      1      457
30.85000      2012-08-28 14:24:10.003      4      457
32.92000      2012-08-28 14:23:05.197      1      457
31.85000      2012-08-28 14:20:08.530      4      457
32.90000      2012-08-28 14:15:59.947      4      457
32.76000      2012-08-28 14:14:53.147      1      457
33.05000      2012-08-28 13:51:05.550      4      457
32.65000      2012-08-28 13:45:48.597      1      457
33.38000      2012-08-28 10:31:10.573      1      457


Thx!
0
Comment
Question by:Madsing
  • 6
  • 5
12 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38345266
Here you go.  You need RANK() and a subquery for the 'Give me Top 1 for each ... '

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp

CREATE TABLE #tmp (price money, CreateDate datetime, SupplierID int, VE_ProductID int )

INSERT INTO #tmp (price, CreateDate,  SupplierID, VE_ProductID)
VALUES 
	(32.33000, '2012-08-28 15:20:38.520', 4 ,     457), 
	(32.99000, '2012-08-28 15:18:29.763', 1,      457), 
	(32.62000, '2012-08-28 14:57:01.837', 1   ,   457), 
	(30.85000, '2012-08-28 14:24:10.003', 4 ,     457), 
	(32.92000, '2012-08-28 14:23:05.197', 1 ,     457), 
	(31.85000, '2012-08-28 14:20:08.530', 4 ,     457), 
	(32.90000, '2012-08-28 14:15:59.947', 4,      457), 
	(32.76000, '2012-08-28 14:14:53.147', 1 ,     457), 
	(33.05000, '2012-08-28 13:51:05.550', 4,      457), 
	(32.65000, '2012-08-28 13:45:48.597', 1,      457), 
	(33.38000, '2012-08-28 10:31:10.573', 1,457)

-- 1# shows the best price (highest) from the latest update (create date) and which suppliers has that price.
-- price, supplier, VE_ProductID
SELECT TOP 1 price, SupplierID, VE_ProductID
FROM #tmp
ORDER BY CreateDate DESC

-- 2# shows the best price pr supplier:
SELECT SupplierID, VE_ProductID, price 
FROM (
	SELECT SupplierID, VE_ProductID, price, 
		RANK() OVER (PARTITION BY SupplierID, VE_ProductID order by PRICE DESC) as price_by_supplier_rank
	FROM #tmp) a
WHERE a.price_by_supplier_rank = 1


-- VE_ProductID, price

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38345277
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38345286
Modifying the below SQL block a little, now you can see all rows, 'grouped' by supplierID and productID, with the prices in descending order, and the rank order within supplier / product.

SELECT SupplierID, VE_ProductID, price, price_by_supplier_by_product_rank 
FROM (
	SELECT SupplierID, VE_ProductID, price, 
		RANK() OVER (PARTITION BY SupplierID, VE_ProductID order by PRICE DESC) as price_by_supplier_by_product_rank
	FROM #tmp) a

Open in new window

0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 38345384
Thanks for the grade.  Good luck with your project.  -Jim

ps This question put me into the 5 million point club.
0
 

Author Comment

by:Madsing
ID: 38345401
It great! I just think I was a bit to fast. For the 1# i need it to be so it shows the best price and attached supplierID for all the different products for a specific date. Maybe request a @Inputdate to used as filter.
I have attached the prices for 2 days in a file.
ProductPrice.csv
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38345476
>for a specific date.
Assuming you mean date without the time component...

Declare @dt date = '2012-08-28'
SELECT TOP 1 price, SupplierID, VE_ProductID,
	RANK() OVER (PARTITION BY SupplierID, VE_ProductID, CAST(CreateDate as date) ORDER BY price DESC) as price_by_supplier_by_product_rank
FROM #tmp
WHERE CAST(CreateDate as date) = @dt

Open in new window

0
 

Author Comment

by:Madsing
ID: 38345610
It only shows 30,99 as price for VEDK_productID 119. I need it to show for all the products that has prices on that date.

Also 30,99 is not the highest price
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38345748
>need it to show for all the products that has prices on that date.
>Also 30,99 is not the highest price

Try this.  I changed the data a little to better illustrate.

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
	DROP TABLE #tmp

CREATE TABLE #tmp (price money, CreateDate datetime, SupplierID int, VE_ProductID int )

INSERT INTO #tmp (price, CreateDate,  SupplierID, VE_ProductID)
VALUES 
	(32.33000, '2012-08-28 15:20:38.520', 4 ,     457), 
	(32.99000, '2012-08-28 15:18:29.763', 1,      456), 
	(36.62000, '2012-08-28 14:57:01.837', 7   ,   456), 
	(30.85000, '2012-08-28 14:24:10.003', 4 ,     333), 
	(32.92000, '2012-08-28 14:23:05.197', 1 ,     457), 
	(31.85000, '2012-08-28 14:20:08.530', 4 ,     457), 
	(32.90000, '2012-08-28 14:15:59.947', 4,      457), 
	(32.76000, '2012-08-28 14:14:53.147', 1 ,     333), 
	(33.05000, '2012-08-28 13:51:05.550', 4,      457), 
	(32.65000, '2012-08-28 13:45:48.597', 1,      333), 
	(33.38000, '2012-08-28 10:31:10.573', 1,457)

-- 1# shows the best price (highest) from the latest update (create date) and which suppliers has that price.
-- price, supplier, VE_ProductID
Declare @dt date = '2012-08-28'

SELECT SupplierID, VE_ProductID, price
FROM (
	SELECT price, SupplierID, VE_ProductID,
		RANK() OVER (PARTITION BY VE_ProductID, CAST(CreateDate as date) ORDER BY price DESC) as price_by_product_rank
	FROM #tmp
	WHERE CAST(CreateDate as date) = @dt ) a
WHERE a.price_by_product_rank = 1

Open in new window

0
 

Author Comment

by:Madsing
ID: 38345951
Still not working. I wont only one record for each productID.

See result for query ran in attached file.

Declare @dt date = '2012-08-28'

SELECT SupplierID, VE_ProductID, price 
FROM (
	SELECT price, SupplierID, VE_ProductID,
		RANK() OVER (PARTITION BY VEDK_ProductID, CAST(CreateDate as date) ORDER BY price DESC) as price_by_product_rank
	FROM VE_ProductPrice
	WHERE CAST(CreateDate as date) = @dt ) a
WHERE a.price_by_product_rank = 1
                                            

Open in new window

                                           


Notice there are 4 records with productID 119?! . There should only be one!
0
 

Author Comment

by:Madsing
ID: 38360793
@jimhorn

Will you take a look at the 1# query again.

Thanks
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1500 total points
ID: 38361047
I believe I answered your original question, which you accepted an answer.
Later the question was modified in the 38345401 comment, and it appears you have withheld the original answer until your modification is addressed.

This is not fair to experts.

I recommend you re-accept the answer to your original question, then ask your change as another question.
0
 

Author Comment

by:Madsing
ID: 38361106
Yes your are right. The points are yours! .
I have created a new question with better data.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27851550.html
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Loops Section Overview

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question