Latest price paid per supplier per product

• matthew.lingard

  SSC Enthusiast

  Points: 163

  More actions

  April 12, 2017 at 7:27 am

  #324360

  Hi,
  I'm struggling with the SQL to show the latest price we've paid a supplier per product. The product can be supplied by different suppliers and a supplier can provide us different products.
  It's just one table and looks similar to this:

  ITMREF_0	IPTDAT_0	BPRNUM_0	Price
  1	02/10/2016	1	10
  1	03/10/2014	1	15
  1	04/10/2016	2	20
  2	05/10/2016	3	10
  2	06/10/2016	4	15
  2	07/10/2015	1	20

  So based upon the above I'd like the SQL to show:

  ITMREF_0	LatestDate	BPRNUM_0	Price
  1	02/10/2016	1	10
  1	04/10/2016	2	20
  2	05/10/2016	3	10
  2	06/10/2016	4	15
  2	07/10/2015	1	20

  Does anyone have any ideas please?
  Thank you,

• Thom A

  SSC Guru

  Points: 99143

  More actions

  April 12, 2017 at 7:38 am

  #1937835

  CREATE TABLE #Supply
  (ITMREF_0 int,
  IPTDAT_0 date,
  BPRNUM_0 int,
  Price int);
GO

  INSERT INTO #Supply
VALUES
  (1, '20161002', 1, 10),
  (1, '20141003', 1, 15),
  (1, '20161004', 2, 20),
  (2, '20161005', 3, 10),
  (2, '20161006', 4, 15),
  (2, '20161007', 1, 20);

  GO

  SELECT *
FROM #Supply;
GO

  WITH CTE AS(
  SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ITMREF_0, BPRNUM_0 ORDER BY IPTDAT_0 DESC) AS RN
  FROM #Supply)
SELECT ITMREF_0,
       IPTDAT_0 AS LatestDate,
       BPRNUM_0,
       Price
FROM CTE
WHERE RN = 1
ORDER BY IPTDAT_0 ASC;

  GO

  DROP TABLE #Supply
GO

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• matthew.lingard

  SSC Enthusiast

  Points: 163

  More actions

  April 12, 2017 at 7:57 am

  #1937844

  Thom A - Wednesday, April 12, 2017 7:38 AM

  CREATE TABLE #Supply
    (ITMREF_0 int,
    IPTDAT_0 date,
    BPRNUM_0 int,
    Price int);
  GO

  INSERT INTO #Supply
  VALUES
    (1, '20161002', 1, 10),
    (1, '20141003', 1, 15),
    (1, '20161004', 2, 20),
    (2, '20161005', 3, 10),
    (2, '20161006', 4, 15),
    (2, '20161007', 1, 20);

  GO

  SELECT *
  FROM #Supply;
  GO

  WITH CTE AS(
    SELECT *,
          ROW_NUMBER() OVER (PARTITION BY ITMREF_0, BPRNUM_0 ORDER BY IPTDAT_0 DESC) AS RN
    FROM #Supply)
  SELECT ITMREF_0,
         IPTDAT_0 AS LatestDate,
         BPRNUM_0,
         Price
  FROM CTE
  WHERE RN = 1
  ORDER BY IPTDAT_0 ASC;

  GO

  DROP TABLE #Supply
GO

  Perfect, thank you very much 🙂