Please see below:
Column J and I are the ones where I need help with:
* Does the customer have one contract with one service type for the entire year? If so, return SYSR (Single Year Single Rev)
* Does the customer have one contract with multiple services for the entire year? If so, return SYMR (Single Year Multi Rev)
* Does the customer have multiple contracts signed at the same time with one service? If so, return MYSR (Multi Year Single Rev)
* Does the customer have multiple contracts signed at the same time with multiple services? If so, return MYMR (Multi Year Multi Rev)
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Column J and I are the ones where I need help with:
* Does the customer have one contract with one service type for the entire year? If so, return SYSR (Single Year Single Rev)
* Does the customer have one contract with multiple services for the entire year? If so, return SYMR (Single Year Multi Rev)
* Does the customer have multiple contracts signed at the same time with one service? If so, return MYSR (Multi Year Single Rev)
* Does the customer have multiple contracts signed at the same time with multiple services? If so, return MYMR (Multi Year Multi Rev)
A | B | C | D | E | F | G | H | I | J | |
1 | Customer ID | Service Type | Contract ID | Contract Start Date | Contract End Date | Contract Entered Date | Contract Amount | Type | Revenue Month | Note |
2 | 1 | Cleaning | 2 | 1/1/2019 | 12/31/2019 | 12/15/2018 | 5,000.00 | SYSR | 12 | |
3 | 100 | Cleaning | 35 | 3/1/2019 | 12/31/2019 | 12/15/2018 | 2,000.00 | SYMR | 10 | |
4 | 100 | Maintenance | 35 | 3/1/2019 | 12/31/2019 | 12/15/2018 | 3,000.00 | SYMR | 10 | |
5 | 100 | Maintenance | 35 | 3/1/2019 | 12/31/2019 | 3/15/2019 | (1,000.00) | SYMR | 10 | Discount given due to service complaint |
6 | 582 | Maintenance | 79 | 4/1/2019 | 3/31/2020 | 2/15/2018 | 3,000.00 | MYSR | 24 | 24 because two contracts signed/entered at the same time |
7 | 582 | Maintenance | 80 | 4/1/2020 | 3/31/2021 | 2/15/2018 | 5,000.00 | MYSR | 24 | 24 because two contracts signed/entered at the same time |
8 | 582 | Maintenance | 85 | 4/1/2021 | 3/31/2022 | 3/10/2020 | 4,500.00 | SYSR | 12 | SYSR with 12 months because A NEW CONTRACT was ENTERED separate from the two that was signed and entered at the same time |
9 | 76 | Cleaning | 123 | 2/1/2019 | 1/31/2020 | 1/15/2019 | 3,000.00 | MYMR | 24 | |
10 | 76 | Installation | 123 | 2/1/2019 | 1/31/2020 | 2/1/2019 | 1,000.00 | MYMR | 24 | |
11 | 76 | Installation | 123 | 2/1/2019 | 1/31/2020 | 2/1/2019 | (500.00) | MYMR | 24 | Discount given as there was a delayed installation timing |
12 | 76 | Maintenance | 123 | 2/1/2019 | 1/31/2020 | 1/15/2019 | 3,000.00 | MYMR | 24 | |
13 | 76 | Cleaning | 254 | 2/1/2020 | 1/31/2021 | 2/1/2019 | 2,500.00 | MYMR | 24 | |
14 | 76 | Installation | 254 | 2/1/2020 | 1/31/2021 | 1/15/2019 | 1,500.00 | MYMR | 24 | |
15 | 76 | Maintenance | 254 | 2/1/2020 | 1/31/2021 | 2/1/2019 | 3,750.00 | MYMR | 24 |
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>