I need a dynamic lookup on a table consisting of tiers
Table A:
Type
Holding Company
Company
Spend
Date
Table B
Type
Holding Company
Company
Rebate
Amount from
Amount to
Date from
Date to
The user should be able to filter by Type, Holding Company ,Company and see the total spend for a particular date range selected from Table A in their report
The total amount (for the date range selected) should look up table B and find the correct rebate amount in the tier for selection.
The user should be able to select a Company within the Holding company and that spend total should correspond to the tier for that Company in Table B to obtain the rebate - The total spend will differ on a Holding Company versus a Company. Mutiple values is expected as the user should be able to select multiple Holding Companies in their selection
| Type | Holding Company | Company | Spend | Date |
| Gas | HLD - CompanyA | CompanyS | 6,675 | 2020-01-29 |
| Gas | HLD - CompanyB | CompanyT | 9,262 | 2020-01-29 |
| Gas | HLD - CompanyA | CompanyV | 2,430 | 2020-01-29 |
| Gas | HLD - CompanyB | CompanyW | 5,785 | 2020-01-29 |
| Gas | HLD - CompanyA | CompanyX | 5,239 | 2020-01-29 |
| Gas | HLD - CompanyB | CompanyZ | 12,835 | 2020-01-29 |
| Gas | HLD - CompanyA | CompanyS | 11,015 | 2020-02-06 |
| Gas | HLD - CompanyC | CompanyT | 7,227 | 2020-02-06 |
| Gas | HLD - CompanyD | CompanyV | 14,104 | 2020-02-06 |
| Holding Company | Rebate% | Amount from | Amount To | Date From | Date To |
| HLD - CompanyA | 1.00% | 20,000 | 40,000 | 2020-01-01 | 2020-12-31 |
| HLD - CompanyA | 3.00% | 40,001 | 60,000 | 2020-01-01 | 2020-12-31 |
| HLD - CompanyA | 5.00% | 60,001 | 9,999,999 | 2020-01-01 | 2020-12-31 |
| HLD - CompanyB | 2.00% | 50,000 | 75,000 | 2020-01-01 | 2020-12-31 |
| HLD - CompanyB | 4.00% | 75,001 | 100,000 | 2020-01-01 | 2020-12-31 |
| HLD - CompanyB | 6.00% | 100,001 | 250,000 | 2020-01-01 | 2020-12-31 |
| HLD - CompanyC | 2.50% | 150,000 | 175,000 | 2020-01-01 | 2020-12-31 |
Expected output:
| | | Rebate appl | Rebate band | Calc Rebate |
| HLD - CompanyA | 89,878 | 5% | 60,001 | 4,493.90 |
| HLD - CompanyB | 99,342 | 4% | 75,001 | 3,973.68 |
| HLD - CompanyC | 129,296 | 0% | 150,000 | -00 |
| HLD - CompanyD | 99,109 | 2% | 80,000 | 1,982.18 |
