Joining database with a date-period

Hi Wim,

This is not an easy one.  To do a join you need at least one common field between each file, this is not the case in your example. Now this could be done in a script.  I am just tring to think if it is possible to create a virtual field that ends up being the common field.  For the date range could it be any range?  So could you have one record that the range is Jan 1 2015 to Feb 28, 2015 and another with Jan 1 2015 to March 31, 2015.  If so how would the join work, would you select both these records or only the first one that is tested, much like regular joins?

Steve, any thought on how this could be done?

Thanks

Brian

Sorry, my fault.
It is like this:
File 1 contains an employercode, an employeecode and a date the employee had an accident or became sick. File 2 contains an employercode, an employeecode and a period the employee had a certain salary from that employer. If the employee got a promotion, a new record is added to file 2. An emplyee can have more than 1 job with different employers, but I want a new file which has all the records of file 1 and the salary the employee had at that moment from the employer in file 1. I also have to figure out a way to check file 2 for, I do not know the english word for it, periods that cross each other. It should not be possible to have 2 salaries from the same employer on a certain date.
I thought of sorting both files on employer- and employeecode and the date (for file 2 the starting date of the period). Then read a record from both files and compare first the employercode, if they are different, read the next record from the file with the lowest code, otherwise compare the employeecode and so on, but I am not sure about the best Ideacommands to use and how to create a new file with the fields form both files.
Hopefully this will make it clearer.
Thanks for looking at the problem and willing to help me.
Wim

Hi Wim,

Without having any data or IDEA in front of me I am thinking what you might want to do in IDEA is to do a join between the two files using the employercode and employeecode as the join keys, and selecting matches only.  Now you can do an extract and I think there is a @between funciton in which you can extract all transaction in which the date of the accident is between the salary dates.  You could then turn this into a script.

For the second problem.  I would probably sort by Employercode, Employeecode and Salary Start.  I would then do an extract using the @getnextvalue (I think that is the command) to compare it with the next record to see if they overlap.  If I get a chance tonight I will try to make a scenario and figure out the equation for you.

Good luck.

Brian

Hello Brian
 
Thank you for your help. I really appreciate it!
I think your solutions will work. I am not at the office today, so I can't try them now.
For the first problem I think I have to join every emplyer/employeecode from file 1 with the matching codes in file 2 and the other way around. I am using the pc-version of version 8 at the moment and I do not think there is such an option there. We are starting with a test of Idea servcer version 9 in january and I have been told that that is one option that the serverversion supplies, so I will have to wait till the test starts. That is no problem.
The second solution I can try tomorrow. I will let you know the results.
Thanks again for your help. Especially your first solution saves me a lot of coding!
 
Wim

Hello Brian
 
Both solutions worked !!!
For the first solution I had to use the visual connection and I had never used that before. I thought I had to wait for the serverversion, but it works also in the standalone version.
I found a few overlapping periods and that causes some problems, but to find those was the idea.
Thanks again for your input.
 
Wim