Count from Different Table

[Jamie_wong]

I have 3 Tables:

Table 1 (Name, Gender, Start Working Date, End Working Date)

John   M   2018-04-28   2019-12-31

Adam  M  2018-10-11   2019-12-31

Kerry   F    2019-06-04  2020-12-31

Table 2 (AnnualEvent, Event Date)

Event A  2019-01-25

Event B   2019-08-31

Event A  2019-12-25

Table 3 (Leave Type, Gender, #Day)

CL    F       4

CL    M     4

ML   F      45

PL    M     30

I would like to:

(1) Count number of event (EventCount) that the Event Date is between the Start Working Date and End Working Date

(2) Lookup the #Day in Table 3 based on the Gender and Type 

I want this number in new Table with column (Year, Name, Gender, EventCount, CL, ML ). 

2018   Adam  M    1     4    30 

2018   John   M     2     4    30 

2019   Adam  M    3     4    30 

2019   Kerry   F      2    4    45 

2019   John   M     3    4    45

2020   Kerry   F      3    4    30 

[v-yuta-msft]

@Jamie_wong ,

Could you please clarify the logic of achieving the EventCount, CL, ML in the new table?

Regards,

Jimmy Tao