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_DAT
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/201
PART01,P02,330.00,15,12/15
PART02,P01,295.00,32,5/13/
PART02,P02,285.00,2,2/7/20
PART03,P01,21.00,6,12/31/2
PART03,P02,17.00,12,12/31/
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
DECLARE @BEGVND CHAR(21)='AAA'
;WITH X
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER ORDER BY ITEM_NUMBER,LAST_QUOTE_DAT
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/201
PART01,P02,330.00,15,12/15
PART02,P01,295.00,32,5/13/
PART02,P02,285.00,2,2/7/20
PART03,P01,21.00,6,12/31/2
PART03,P02,17.00,12,12/31/
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
dont you have any INPUT DATE, or DATA ENTRY DATE in your tables?, that would be a lot better than trying to use rownum
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_DAT
FROM IVPLVLVN)
(SELECT ROW_NUMBER() OVER (PARTITION BY VENDOR_NUMBER, ITEM_NUMBER ORDER BY ITEM_NUMBER,LAST_QUOTE_DAT
FROM IVPLVLVN)
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_NU
FROM POWMATQP..IVPLVLVN T1)
SELECT *
FROM X
WHERE (X.VENDOR_NUMBER_IVVQ=@BEG
DECLARE @BEGVND CHAR(21)='U07'
;WITH X
AS
(SELECT ROW_NUMBER() OVER (PARTITION BY VENDOR_NUMBER_IVVQ,ITEM_NU
FROM POWMATQP..IVPLVLVN T1)
SELECT *
FROM X
WHERE (X.VENDOR_NUMBER_IVVQ=@BEG
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
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
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.
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
Maybe I am not understanding, but seems fairly straightforward.
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 ?
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
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 ?
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.
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
ORDER BY ITEM_NUMBER_IVVQ, VENDOR_NUMBER_IVVQ,X.RN, X.ORN
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
ORDER BY ITEM_NUMBER_IVVQ, VENDOR_NUMBER_IVVQ,X.RN, X.ORN
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
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
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
ItemsByVendor.xlsx
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_NU
T1.ITEM_NUMBER_ivvq,T1.VEN
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_NU
No199D3980G001.png
DECLARE @BEGVND CHAR(21)='u07'
;WITH X
AS
( SELECT ROW_NUMBER() OVER (PARTITION BY ITEM_NUMBER_ivvq,VENDOR_NU
T1.ITEM_NUMBER_ivvq,T1.VEN
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_NU
No199D3980G001.png
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
nice work Sharath!
for ordering
ORDER BY ITEM_NUMBER_IVVQ, CASE WHEN VENDOR_NUMBER_IVVQ = @BEGVND THEN 1 ELSE 2 END
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 |
+----+------------------+--------------------+----------------------+----------------------+----------------------+----------------------+-----+----+
@rwheeler
Simple, just add the item_number_ivvq test to the EXISTS e.g.
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)))
Premium Content
You need an Expert Office subscription to comment.Start Free Trial