Search code examples
sqlsql-servert-sql

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?

Solution

  • 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