Join on Generated Dates

[elclark]

Hello,

I have the below two tables, what I'm trying to do is combine the two tables based on state, city, and date to bring in the approved lodging rate into Table B (see Desired Output). I tried doing a formula and generate rows so it would give me all dates between the season begin and end timeframe but I'm having issues when the dates go from one year to the next (example October to May). The year doesn't necessarily matter, I'm really just comparing based on Month and Day. Any help would be much appreciated.

Table A

STATE	CITY	SEASON BEGIN	SEASON END	APPROVED LODGING RATE
AZ	Phoenix	October 1	May 31	$151
AZ	Phoenix	June 1	August 31	$96
AZ	Phoenix	September 1	September 30	$151

Table B

Booking Date	Hotel State	Hotel City	Booked Rate
2/18/2022	AZ	PHOENIX	$130

Desired Output

Book Date	Hotel State	Hotel City	Booked Rate	APPROVED LODGING RATE
2/18/2022	AZ	PHOENIX	$130	$151
9/05/2022	AZ	PHOENIX	$180	$151
07/15/2022	AZ	PHOENIX	$100	$96

[MarqueeCrew]

@elclark ,

please try this macro out for size:

https://community.alteryx.com/t5/Public-Community-Gallery/CReW-Generate-Date-Rows/ta-p/926163

cheers,

 mark

[Qiu · Accepted solution]

@elclark 

There are many DateTime Tools and hope you dont mind.

For the issue  (example October to May), I added one year to Season End if Season End is smaller than Season Begin given they are in the same year.
And I think you are missing some input data in for Table B. Kindly check.