CODEWITHSUNDEEP
sqlsql-servert-sql
Madhav Vadalia
Madhav Vadalia

Reputation: 43

Query item with closest date based on current date

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?

Upvotes: 1

Views: 846

Answers (3)

KyleMit
KyleMit

Reputation: 29829

This seems like an iteration of the 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

Demo in SQL Fiddle

Further Reading:

Upvotes: 0

Paul
Paul

Reputation: 3884

Could you order by the the absolute value of DATEDIFF?

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

Upvotes: 3

D-Shih
D-Shih

Reputation: 46219

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

Upvotes: 3

Related Questions