[SQL] SQL match current or next closest [duplicate]

I have a data set with web traffic table and members table. A member has an effective date for coverage, but can still register to use the site ahead of their effective date, with limited functionality until that effective date.

I'm looking for help with logic that will join traffic to member data, using the current month of membership data if available, or use the nearest future date if the current date doesn't exist in the member data. Any ideas? Thanks!

Traffic

MemberId Date Location Note
A 1/1/2024 example.com/somewhere Coverage starts 1/1, traffic date is 1/1
B 1/1/2024 example.com/anotherplace Coverage starts 6/1, traffic 5 months earlier

Members

MemberId EffectiveDate Name
A 1/1/2024 Andy
B 6/1/2024 Bob

Desired Output

TrafficDate Location Name Notes
1/1/2024 example.com/somewhere Andy Andy has coverage in the same month as traffic, this is pretty standard
1/1/2024 example.com/anotherplace Bob Bob uses the site with some limited functionality, and will have more functionality on 6/1

Note: This is fairly generic to any SQL but will ultimately be implemented via SparkSql so I've included that tag here as well in case there are helpful additional tools.

Continue reading...