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:

Current table sample:

Desired output:

Any ideas or guidance would be extremely helpful and appreciated.

CodePudding user response：

Firstly, your tiers aren't quite right. For example, the first one begins at 0 and ends at 10000, but the next one starts at 10001, leaving anything between 10000 and 10001 unaccounted for. Instead, have your tiers abutting each other, and pick your intervals carefully using > AND <=.

Secondly, this doesn't need a trigger at all. You should just calculate this on the fly when you need it. Create a view or an inline Table Valued Function if you need to.

It looks like a fairly simple grouped join, which you can do neatly using CROSS APPLY. You just need to calculate how much to multiply for each tier: the lower of GrossAmt or Max, subtracting Min

SELECT
  c.*,
  [Total A %] = t.Total / c.GrossAmt,
  [Total A $] = t.Total,
  [Net Amt]   = c.GrossAmt - t.Total
FROM CurrentData c
CROSS APPLY (
    SELECT
      Total = SUM((v.ActualMax - t.Min) * t.[Total A])
    FROM Tiers t
    CROSS APPLY (VALUES(
      CASE WHEN c.GrossAmt < t.Max THEN c.GrossAmt ELSE t.Max END
    )) v(ActualMax)
    WHERE c.GrossAmt > t.Min
) t;

db<>fiddle

You can do this as an inline Table Valued Function if you want.

CREATE FUNCTION dbo.GetTieredTotal (@GrossAmt decimal(18,9))
RETURNS TABLE
AS RETURN
    SELECT
      Total = SUM((CASE WHEN @GrossAmt < t.Max THEN @GrossAmt ELSE t.Max END - t.Min) * t.[Total A])
    FROM Tiers t
    WHERE @GrossAmt > t.Min
;

You can then change the main query to

SELECT
  c.*,
  [Total A %] = t.Total / c.GrossAmt,
  [Total A $] = t.Total,
  [Net Amt]   = c.GrossAmt - t.Total
FROM CurrentData c
CROSS APPLY dbo.GetTieredTotal (c.GrossAmt) t;