troubleshooting Question

Selecting multiple records based maximum dates using Row_Number().

Avatar of rwheeler23
rwheeler23Flag for United States of America asked on
Microsoft SQL Server
17 Comments1 Solution109 ViewsLast Modified:
Is it possible to take this script and expand it to allow the selection of the last cost, quantity and date from other vendors? What I am trying to get is the last values from a selected vendor. What I then want to add is the same last information from any other vendors for the same item.
DECLARE @BEGVND CHAR(21)='AAA'

;WITH X
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER ORDER BY ITEM_NUMBER,LAST_QUOTE_DATE DESC) AS RN,ITEM_NUMBER,VENDOR_NUMBER,.VENDOR_QUOT_CST,VENDOR_QUOT_QTY,LAST_QUOTE_DATE
FROM IVPLVLVN)
SELECT *
FROM X
WHERE X.VENDOR_NUMBER=@BEGVND AND X.RN =1

I have attached a sample dataset. The desired output would be
PART01,P01,5.00,1,5/20/2014
PART01,P02,330.00,15,12/15/2016
PART02,P01,295.00,32,5/13/2016
PART02,P02,285.00,2,2/7/2018
PART03,P01,21.00,6,12/31/2014
PART03,P02,17.00,12,12/31/2014

So there will always be one for the selected vendor but there could be as many as found for all the other vendors who have quoted a cost. All records must reflect the most recent quoted cost.
SampleQuoteCost.xlsx
ASKER CERTIFIED SOLUTION
Sharath S
Data Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 17 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 17 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros