Essentially, using SQL Server, I want to take the “Gross Amt” from the current table below (which is derived from a computed column upon INSERT or UPDATE) and then have that “Gross Amt” run through the “Tiered Table” to derive the “Total A $” in the desired output table.
I figured this would likely need to be done with a trigger (maybe a function?) since this calculation would happen upon INSERT or UPDATE and because the conditional logic could be incorporated into it since there are different tier tables with different Min/Max values and percentage thresholds for different tiers.
The example below is, of course, cumulative, and functions like marginal income tax rates, the first 10000 is at 90% (for Total A), the second tier calculates the 19999 at 60%, the third 69999 at 40%, and so on, etc. There are other regions with different tiers that are just simple lookup reference values.
Tiered table:
| RegionID | TierNo | Min | Max | Total A | Total B |
|---|---|---|---|---|---|
| 3 | 1 | 0 | 10000 | .90 | .10 |
| 3 | 2 | 10001 | 30000 | .60 | .40 |
| 3 | 3 | 30001 | 100000 | .40 | .60 |
| 3 | 4 | 100001 | 500000 | .40 | .60 |
| 3 | 5 | 500001 | 999999999999 | .20 | .80 |
Current table sample:
| TransID | RegionID | GrossAmt | Total A % | Total A $ | Net Amt |
|---|---|---|---|---|---|
| 100001 | 3 | 125000 |
Desired output:
| TransID | RegionID | GrossAmt | Total A % | Total A $ | Net Amt |
|---|---|---|---|---|---|
| 100001 | 3 | 125000 | 0.47 | 59000 | 66000 |
Any ideas or guidance would be extremely helpful and appreciated.