many to many

[rujimenez]

I have two date tracked tables (Positions and Assignments) to allow tracking of changes to each. The Positions table contains a Position ID, effective start date and effective end date).   The assignment table contains Assignment ID, effective start date and effective end date (for the assignment).  It also references contains the Position ID as part of the record. 

I want to be able to display the applicable position and assignment information for a given date.  Since the start and end dates don't correlate, I'm having a problem trying to create a relationship.  Any suggestions would be greatly appreciated.

[v-ljerr-msft]

Hi @rujimenez,

---

I want to be able to display the applicable position and assignment information for a given date. 

---

How do you want to display the applicable position and assignment information for a given date, is it by the given date = effective start/end date or the given date is between effective start and end date?

Could you be more precisely with your requirements by posting some sample data with your expected result? 

Regards

[rujimenez]

The result will contain fields from records which are active between  the start and end dates of both tables.  A simple example:

Positions:

Position ID      Start Date      End Date       Position Name

1                     02/15/2013    11/10/2014   Original Position Name

1                     11/11/2014     03/21/2015  Position Name change 1

1                      03/22/2015                         Position Name change 2

2                      10/15/2014                         Another Position Name

Assignments:

Asg ID             Start Date       End Date        Position ID          Employee ID    Pay Grade

20                    10/10/2014     01/15/2015    1                         233333             11

20                     01/16/2015    11/15/2015     1                        233333             12

20                     11/16/2015     01/20/2016    1                         233333             13

23                      01/21/2016                           2                        2333333            13 

On 02/15/2015

Employee ID           Position Name                         Assignment ID          Pay Grade

233333                     Position Name change 1                20                           12

0n 02/22/2016

Employee ID           Position Name                         Assignment ID           Pay Grad

233333                    Position Name change 2                 23                           13

The result would be a snapshot of all employees and their assignments on a given date.