We help IT Professionals succeed at work.
Get Started

Selecting multiple records based maximum dates using Row_Number().

rwheeler23
rwheeler23 asked
on
107 Views
Last Modified: 2018-11-01
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
Comment
Watch Question
Data Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 17 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE