0

Good afternoon,

I've been working on this all day and I can't figure it out. I'm performing this query in excel.

SELECT 
PRICE_BOOK.status,
PRICE_BOOK.ms_code,
PRICE_BOOK.location_code,
SUPPLIER_LOC_HDR.name,
PRICE_BOOK.mfg_code,
PRICE_BOOK.group_code,
PRICE_BOOK.stock_id,
PRICE_BOOK.effective_date,
PRICE_BOOK.price
FROM dbo.MS_INFO MS_INFO, dbo.PRICE_BOOK PRICE_BOOK, dbo.SUPPLIER_LOC_HDR 
SUPPLIER_LOC_HDR
WHERE MS_INFO.ms_code = PRICE_BOOK.ms_code AND 
SUPPLIER_LOC_HDR.location_code = PRICE_BOOK.location_code AND 
((PRICE_BOOK.mfg_code Not In ('Type1','Type2','Type3')))

This is exporting the prices for each location, product, effective time, and price.

But, I need only the most recent "PRICE_BOOK.effective_date" per unique combination of ms_code, location_code, name, mfg_code, group_code, stock_id.

In other words, I need to export only the most recent price per product per location.

status ms_code location_code name mfg_code group_code stock_id effective_date price A Supplier1 Terminal 1 City #1 PartType1 Group1 61 7/5/17 0:01 1.09 A Supplier1 Terminal 1 City #1 PartType1 Group1 61 7/4/17 0:01 1.41 A Supplier1 Terminal 1 City #2 PartType1 Group1 61 7/3/17 0:01 1.76 A Supplier1 Terminal 1 City #2 PartType1 Group1 61 5/24/17 0:01 1.20 A Supplier1 Terminal 1 City #1 PartType1 Group1 62 7/5/17 0:01 1.67 A Supplier1 Terminal 1 City #1 PartType1 Group1 62 7/4/17 0:01 1.19 A Supplier1 Terminal 1 City #1 PartType1 Group1 62 7/3/17 0:01 1.14 A Supplier1 Terminal 1 City #1 PartType1 Group1 62 5/24/17 0:01 1.11 A Supplier1 Terminal 1 City #1 PartType1 Group1 63 7/5/17 0:01 1.33 A Supplier1 Terminal 1 City #1 PartType1 Group1 63 7/4/17 0:01 1.59 A Supplier1 Terminal 1 City #1 PartType1 Group1 63 7/3/17 0:01 1.61 A Supplier1 Terminal 1 City #1 PartType1 Group1 63 5/24/17 0:01 1.75 A Supplier1 Terminal 1 City #1 PartType1 Group1 64 7/5/17 0:01 1.75 A Supplier1 Terminal 1 City #1 PartType1 Group1 64 7/4/17 0:01 1.77 A Supplier2 Terminal 1 City #1 PartType1 Group1 64 7/3/17 0:01 1.45 A Supplier2 Terminal 1 City #1 PartType1 Group1 64 5/24/17 0:01 1.77

Expected Results

status ms_code location_code name mfg_code group_code stock_id effective_date price A Supplier1 Terminal 1 City #1 PartType1 Group1 61 7/5/17 0:01 1.09 A Supplier1 Terminal 1 City #2 PartType1 Group1 61 7/3/17 0:01 1.76 A Supplier1 Terminal 1 City #1 PartType1 Group1 62 7/5/17 0:01 1.67 A Supplier1 Terminal 1 City #1 PartType1 Group1 63 7/5/17 0:01 1.33 A Supplier1 Terminal 1 City #1 PartType1 Group1 64 7/5/17 0:01 1.75 A Supplier2 Terminal 1 City #1 PartType1 Group1 64 7/3/17 0:01 1.45

Edit: Changed query per Clock's comment

SELECT
PRICE_BOOK.status,
PRICE_BOOK.ms_code,
PRICE_BOOK.location_code,
SUPPLIER_LOC_HDR.name,
PRICE_BOOK.mfg_code,
PRICE_BOOK.group_code,
PRICE_BOOK.stock_id,
PRICE_BOOK.effective_date,
PRICE_BOOK.price
FROM dbo.PRICE_BOOK PRICE_BOOK
    INNER JOIN dbo.SUPPLIER_LOC_HDR SUPPLIER_LOC_HDR
        ON SUPPLIER_LOC_HDR.location_code = PRICE_BOOK.location_code
WHERE ((PRICE_BOOK.mfg_code Not In ('Type1','Type2','Type3')))

Thanks in advance,

Jeff

jeffpro
  • 21
  • 2
  • 2
    Can you provide your schema with some data to write and test query? – Santosh M. Sep 26 '17 at 21:12
  • Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column). Welcome to [tag:greatest-n-per-group], where other RDBMSs have utilities to help you. Also, drop the implicit-join (comma-separated `FROM` clause) syntax, and use explicit `JOIN`s and accompanying conditions instead. – Clockwork-Muse Sep 26 '17 at 21:20
  • Clockwork, sorry I’m not a programmer. Not understanding what you said. Appreciate the help. – jeffpro Sep 26 '17 at 23:25

1 Answers1

0

You should use window function. Something like this:

qualify row_number() over(partition by product, location order by effective_date desc) = 1
Alex
  • 731
  • 1
  • 6
  • 21