IntervalMatch() - how to use it in this case?

[datanibbler]

Hi,

I have a new requirement, something I haven't done before, at least not in this constellation, so some help would be nice:

- I have a table with all the employees we have (or had) in our company, together with their joining_date and leaving_date
=> There are those that are currently with the company
=> There are those who have already left (with the corresp. leaving_date)

I have no specific date_field in the table. In my app I have a master_calendar, that has every day.

Now I have to use IntervalMatch to find out which employees were there on a given day, so that day (from the calendar)

must be matched to the interval between an employee's joining_date and his/her leaving_date.

I used IntervalMatch() before, but then I had a list - an inline_table - of intervals. This time around, I have two tables - one (the calendar) has every single day and the other has the joining_date and leaving_date.

So how to go about this?

Thanks a lot!

Best regards,

DataNibbler

[hic]

You should use a While-loop to create all dates in the interval, and then connect the individual dates to your calendar. Take a look at Creating Reference Dates for Intervals.

HIC

[datanibbler]

Hi Henric,

thanks for the answer!

I don't think I need a new LOOP to create dates, however - I already have that as part of my master_calendar. Most tables in the app, like in all of my apps, are linked via the date, so I necessarily have every date in there.

I'm just trying it out.

Talk to you soon!

[hic]

I think you do need a loop... The goal is to have three tables:

1. EmploymentPeriods: One record per employee employment period. I.e. one record per person, unless a person has been employed during two different periods.
2. EmploymentPeriodDate: One record per date and employment period. I.e. the same date appears several times - once per person.
3. Master Calendar: One record per date.

You need the loop to create table 2.

HIC

[Anonymous]

Hi,

You may have to create temp table with following script,

IntervalMatch (master_calendar_date) LOAD joining_date, leaving_date Resident employee;

And join it back to your original employee table.

[datanibbler]

Hi Henric,

okay, that sounds sensible - and how would I go about creating that table?

[hic · Accepted solution]

Employees:
Load EmployeeD, JoiningDate, LeavingDate, ...
          From Employees;

Employees_x_Dates:

Load EmployeeD,
          ...,
          Date( JoiningDate + IterNo() – 1 ) as ReferenceDate
          Resident Employees
          While IterNo() <= LeavingDate - JoiningDate + 1 ;

And then you link your reference date to your calendar. Check the link in my previous answer.

HIC

[datanibbler]

Ah, ok.

So you do entirely without the IntervalMatch, but instead you "pump up" the table to have one record per day per employee, yes?

OK. That is what I do in some other instances already. I didn't think of it because I was focused on finding a solution using the IntervalMatch() - I guess that is a strong sign that I need to be having lunch 😉 - but I guess that is the easier way.

Thanks a lot!

Best regards,

DataNibbler