By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table a(node varchar(128), date date, value int);
create table b(node varchar(128), timestamp date, method varchar(128));
insert into a values
('01R-123','2023-01-10',09),
('01R-123','2023-01-09',11),
('01R-123','2023-01-08',18),
('01R-123','2023-01-07',87),
('01R-123','2023-01-06',32),
('01R-123','2023-01-05',22),
('01R-123','2023-01-04',16),
('01R-123','2023-01-03',24),
('01R-123','2023-01-02',24),
('01R-123','2023-01-01',24);
insert into b values
('01R-123','2023-01-10','Jet'),
('01R-123','2023-01-09','Jet'),
('01R-123','2023-01-08','Jet'),
('01R-123','2023-01-05','Jet'),
('01R-123','2023-01-04','Jet'),
('01R-123','2023-01-03','Jet'),
('01R-123','2022-12-30','Jet'),
('01R-123','2022-12-29','Jet'),
('01R-123','2022-12-28','Jet'),
('01R-123','2022-12-25','Jet');
20 rows affected
SELECT node, date, value, method
FROM
(
SELECT a.*, b.method, row_number() OVER (partition by a.node, a.date ORDER BY CASE WHEN method IS NULL THEN 1 ELSE 0 END, datediff(day, a.date, b.timestamp)) rn
FROM a LEFT OUTER JOIN b
ON a.node = b.node AND a.date <= b.timestamp
) sq
WHERE rn = 1
ORDER BY node, date desc
| node | date | value | method |
|---|---|---|---|
| 01R-123 | 2023-01-10 | 9 | Jet |
| 01R-123 | 2023-01-09 | 11 | Jet |
| 01R-123 | 2023-01-08 | 18 | Jet |
| 01R-123 | 2023-01-07 | 87 | Jet |
| 01R-123 | 2023-01-06 | 32 | Jet |
| 01R-123 | 2023-01-05 | 22 | Jet |
| 01R-123 | 2023-01-04 | 16 | Jet |
| 01R-123 | 2023-01-03 | 24 | Jet |
| 01R-123 | 2023-01-02 | 24 | Jet |
| 01R-123 | 2023-01-01 | 24 | Jet |
-- Try with some more complicated data (differing values and a row with no matches)
-- to confirm no mistakes in query
delete from a;
insert into a values
('01R-123','2024-01-10',09),
('01R-123','2023-01-10',09),
('01R-123','2023-01-09',11),
('01R-123','2023-01-08',18),
('01R-123','2023-01-07',87),
('01R-123','2023-01-06',32),
('01R-123','2023-01-05',22),
('01R-123','2023-01-04',16),
('01R-123','2023-01-03',24),
('01R-123','2023-01-02',24),
('01R-123','2023-01-01',24);
delete from b;
insert into b values
('01R-123','2023-01-10','Jet'),
('01R-123','2023-01-09','Jet'),
('01R-123','2023-01-08','Jet2'),
('01R-123','2023-01-05','Jet'),
('01R-123','2023-01-04','Jet'),
('01R-123','2023-01-03','Jet3'),
('01R-123','2022-12-30','Jet'),
('01R-123','2022-12-29','Jet'),
('01R-123','2022-12-28','Jet'),
('01R-123','2022-12-25','Jet');
SELECT node, date, value, method
FROM
(
SELECT a.*, b.method, row_number() OVER (partition by a.node, a.date ORDER BY CASE WHEN method IS NULL THEN 1 ELSE 0 END, datediff(day, a.date, b.timestamp)) rn
FROM a LEFT OUTER JOIN b
ON a.node = b.node AND a.date <= b.timestamp
) sq
WHERE rn = 1
ORDER BY node, date desc
| node | date | value | method |
|---|---|---|---|
| 01R-123 | 2024-01-10 | 9 | null |
| 01R-123 | 2023-01-10 | 9 | Jet |
| 01R-123 | 2023-01-09 | 11 | Jet |
| 01R-123 | 2023-01-08 | 18 | Jet2 |
| 01R-123 | 2023-01-07 | 87 | Jet2 |
| 01R-123 | 2023-01-06 | 32 | Jet2 |
| 01R-123 | 2023-01-05 | 22 | Jet |
| 01R-123 | 2023-01-04 | 16 | Jet |
| 01R-123 | 2023-01-03 | 24 | Jet3 |
| 01R-123 | 2023-01-02 | 24 | Jet3 |
| 01R-123 | 2023-01-01 | 24 | Jet3 |