HI,
I have a table with some values, all I want join them based on following scenario.
Scenario: All employees manually updates their work KPIs everyday. The issue is when they forget to update or they are on leave.
1. When they updates, their score is 100.
2. When they are on leave their score should be automatically 100.
3. When they forget or any entry not found in the score table then in the mergd table there should be an entry where score should be 0.
Now I have 2 tables,
Employee Leave Table
| Employee ID | Full Name | Start Date | End Date | Duration |
| 1 | Emp 1 | 2nd Feb 2023 | 4th Feb 2023 | 3 |
| 1 | Emp 1 | 6th Feb 2023 | 6th Feb 2023 | 1 |
KPI Table (Manually Entered by Employee)
| Date | Emp Id | Full Name | Score |
| 1st Feb 2023 | 1 | Bruce Wayne | 100 |
| 5th Feb 2023 | 1 | Bruce Wayne | 100 |
| 7th Feb 2023 | 1 | Bruce Wayne | 100 |
| 10th Feb 2023 | 1 | Bruce Wayne | 100 |
Expected Table
| Date | Emp ID | Full Name | Score |
| 1st Feb 2023 (Manual) | 1 | Bruce Wayne | 100 |
| 2nd Feb 2023 (From Leave) | 1 | Bruce Wayne | 100 |
| 3rd Feb 2023 (From Leave) | 1 | Bruce Wayne | 100 |
| 4th Feb 2023 (From Leave) | 1 | Bruce Wayne | 100 |
| 5th Feb 2023 (Manual) | 1 | Bruce Wayne | 100 |
| 6th Feb 2023 (From Leave) | 1 | Bruce Wayne | 100 |
| 7th Feb 2023 (Manual) | 1 | Bruce Wayne | 100 |
| 8th Feb 2023 (Not Entered) | 1 | Bruce Wayne | 0 |
| 9th Feb 2023 (Not Entered) | 1 | Bruce Wayne | 0 |
| 10th Feb 2023 (Manual) | 1 | Bruce Wayne | 100 |
Based on expected table my visuals will show.
any ideas how can I achieve this?
