Linking Dates/Date Ranges in Separate Tables

[ciaran_mcgowan]

Hi,

I have 2 tables; 1 that contain Sales data and another that contains Employee data. They are linked by the Department name the employee works in/sale was made in. Each employee has a Start/End Date for their department and if they move department, the End Date is filled in and another record for them is created. A master calender was created from the date a Sale was made.

If I select a Date, the information about the Sale appears but it also lists every employee that every worked for the department the sale occurred in. Is there a way to only show the employees that where employed on the date a sale was made. And similarly, by selecting an employee, show all the sales that occurred when they were a member of that department.

In other words: Start Date > Sales Date > End Date = Show available Sales/Employees

Thanks.

[rwunderlich]

Take a look at IntervalMatch in the Ref Guide. I think that will do what you want.

-Rob

[ciaran_mcgowan]

Hi Rob,

Thanks for your reply.

I already tried that but it just created an infinite loop between the Sales table, the Employee table and the Master Calendar table (which is linked to the Sales table by SalesDate).

[danieloberbilli]

Hi Ciaran,

Please have a look at the qvw attached.

This is definitely not best practise, as first of all the join will probably be too slow for big tables. Nevertheless it should help to get an idea how to approach the problem.

To avoid the join please have a look at the different methods regarding 'slowly changing dimensions',

Kind Regards

Daniel