HR Data modeling {Interval Dates}

[#1]

Hello everybody,

I've a problem building a data model for HR APP.

- I have 2 different tables of  measures, each table is connected to the other by the PERSONAL_ID and date FROM date TO

Example:

Table 1 : PERSONAL_DATA

PERSONAL_ID	FROM	TO	ADRESS
0000001	20160101	20160131	A
0000001	20160201	99991231	B

Table 2: CONTRACT

PERSONAL_ID	FROM	TO	CONTRACT_ID
0000001	20160101	20160115	A
0000001	20160116	20160229	B
0000001	20160301	99991231	C

What i need is to be able to use a CALENDAR table, that if I select DATE: 20160116 and the result be like:

PERSONAL_ID	ADRESS	CONTRACT
00001	A	B

And if i select whole January:

PERSONAL_ID	ADRESS	CONTRACT
000001	A	A
000001	A	B

I tried to build a LINK table between the Calendar and the 2 tables and i doesn't work properly

I tried to concatenate both tables and link the calendar by a link table ( Datelink, Date) and it doesn't either.

How you would build the data modeling without increasing the 2 tables registers?

[marcus_sommer]

You need to match your from-to fields to a calendar per IntervalMatch.

- Marcus