Avatar of rwheeler23
rwheeler23
Flag for United States of America asked on

Selecting multiple records based maximum dates using Row_Number().

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
Microsoft SQL Server

Avatar of undefined
Last Comment
rwheeler23

8/22/2022 - Mon
Arana (G.P.)

dont you have any INPUT DATE, or DATA ENTRY DATE in your tables?, that would be a lot better than trying to use rownum
Scott Pletcher

Seems like you should be able to add VENDOR_NUMBER to the partitioning:

(SELECT ROW_NUMBER() OVER (PARTITION BY VENDOR_NUMBER, 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)
rwheeler23

ASKER
I have tried several variations without success. This script will be embedded in an SSRS report. The user will supply the desired vendor ID so any items that come from that vendor will be listed as the first row per item. Any subsequent rows would be for the same item from other vendors but ONLY be the most recent Last_Quoted_Cost.

DECLARE @BEGVND CHAR(21)='U07'

;WITH X
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY VENDOR_NUMBER_IVVQ,ITEM_NUMBER_IVVQ ORDER BY VENDOR_NUMBER_IVVQ,ITEM_NUMBER_IVVQ,LAST_QUOTE_DATE_IVVQ DESC) AS RN,T1.ITEM_NUMBER_IVVQ,T1.VENDOR_NUMBER_IVVQ,T1.VENDOR_QUOT_CST_IVVQ,T1.VENDOR_QUOT_QTY_IVVQ,T1.LAST_QUOTE_DATE_IVVQ
FROM POWMATQP..IVPLVLVN T1)
SELECT *
FROM X
WHERE (X.VENDOR_NUMBER_IVVQ=@BEGVND and X.ITEM_NUMBER_IVVQ<>'' AND X.RN =1) OR (X.VENDOR_NUMBER_IVVQ<>@BEGVND and X.ITEM_NUMBER_IVVQ<>'' AND X.RN >1)
Your help has saved me hundreds of hours of internet surfing.
fblack61
PortletPaul

I believe you need more than a single row number calculation. One for the filtering, and another for the ordering. For filtering just concentrate on getting the most recent rows regardless of vendor.

For ordering the result rows partition by item number then in order by using a case expression so that if the vendor matches the parameter set that to 1, else 2. Then fine-tune the remaining order by the vendor.

Filtering by rn=1 should be all you now require, then use the second calculation to assist ordering of the result
/*
The user will supply the desired vendor ID 
so any items that come from that vendor will be listed as the first row per item. 
Any subsequent rows would be for the same item from other vendors 
but ONLY be the most recent Last_Quoted_Cost.
*/

DECLARE @BEGVND char(21) = 'U07'

SELECT
    *
FROM (
    SELECT
          ROW_NUMBER() OVER (PARTITION BY VENDOR_NUMBER_IVVQ, ITEM_NUMBER_IVVQ
                             ORDER BY LAST_QUOTE_DATE_IVVQ DESC) AS RN
        , ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER_IVVQ
                             ORDER BY CASE WHEN VENDOR_NUMBER_IVVQ = @BEGVND THEN 1 ELSE 2 END
                                     , VENDOR_NUMBER_IVVQ) AS ORN
        , T1.ITEM_NUMBER_IVVQ
        , T1.VENDOR_NUMBER_IVVQ
        , T1.VENDOR_QUOT_CST_IVVQ
        , T1.VENDOR_QUOT_QTY_IVVQ
        , T1.LAST_QUOTE_DATE_IVVQ
    FROM POWMATQP..IVPLVLVN T1
) X
WHERE X.RN = 1
ORDER BY ITEM_NUMBER_IVVQ, X.ORN

Open in new window

Note I have not used a common table expression as there is no need for that.
Sharath S

Did you try Scott's suggestion? That should solve your need based on your sample data and expected result.
It doesn't harm but you can remove ITEM_NUMBER in the ORDER BY clause.

;WITH X
AS 
(SELECT ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER, VENDOR_NUMBER ORDER BY 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

Open in new window

Mark Wills

Maybe I am not understanding, but seems fairly straightforward.
DECLARE @BEGVND CHAR(21)='U07'

;WITH X
AS 
(SELECT ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER_IVVQ,VENDOR_NUMBER_IVVQ ORDER BY  LAST_QUOTE_DATE_IVVQ DESC) AS RN,T1.ITEM_NUMBER_IVVQ,T1.VENDOR_NUMBER_IVVQ,T1.VENDOR_QUOT_CST_IVVQ,T1.VENDOR_QUOT_QTY_IVVQ,T1.LAST_QUOTE_DATE_IVVQ
FROM POWMATQP..IVPLVLVN T1)
SELECT *
FROM X
WHERE X.RN =1
order by ITEM_NUMBER_IVVQ -- and/or whatever else 

Open in new window

If you have multiple instances of multiple groups from T1, then you might want to select distinct (with whatever where clauses) as an initial CTE and then do the row_number() cte, and then the final select

If you want to see the seected vendor at the top of the list, then you could do something like : CASE when VENDOR_NUMBER_IVVQ = @BEGVND then 1 else 2 end as VSORT and use that in the Order By. Can also check VSORT in the final WHERE clause : WHERE X.RN =1 and VSORT = 1  -- etc
or if wanting other venfors but only if the parameter one exists :    where RN = 1 and (VSORT = 1 or (VSORT = 2 and exists (select null from x where vsort = 1)))

Does that make sense ?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rwheeler23

ASKER
OK, we are real close. Scott's version only gives me records for the specified vendor and PortletPaul's version gives me all items.  If the item does not come from the specified vendor I want no records to be returned. If the item does come from the specified vendor, I want the most recent record for that vendor and then the most recent record for all other vendors that provide that part. You have all been most helpful. I will tweak this some more in the morning.
Sharath S

Can you provide some sample data and expected result for your last post?
rwheeler23

ASKER
I discovered that many rows have blanks for the item and/or vendor number. My revised script looks like this now.

SELECT
    *
FROM (
    SELECT
          ROW_NUMBER() OVER (PARTITION BY T1.ITEM_NUMBER_IVVQ, T1.VENDOR_NUMBER_IVVQ
                             ORDER BY LAST_QUOTE_DATE_IVVQ DESC) AS RN
        , ROW_NUMBER() OVER (PARTITION BY T1.ITEM_NUMBER_IVVQ
                             ORDER BY CASE WHEN VENDOR_NUMBER_IVVQ = @BEGVND THEN 1 ELSE 2 END
                                     , VENDOR_NUMBER_IVVQ) AS ORN
        , CASE WHEN T1.ITEM_NUMBER_IVVQ <> '' THEN T1.ITEM_NUMBER_IVVQ ELSE '999Z9999P999' END AS ITEM_NUMBER_IVVQ
        , CASE WHEN T1.VENDOR_NUMBER_IVVQ <>'' THEN T1.VENDOR_NUMBER_IVVQ ELSE 'Z99' END AS VENDOR_NUMBER_IVVQ
        , T1.VENDOR_QUOT_CST_IVVQ
        , T1.VENDOR_QUOT_QTY_IVVQ
        , T1.LAST_QUOTE_DATE_IVVQ
    FROM POWMATQP..IVPLVLVN T1
      WHERE T1.ITEM_NUMBER_IVVQ <> '' AND T1.VENDOR_NUMBER_IVVQ <>''
) X
WHERE (X.RN = 1 AND VENDOR_NUMBER_IVVQ=@BEGVND) OR (X.RN>1 AND X.ORN > 1 AND VENDOR_NUMBER_IVVQ<>@BEGVND)
ORDER BY ITEM_NUMBER_IVVQ, VENDOR_NUMBER_IVVQ,X.RN, X.ORN
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Mark Wills

Just loaded your spreadsheet  (@sharath see SampleQuoteCost.xlsx in question header - carefull - column names need _IVVQ appended)

and ran the following code as per my previous post
DECLARE @BEGVND CHAR(21)='P01'

;WITH X
AS 
( SELECT ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER_ivvq,VENDOR_NUMBER_ivvq ORDER BY  LAST_QUOTE_DATE_ivvq DESC) AS RN,T1.ITEM_NUMBER_ivvq,T1.VENDOR_NUMBER_ivvq,T1.VENDOR_QUOT_CST_ivvq,T1.VENDOR_QUOT_QTY_ivvq,T1.LAST_QUOTE_DATE_ivvq
        ,CASE when VENDOR_NUMBER_ivvq = @BEGVND then 1 else 2 end as VSORT
  FROM POWMATQP..IVPLVLVN T1
) SELECT *
FROM X
WHERE RN = 1 and (VSORT = 1 or (VSORT = 2 and exists (select null from x where vsort = 1)))
order by ITEM_NUMBER_IVVQ -- and/or whatever else 

Open in new window

Seems to work
rwheeler23

ASKER
Here is a more complete dataset. The 'DoNotWant' illustrate records to not be included. Since none of them come from U70 I want none of them. The 'WantThese' tabs shows the ones I do want. U70 for one and then the most recent of each vendor, as marked.
ItemsByVendor.xlsx
rwheeler23

ASKER
This version is very close. However, look at the file sent with this. Part 199D3980G001 should not be included as it does not come from U07.

DECLARE @BEGVND CHAR(21)='u07'

;WITH X
AS
( SELECT ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER_ivvq,VENDOR_NUMBER_ivvq ORDER BY  LAST_QUOTE_DATE_ivvq DESC) AS RN,CASE when VENDOR_NUMBER_ivvq = @BEGVND then 1 else 2 end as VSORT,
  T1.ITEM_NUMBER_ivvq,T1.VENDOR_NUMBER_ivvq,T1.VENDOR_QUOT_CST_ivvq,T1.VENDOR_QUOT_QTY_ivvq,T1.LAST_QUOTE_DATE_ivvq
       
  FROM POWMATQP..IVPLVLVN T1
        WHERE T1.ITEM_NUMBER_IVVQ <> '' AND T1.VENDOR_NUMBER_IVVQ <>''
) SELECT *
FROM X
WHERE RN = 1 and (VSORT = 1 or (VSORT = 2 and exists (select null from x where vsort = 1)))
order by ITEM_NUMBER_IVVQ,VENDOR_NUMBER_IVVQ -- and/or whatever else
No199D3980G001.png
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
rwheeler23

ASKER
I need to call it a night. My apologies. I will see your comments in the morning.
ASKER CERTIFIED SOLUTION
Sharath S

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
PortletPaul

nice work Sharath!
for ordering

ORDER BY ITEM_NUMBER_IVVQ, CASE WHEN  VENDOR_NUMBER_IVVQ = @BEGVND THEN 1 ELSE 2 END

+----+------------------+--------------------+----------------------+----------------------+----------------------+----------------------+-----+----+
|    | ITEM_NUMBER_IVVQ | VENDOR_NUMBER_IVVQ | VENDOR_QUOT_CST_IVVQ | VENDOR_QUOT_QTY_IVVQ | LAST_QUOTE_DATE_IVVQ | FIELD6               | cnt | rn |
+----+------------------+--------------------+----------------------+----------------------+----------------------+----------------------+-----+----+
| 1  | 279A2352P001     | U07                | 295,0                | 12                   | 24.02.2017 00:00:00  | KEEP-MOST RECENT U70 | 41  | 1  |
| 2  | 279A2352P001     | C35                | 315,0                | 2                    | 19.04.2005 00:00:00  | KEEP-MOST RECENT C35 | 41  | 1  |
| 3  | 279A2352P001     | E03                | 0,0                  | 6                    | 16.02.2017 00:00:00  | KEEP-MOST RECENT E03 | 41  | 1  |
| 4  | 279A2352P001     | I24                | 0,0                  | 5                    | 19.05.2003 00:00:00  | KEEP-MOST RECENT I24 | 41  | 1  |
| 5  | 279A2352P001     | M70                | 0,0                  | 16                   | 21.11.1994 00:00:00  | KEEP-MOST RECENT M70 | 41  | 1  |
+----+------------------+--------------------+----------------------+----------------------+----------------------+----------------------+-----+----+

Open in new window

Mark Wills

@rwheeler
 
Simple, just add the item_number_ivvq test to the EXISTS e.g.
DECLARE @BEGVND CHAR(21)='U07'

;WITH X
AS 
( SELECT ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER_ivvq,VENDOR_NUMBER_ivvq ORDER BY  LAST_QUOTE_DATE_ivvq DESC) AS RN,T1.ITEM_NUMBER_ivvq,T1.VENDOR_NUMBER_ivvq,T1.VENDOR_QUOT_CST_ivvq,T1.VENDOR_QUOT_QTY_ivvq,T1.LAST_QUOTE_DATE_ivvq
        ,CASE when VENDOR_NUMBER_ivvq = @BEGVND then 1 else 2 end as VSORT
  FROM POWMATQP..IVPLVLVN T1
) SELECT *
FROM X T1
WHERE RN = 1 and (VSORT = 1 or (VSORT = 2 and exists (select null from x T2 where T2.vsort = 1 and t2.item_number_ivvq = T1.item_number_ivvq)))

Open in new window

I think you will find it performs well with volume because the EXISTS is a very, very quick test compared to having to generating another partitioned sort ( or windoowed functions).
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
rwheeler23

ASKER
Thank you all for your help. Your gifted insight is always very much appreciated.