Hi Experts, I have a group of employees in a sales department who are paid based on actual results versus goals in the region(s) where they work. An employee can work in one region for the entire year, or they can have a mid-year move between one region and another, or they can be shared across regions, or they can have a combination of these scenarios (see Employee_tbl for some examples).
Actual results for regions are reported monthly as the year progresses, with the latest month reported shown in the Period_tbl. Goals are known for all 12 months of the year for each region. For the length of each assignment, the sum of Actual results is compared to the sum of the Goals. But, I’m looking for a full-year estimate of performance, and for any unreported months of an assignment, the Goal is used as a proxy for the Actual results. For example, Employee_001’s Actual results would be calculated by taking the sum of the Actual results for RegA from Jan through Sep and then adding the Goals from Oct through Dec. (i.e. 920 + 230). Employee_001’s Goals would be calculated by taking the sum of the goals from Jan through Dec (i.e. 1,060) – please see Performance_tbl.
The output that I am looking for via a query is a value for the Actual results and a value for the Goals, for each assignment, for each employee, as shown in the Output_tbl. Thank you.