Home > Enterprise >  SQL merge entries with start and end dates to entries with single dates
SQL merge entries with start and end dates to entries with single dates

Time:04-04

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?

CodePudding user response:

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
  • Related