How to combine information from a main table and a log table in SQL Server

I currently have 2 tables in my database that i need to combine information from. One of them is a log table, that registers the status change of all products in the system. Structure example:

ChangeDate	Product	LastStatus	NewStatus
2021-10-01	A	New	Aging
2021-11-02	A	Aging	Ressuply
2021-11-25	A	Ressuply	OFF
2021-08-03	B	New	Aging
2021-09-04	B	New	Aging
2021-10-13	B	Ressuply	OFF

The other one is a fact table that contains each sale of each product. Structure example:

Date	Product	PricePaid
2021-10-02	A	124
2021-11-05	A	115
2021-08-25	B	223
2021-09-03	B	218

I want to write a statement that brings me the product’s status at the time when it was sold. Desired result:

Date	Product	PricePaid	StatusAtTime
2021-10-02	A	124	Aging
2021-11-05	A	115	Ressuply
2021-08-25	B	223	Aging
2021-09-03	B	218	Aging

I’ve been banging my head for about a week now, and can´t seem to find an answer to this. I´d guess the biggest challenge is performance, since the fact table has around 2M lines, and the log table is quite large as well.

Thank you guys in advance 🙂

Answer

For example

select top(1) with ties s.*, l.NewStatus
from sales s
join statusLog l on s.Product = l.Product and s.Date >= l.ChangeDate
order by row_number() over(partition by s.Product, s.Date order by l.ChangeDate desc);

With the sample data returns

Date    Product PricePaid   NewStatus
2021-10-02  A   124 Aging
2021-11-05  A   115 Ressuply
2021-08-25  B   223 Aging
2021-09-03  B   218 Aging