Connect the Date to find Cadre

[nuwanhwb87]

[edwin]

Assuming the user selects a Month and the Current Cadre means determine if the Emp is active in any date inside the selected Month:

[nuwanhwb87]

Can u please explain further How to link Date key

[edwin]

what i did was build a bridge that links all combinations of start + end date to dates where it is between the two dates.  for example:

start date 1/1/2020 end date 1/5/2020 will be linked to 
1/1/2020, 1/2/2020, ..up to 1/5/2020.

this means if any one date is selected, all EmpIDs with start and end date straddling the selected date will be a possible selection.  if you select on a higher level (month) then any emp with a linked date inside the month will be a possible selection.

the bridge would normally link your fact table and your calendar which is what you use to select a date, month, or year