SQL merge entries with start and end dates to entries with single dates

I have two tables, lets call them A and B. Table A has data regarding specific events and has a unique key column pairing of event_date and person. Table B has aggregate data over time and thus has key columns start_date,end_date and person. The date ranges in table B will never overlap for a given person so end_date is not strictly necessary for the composite key.

Below are two examples

SELECT event_date, person
FROM A

event_date	person
2021-10-01	Alice
2021-10-01	Bob
2021-10-05	Bob
2021-11-05	Bob

SELECT start_date, end_date, person, attribute
FROM B

start_date	end_date	person	attribute
2021-10-01	2021-11-01	Alice	Attribute 1
2021-10-01	2021-11-01	Bob	Attribute 1
2021-11-01	2021-12-01	Bob	Attribute 2

I would like to add the attribute column to table A. The merger should consider in which date range the event_date column falls into and choose the appropriate attribute. The final table after the merge should look like this:

event_date	person	attribute
2021-10-01	Alice	Attribute 1
2021-10-01	Bob	Attribute 1
2021-10-05	Bob	Attribute 1
2021-11-05	Bob	Attribute 2

How would one go about solving this?

Answer

You can try to JOIN by BETWEEN dates.

SELECT a.*,b.attribute
FROM A a
JOIN B b
ON a.event_date BETWEEN b.start_date AND b.end_date
AND a.person = b.person