Join with date range

[CarstenWiedemann]

Hi all,

I have a table (a) with time sheet entries which I need to enrich with employee details from another table (b). B stores revisions of each employee to keep historic data.

Now I need to match the time sheet entries with the correct employee details according to the date of the entry.

The condition should be something like this:

where a.employeenumber = b.employeenumber and
  a.entrydate >= b.startdate and
  a.entrydate <= b.enddate

I tried creating an artificial end date and creating all entries in between for the employee details with the "Generate Rows"-tool which resulted in a very large output.

Isn't there any better way to solve this? Any hints warmly welcome!

Best

Carsten

[DataNath · Accepted solution]

Hi Carsten,

Could you not just join all matching entries on employee number, and then add a filter which removes rows that aren't within the range? Instead of lumping it into one step. If not, some screenshots/sample data may help to inspire a solution!

[CarstenWiedemann]

Well... that was too easy.

Thank you very much for this (obvious) solution!

Carsten