SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Set date format to day/month/year.  
SET DATEFORMAT dmy;

CREATE TABLE table1 
(
    ID	VARCHAR(512),
    name VARCHAR(512),
    qty	INT,
    [date]	Date
);

INSERT INTO table1  (ID, name, qty, [date]) VALUES
	('1', 'abc', 20, '17/01/2022'),
	('1', 'abc', 10, '18/01/2022'),
	('2', 'def', 10, '24/01/2022'),
	('2', 'def', 40, '25/01/2022'),
	('2', 'def', 67, '26/01/2022');

SELECT * FROM table1

CREATE TABLE table2 
(
    ID	VARCHAR(512),
    name	VARCHAR(512),
    price_dt	DATE,
    price	MONEY
);

INSERT INTO table2 (ID, name, price_dt, price) VALUES
	('1', 'abc', '18/01/2022', '23.56'),
	('1', 'abc', '17/01/2022', '10.56'),
	('1', 'abc', '16/01/2022', '44.33'),
	('1', 'abc', '15/01/2022', '56.11'),
	('2', 'def', '25/01/2022', '2.98'),
	('2', 'def', '26/01/2022', '4.92'),
	('2', 'def', '27/01/2022', '4.88'),
	('2', 'def', '24/01/2022', '3.33'),
	('2', 'def', '23/01/2022', '8.47'),
	('2', 'def', '22/01/2022', '3.89');

SELECT * FROM table2

--price_dt records less than table1.date
SELECT t1.ID, t1.name, t2.price_dt
FROM table1 t1
INNER JOIN table2 t2 on t1.ID=T2.ID AND t1.date > t2.price_dt
  
SELECT t1.ID, t1.name, Max(t2.price_dt) as MaxPrice_dt 
FROM table1 t1
INNER JOIN table2 t2 on t1.ID=T2.ID AND t1.date > t2.price_dt
GROUP BY t1.ID, t1.name

ID	name	qty	date
1	abc	20	2022-01-17
1	abc	10	2022-01-18
2	def	10	2022-01-24
2	def	40	2022-01-25
2	def	67	2022-01-26

ID	name	price_dt	price
1	abc	2022-01-18	23.5600
1	abc	2022-01-17	10.5600
1	abc	2022-01-16	44.3300
1	abc	2022-01-15	56.1100
2	def	2022-01-25	2.9800
2	def	2022-01-26	4.9200
2	def	2022-01-27	4.8800
2	def	2022-01-24	3.3300
2	def	2022-01-23	8.4700
2	def	2022-01-22	3.8900

ID	name	price_dt
1	abc	2022-01-15
1	abc	2022-01-16
1	abc	2022-01-15
1	abc	2022-01-16
1	abc	2022-01-17
2	def	2022-01-22
2	def	2022-01-23
2	def	2022-01-22
2	def	2022-01-23
2	def	2022-01-24
2	def	2022-01-22
2	def	2022-01-23
2	def	2022-01-24
2	def	2022-01-25

ID	name	MaxPrice_dt
1	abc	2022-01-17
2	def	2022-01-25