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
LVL 1
rwheeler23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aranaCommented:
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 PletcherSenior DBACommented:
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)
rwheeler23Author Commented:
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)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
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.
SharathData EngineerCommented:
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 WillsTopic AdvisorCommented:
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 ?
rwheeler23Author Commented:
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.
SharathData EngineerCommented:
Can you provide some sample data and expected result for your last post?
rwheeler23Author Commented:
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
Mark WillsTopic AdvisorCommented:
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
rwheeler23Author Commented:
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
rwheeler23Author Commented:
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
rwheeler23Author Commented:
I need to call it a night. My apologies. I will see your comments in the morning.
SharathData EngineerCommented:
Can you try this?

declare @BEGVND varchar(10) = 'U07'
;WITH x AS (
select *,COUNT(CASE WHEN VENDOR_NUMBER_IVVQ = @BEGVND THEN 1 END) over (PARTITION BY ITEM_NUMBER_IVVQ) cnt,
       ROW_NUMBER() OVER (PARTITION BY VENDOR_NUMBER_IVVQ,ITEM_NUMBER_IVVQ ORDER BY LAST_QUOTE_DATE_IVVQ DESC) rn 
  from POWMATQP.dbo.IVPLVLVN)
SELECT *
  FROM x
 WHERE cnt > 0 AND rn = 1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
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 WillsTopic AdvisorCommented:
@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).
rwheeler23Author Commented:
Thank you all for your help. Your gifted insight is always very much appreciated.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.