I have a CRM where I need to calculate agent utilisation. My problem is that I have agents who have shifts on different days. I need to calculate the number of agents whose working day is true. From here I can then work out the total capacity hours for the month based on 7.6 hour days.
There are 4 tables in play
[Agents - Time Entries] - This is the agent table which liks to Work Shifts via Workday ID
[Work Shifts] - This is the roster table which links to Working Days via Workday ID
[Working Days] - This is working days table that links to the Date Table via Weekday ID
[Date Table] - Is the date table which also has day types for public holidays.
In my report I need to work out capacity hours based on the number of working agents per day for that period. So for example, If there are 30 days it would be total working days for the period multiplied by 7.6. Each day would be multipled by the number of working agents, then added together for the period to give total working days. The number of working agents would vary from day to day, so it would need to be able to calculate agent count per day.
Tables Below
| Agent ID | Agent Name | Agent Disabled | Workday ID | Date Created |
| 3 | Agent One | FALSE | 5 | 9/01/2019 9:37 |
| 13 | Agent Two | FALSE | 3 | 25/07/2022 7:58 |
| 14 | Agent Three | FALSE | 8 | 27/07/2022 5:58 |
Work Shifts
| Workday ID | Workday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
| 1 | 08:00-5:00PM Shift | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| 2 | 24 Hours | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE | TRUE |
| 3 | 7:30 Shift | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| 4 | 8:00 Shift | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| 5 | 8:30 Shift | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| 6 | Part Time Shift | FALSE | FALSE | TRUE | TRUE | TRUE | FALSE | FALSE |
| 7 | South African Shift | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE | FALSE |
| 8 | 4 Day Week (Tuesday Off) | TRUE | FALSE | TRUE | TRUE | TRUE | FALSE | FALSE |
Working Days
| Workday ID | Day Name | Weekday ID | Is Working Day | Working Hours |
| 1 | Monday | 2 | TRUE | 9 |
| 1 | Tuesday | 3 | TRUE | 9 |
| 1 | Wednesday | 4 | TRUE | 9 |
| 1 | Thursday | 5 | TRUE | 9 |
| 1 | Friday | 6 | TRUE | 9 |
| 1 | Saturday | 7 | FALSE | 9 |
| 1 | Sunday | 1 | FALSE | 9 |
| 2 | Monday | 2 | TRUE | 24 |
| 2 | Tuesday | 3 | TRUE | 24 |
| 2 | Wednesday | 4 | TRUE | 24 |
| 2 | Thursday | 5 | TRUE | 24 |
| 2 | Friday | 6 | TRUE | 24 |
| 2 | Saturday | 7 | TRUE | 24 |
| 2 | Sunday | 1 | TRUE | 24 |
| 3 | Monday | 2 | TRUE | 9 |
| 3 | Tuesday | 3 | TRUE | 9 |
| 3 | Wednesday | 4 | TRUE | 9 |
| 3 | Thursday | 5 | TRUE | 9 |
| 3 | Friday | 6 | TRUE | 9 |
| 3 | Saturday | 7 | FALSE | 9 |
| 3 | Sunday | 1 | FALSE | 9 |
| 4 | Monday | 2 | TRUE | 9 |
| 4 | Tuesday | 3 | TRUE | 9 |
| 4 | Wednesday | 4 | TRUE | 9 |
| 4 | Thursday | 5 | TRUE | 9 |
| 4 | Friday | 6 | TRUE | 9 |
| 4 | Saturday | 7 | FALSE | 9 |
| 4 | Sunday | 1 | FALSE | 9 |
| 5 | Monday | 2 | TRUE | 9 |
| 5 | Tuesday | 3 | TRUE | 9 |
| 5 | Wednesday | 4 | TRUE | 9 |
| 5 | Thursday | 5 | TRUE | 9 |
| 5 | Friday | 6 | TRUE | 9 |
| 5 | Saturday | 7 | FALSE | 9 |
| 5 | Sunday | 1 | FALSE | 9 |
| 6 | Monday | 2 | FALSE | 9 |
| 6 | Tuesday | 3 | FALSE | 9 |
| 6 | Wednesday | 4 | TRUE | 9 |
| 6 | Thursday | 5 | TRUE | 9 |
| 6 | Friday | 6 | TRUE | 9 |
| 6 | Saturday | 7 | FALSE | 9 |
| 6 | Sunday | 1 | FALSE | 9 |
| 7 | Monday | 2 | TRUE | 8 |
| 7 | Tuesday | 3 | TRUE | 8 |
| 7 | Wednesday | 4 | TRUE | 8 |
| 7 | Thursday | 5 | TRUE | 8 |
| 7 | Friday | 6 | TRUE | 8 |
| 7 | Saturday | 7 | FALSE | 8 |
| 7 | Sunday | 1 | FALSE | 8 |
| 8 | Monday | 2 | TRUE | 9 |
| 8 | Tuesday | 3 | FALSE | 9 |
| 8 | Wednesday | 4 | TRUE | 9 |
| 8 | Thursday | 5 | TRUE | 9 |
| 8 | Friday | 6 | TRUE | 9 |
| 8 | Saturday | 7 | FALSE | 9 |
| 8 | Sunday | 1 | FALSE | 9 |
Date Table
| Date | Day Name | Date Type | Capacity | Weekday ID |
| Saturday, 1 July 2023 | Saturday | Weekend | 0 | 7 |
| Sunday, 2 July 2023 | Sunday | Weekend | 0 | 1 |
| Monday, 3 July 2023 | Monday | Weekday | 7.6 | 2 |
| Tuesday, 4 July 2023 | Tuesday | Weekday | 7.6 | 3 |
| Wednesday, 5 July 2023 | Wednesday | Weekday | 7.6 | 4 |
| Thursday, 6 July 2023 | Thursday | Weekday | 7.6 | 5 |
| Friday, 7 July 2023 | Friday | Weekday | 7.6 | 6 |
| Saturday, 8 July 2023 | Saturday | Weekend | 0 | 7 |
| Sunday, 9 July 2023 | Sunday | Weekend | 0 | 1 |
| Monday, 10 July 2023 | Monday | Weekday | 7.6 | 2 |
| Tuesday, 11 July 2023 | Tuesday | Weekday | 7.6 | 3 |
| Wednesday, 12 July 2023 | Wednesday | Weekday | 7.6 | 4 |
| Thursday, 13 July 2023 | Thursday | Weekday | 7.6 | 5 |
| Friday, 14 July 2023 | Friday | Weekday | 7.6 | 6 |
| Saturday, 15 July 2023 | Saturday | Weekend | 0 | 7 |
| Sunday, 16 July 2023 | Sunday | Weekend | 0 | 1 |
| Monday, 17 July 2023 | Monday | Weekday | 7.6 | 2 |
| Tuesday, 18 July 2023 | Tuesday | Weekday | 7.6 | 3 |
| Wednesday, 19 July 2023 | Wednesday | Weekday | 7.6 | 4 |
| Thursday, 20 July 2023 | Thursday | Weekday | 7.6 | 5 |
| Friday, 21 July 2023 | Friday | Weekday | 7.6 | 6 |
| Saturday, 22 July 2023 | Saturday | Weekend | 0 | 7 |
| Sunday, 23 July 2023 | Sunday | Weekend | 0 | 1 |
| Monday, 24 July 2023 | Monday | Weekday | 7.6 | 2 |
| Tuesday, 25 July 2023 | Tuesday | Weekday | 7.6 | 3 |
| Wednesday, 26 July 2023 | Wednesday | Weekday | 7.6 | 4 |
| Thursday, 27 July 2023 | Thursday | Weekday | 7.6 | 5 |
| Friday, 28 July 2023 | Friday | Weekday | 7.6 | 6 |
| Saturday, 29 July 2023 | Saturday | Weekend | 0 | 7 |
| Sunday, 30 July 2023 | Sunday | Weekend | 0 | 1 |
| Monday, 31 July 2023 | Monday | Weekday | 7.6 | 2 |
