Joining Multiple Date Fields onto a Master calendar, including date fields needing an interval match

[cary]

Hello,

I am building out a data model, but struggling to get all the dates to connect appropriately. I have a master calendar generated into the model. I have two additional tables, one that has staff info, including their position start and end date, and one other table that has information about staff promotions, including the date of the promotion. I am able to connect the two tables by a commonID among the two.

I need to be able to count the number of staff for each year they have been at the organization. For example, if their start date was in 2019 and their end date was in 2022, if I perform a count on the front end, I need them to be accounted for in 2019, 2020, 2021 and 2022. I was able to accomplish this by performing an IntervalMatch() on the start and end date from the staff table, and joining it on to a date string field in the master calendar.

Now is where I am getting the complication. I need to also be able to count the number of promotions for staff members in each given year. So, if they were promoted in 2020 and 2022, then their promotion needs to be reflected in the front end for 2020 and 2022. However, the issue I am having is joining the promotion date back onto the master calendar. Because I am already using an interval match on the other two dates from the first table and connecting that on to the master calendar, I am not sure how I can now bring in this additional promotion date from the third table onto the master calendar without creating a circular reference.

I imagine I need to create some sort of additional link table that will be able to account for the interval match of the start and end date for employees, in addition to accounting for the promotion date field.

Any suggestions would be helpful. Attaching current data model example photo.

Thanks,

Cary

[LRuCelver]

You could restructure your datamodel so that the start and end dates do not represent the dates when a person joined and left the company, but rather when a person started assuimg a position and when they where promoted. When a person if promoted, their current position gets and end date and a new entry is made for that person with the start date being the day after the end date of the previous position. I'll try to share a mockup later.

Edit: Here is the mockup:

[cary]

Thank you! Unfortunately the promo dates and start dates don’t align exactly, but I would be curious to see your mock up!

[LRuCelver]

Here is the script for a mockup:

// DATA ==============================================

Staff:
NoConcatenate Load * Inline [
    PersonId,   Name
    1,          Anna
    2,          Bob
    3,          Charlie
];

Promotions:
NoConcatenate Load
    StartDateId,
    If(EndDateId = '', Null(), EndDateId) as EndDateId,
    If(EndDateId = '', Num#(Text(Date(Today(), 'YYYYMMDD'))), EndDateId) as IntervalEndDateId,
    PersonId,
    Position
Inline [
    StartDateId,    EndDateId,  PersonId,   Position
    20230101,       20230228,   1,          Manager
    20230301,       20230930,   1,          Director
    20231001,       ,           1,          CEO
    20230601,       20230930,   2,          Manager
    20231001,       ,           2,          Director
    20230815,       ,           3,          Assistant
];


// CALENDAR ==========================================

GetMinDate:
NoConcatenate Load
    Min(StartDateId) as MinDate
Resident Promotions;

Let vMinDate = Num(Date#(Peek('MinDate'), 'YYYYMMDD'));
Let vMaxDate = Num(Today());

Drop Tables GetMinDate;

"Master Calendar":
NoConcatenate Load
    *,
    Num#(Date(Date, 'YYYYMMDD')) as DateId,
    Year(Date) as Year,
    Month(Date) as Month,
    Week(Date) as Week;
//  ...
Load
    Date($(vMinDate) + IterNo() - 1) as Date
AutoGenerate 1 While $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Let vMinDate;
Let vMaxDate;


// INTERVAL MATCH ====================================

Join(Promotions) Load Distinct
    StartDateId,
    IntervalEndDateId,
    AutoNumberHash128(StartDateId, IntervalEndDateId) as IntervalKey
Resident Promotions;

"Interval Match Table":
NoConcatenate Load
    DateId
Resident "Master Calendar";

Inner Join IntervalMatch(DateId) Load Distinct
    StartDateId,
    IntervalEndDateId
Resident Promotions;

Join Load Distinct
    StartDateId,
    IntervalEndDateId,
    IntervalKey
Resident Promotions;

Drop Field StartDateId From "Interval Match Table";
Drop Field IntervalEndDateId;

[cary]

Thank you!! I am going to give this a try