Really struggling with matching on a field and match a date range and not lose records?

[mdorio]

Hi All,

I could use your help.  I'm really struggling with this one.  Here are the details below, and I have attached my flow so far but can't figure out how to get the record with the 2016 stay to be part of the output.

I have one file of vacation stays.  Each stay has a plan ID.

I have one file of vacation plans.  Each plan has a date range, a plan name, and a rate.

FIle #1:  Stays

Name	Stay Date	Plan ID
Smith	7/10/2017	1
Smith	7/10/2018	1
Smith	7/10/2019	1
Smith	7/10/2016	1

File #2:  Plans

Plan ID	Start Date	End Date	Plan Name	Rate
1	4/17/2017	12/31/2017	SummerPlan	1400
1	01/01/2018	12/31/2018	SummerPlan	1400
1	01/01/2019	12/31/2019	SummerPlan	1400
1	01/01/2020	12/31/2020	SummerPlan	1600

The output I want is this:

Name	Stay Date	Plan ID	Plan Name	Rate
Smith	7/10/2017	1	Summer	1400
Smith	7/10/2018	1	Summer	1400
Smith	7/10/2019	1	Summer	1400
Smith	7/10/2016	1	Not Found	Not Found

I do a join on Plan ID, but since I don't have anything else to join on, this creates multiple records for each record in the stay file.  So once I get that output, I filter out where the stay date is within the start date/end date for the plan.  This gets me the actual plan name/rate I want.  But now I have to figure out how to get the record from the stays file that did have a match on plan ID, but I don't want to lose the record if we can't find a valid plan because the stay date is not within start/end...and want to keep it.

Any guidance or advice would be truly appreciated!

Mike

[TonyA · Accepted solution]

How about this? Before you eliminate the out of range rows, sum on the KeepRange flag. Then process all the rows and Union with the rows where the sum of KeepRange was 0.

Edit: I tweaked the workflow to line up a little better with your output.

[mdorio]

Thanks so much Tony!  It seems to be working.  I'm going to do some testing on larger datasets to confirm. 

Thank you again for your expertise and solution.  Truly appreciated!