Im currently working on a report which looks at various time periods (KPIS) between a customer journey and checks whether they were in timescale or not.
What makes this complicated is that a customer may have any number of on hold reason date periods which need to be deducted from the KPI periods. These may overlap each other.
I have two tables which im trying to compare:
The first is the on hold table which lists each on hold per customer id by start date:
| Customer ID | On Hold reason | Hold Start Date | Hold End Date | On Hold Index | Total Working Days on hold |
| 1234 | Reason1 | 01/01/2021 | 10/01/2021 | 1 | 6 |
| 1234 | Reason2 | 03/01/2021 | 07/01/2021 | 2 | 4 |
| 1234 | Reason3 | 04/01/2021 | 14/01/2021 | 3 | 9 |
| 1234 | Reason4 | 12/01/2021 | 12/01/2021 | 4 | 1 |
| 1234 | Reason5 | 15/01/2021 | 17/01/2021 | 5 | 1 |
| 1234 | Reason6 | 25/01/2021 | 26/01/2021 | 6 | 2 |
| 1234 | Reason7 | 26/01/2021 | 28/01/2021 | 7 | 3 |
| 1234 | Reason8 | 27/01/2021 | 28/01/2021 | 8 | 2 |
The second table is the customer KPI table which lists the KPI periods which I want to know the working days for:
| Customer ID | Period 1 Start Date | Period 1 End Date | Period 1 working days | Days After hold reduction | Period 2 Start Date | Period 2 End Date | Period 2 working days | Days After hold reduction |
| 1234 | 01/01/2021 | 10/01/2021 | 6 | 0 | 15/01/2021 | 25/01/2021 | 7 | 5 |
I want to compare all of the on hold days against each other to see if they were in the same hold period and then whether they were in the KPI period in the customer table and then get the on hold days to deduct from the working days. I do not want to double count any hold day instance.
Happy to clarify any points.
Thanks in advance.
