Query item with closest date based on current date

Profile picture for Madhav Vadalia
Madhav Vadalia

I am trying to get the closest date for item no and price based on the current date. The query is giving me output, but not the way I want.

There is a different price for the same item and it's not filtering.

Here's my query:

SELECT distinct [ITEM_NO]
     ,min(REQUIRED_DATE) as Date
     ,[PRICE]
  FROM [DATA_WAREHOUSE].[app].[OHCMS_HOPS_ORDERS]
  where (REQUIRED_DATE) >= GETDATE() and PRICE is not null
  group by ITEM_NO,PRICE
  order by ITEM_NO

Current vs Expected Output

Any Ideas?

This seems like an iteration of the [tag:greatest-n-per-group] problem

I'm not quite certain what constraints you're looking to impose

  1. Largest Date
  2. Most Recent Date (but not in future)
  3. Closest Date to today (past or present)

Here's an example table and which row we'd want if queried on 6/3/2019:

| Item | RequiredDate | Price |
|------|--------------|-------|
| A    | 2019-05-29   |    10 |
| A    | 2019-06-01   |    20 | <-- #2
| A    | 2019-06-04   |    30 | <-- #3
| A    | 2019-06-05   |    40 | <-- #1
| B    | 2019-06-01   |    80 |

But I'm going to guess you're looking for #2

We can identify we the row / largest date by grouping by item and using an aggregate operation like MAX on each group

SELECT o.Item, MAX(o.RequiredDate) AS MostRecentDt 
FROM Orders o
WHERE o.RequiredDate <= GETDATE()
GROUP BY o.Item

Which returns this:

| Item | MostRecentDt |
|------|--------------|
| A    | 2019-05-29   |
| A    | 2019-06-01   |
| B    | 2019-06-01   |

However, once we've grouped by that record, the trouble is then in joining back to the original table to get the full row/record in order to select any other information not part of the original GROUP BY statement

Using ROW_NUMBER we can sort elements in a set, and indicate their order (highest...lowest)

SELECT *, ROW_NUMBER() OVER(PARTITION BY Item ORDER BY RequiredDate DESC) rn
FROM Orders o
WHERE o.RequiredDate <= GETDATE()

| Item | RequiredDate | Price | rn |
|------|--------------|-------|----|
| A    | 2019-05-29   |    10 | 1  |
| A    | 2019-06-01   |    20 | 2  |
| B    | 2019-06-01   |    80 | 1  |

Since we've sorted DESC, now we just want to query this group to get the most recent values per group (rn=1)

WITH OrderedPastItems AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY Item ORDER BY RequiredDate DESC) rn
  FROM Orders o
  WHERE o.RequiredDate <= GETDATE()
)
SELECT * 
FROM OrderedPastItems
WHERE rn = 1

Here's a MCVE in SQL Fiddle

Further Reading: