Query item with closest date based on current date

Question

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

Any Ideas?

1) You should include input (the data being queried) so the expected and actual output can be reproduced. 2) One hint: `GETDATE()` also returns the time and, based on your screen shot, your column only records the date (no time). — Igor, Jun 03 '19 at 14:40
there is no input. i am querying the data with that code and getting current output but i want to get whats in expect output .i used convert to change getdate() to show only date. and still the same. — Madhav Vadalia, Jun 03 '19 at 14:45
Please read [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Jun 03 '19 at 14:52
`there is no input` ← By input I mean the data being queried (*ie. records in the `[DATA_WAREHOUSE].[app].[OHCMS_HOPS_ORDERS]` table*) , that's the "input" in this case. Without that there is not enough data to create an [mcve] which is needed for those that want to assist you. — Igor, Jun 03 '19 at 14:54

score 3 · Accepted Answer · answered Jun 03 '19 at 14:44

You can try to use ROW_NUMBER window function to make it.

SELECT ITEM_NO,
       REQUIRED_DATE,
       PRICE
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY ITEM_NO ORDER BY REQUIRED_DATE) rn
    FROM DATA_WAREHOUSE].[app].[OHCMS_HOPS_ORDERS]
    where REQUIRED_DATE >= GETDATE() and PRICE is not null
)t1
WHERE rn = 1

score 3 · Answer 2 · answered Jun 03 '19 at 15:42

3

Could you order by the the absolute value of DATEDIFF?

ORDER BY ABS(DATEDIFF(day, REQUIRED_DATE, GETDATE()))

answered Jun 03 '19 at 15:42

Paul

3,634
1
18
23

KyleMit · Answer 3 · 2019-06-03T18:06:26.417

This seems like an iteration of the greatest-n-per-group problem

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

Largest Date
Most Recent Date (but not in future)
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:

Query item with closest date based on current date

3 Answers3