Find Row with closest date for each item in list in SQL Server-CodePudding

Dear all unfortunately I could not get the solution in this Post to work for my Problem.

I have a Table which stores Prices for each ID and attaches a Date. When the Prices are update a new Entry is generated, hence this talble contains mutliple historic Prices for each ID. Now I want to find for each Item in a List of Id's the Price witht he closest smaller Date with reference to a given date.

The table looks like:

| ID | Price | Date       |
|----|-------|------------|
| 1  | 5     | 2022-06-01 |
| 2  | 10    | 2022-06-01 |
| 1  | 4     | 2022-04-01 |
.....

I tried something like this but this does not work:

DECLARE @get_Date VARCHAR(100)   
SET @get_Date = '2022-06-01 00:00:00.000'

SELECT  
      ID
      ,Date
      ,Price
   FROM table
  WHERE ID IN ('1','2') 
  AND Date < CONVERT(DATETIME,@get_Date) 
  ORDER BY Date,ID DESC;

This gives me some output, but I only want exactly one Result per ID?

Any tips would be appreciated.

EDIT:

Tim Biegeleisen´s Solution from below worked fine!

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (
                  PARTITION BY ID
                  ORDER BY DATEDIFF(day, Date, '20220601')
              ) rn
    FROM yourTable
    WHERE Date < '20220601' AND ID in ('1','2')
)

SELECT ID, Price, Date
FROM cte
WHERE rn = 1
ORDER BY ID;

However, it made a significant impact where i put the AND ID in ... clause, which i do note quite understand.

CodePudding user response：

We can use DATEDIFF along with ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (
                  PARTITION BY ID
                  ORDER BY DATEDIFF(day, Date, '20220601')
              ) rn
    FROM yourTable
    WHERE Date < '20220601'
)

SELECT ID, Price, Date
FROM cte
WHERE rn = 1
ORDER BY ID;

The idea here is to find the date per ID whose number of days is smallest relative to the target date.