Skip to content
Advertisement

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

event_date person
2021-10-01 Alice
2021-10-01 Bob
2021-10-05 Bob
2021-11-05 Bob
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?

Advertisement

Answer

You can try to JOIN by BETWEEN dates.

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement