cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
rujimenez Frequent Visitor
Frequent Visitor

many to many

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.

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

Re: many to many

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?Smiley Happy 

 

Regards

rujimenez Frequent Visitor
Frequent Visitor

Re: many to many

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.

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 256 members 2,659 guests
Please welcome our newest community members: