Cumulative vs. Tiered Calculation in SQL Query or Inline Table Value Function with Conditional Logic-CodePudding

I'm working with two tables of data, but different methodologies to derive two distinct desired outputs below.

The first is a cumulative tier calculation and the second is a just a tiered lookup based on a range. I need to be able to return this calculation for a row item transaction based on some JOINS using foreign keys to other dimension tables, namely Accounts and Regions and Tier Type. For example, a single account could have a calculation be either cumulative or tiered based on the region's and/or account's ID.

Link to DB Fiddle

Type ID	Name
1	Cumulative
2	Tiered

Tiered table:

Account ID	Type ID	Region ID	TierNo	Min	Max	Total A	Total B
101	1	2	1	0	10000	.90	.10
101	1	2	2	10001	30000	.60	.40
101	1	2	3	30001	100000	.40	.60
101	1	2	4	100001	500000	.40	.60
101	1	2	5	500001	999999999999	.20	.80
102	1	3	1	0	7800	.80	.20
102	1	3	2	7801	12800	.70	.30
102	1	3	3	12801	34000	.60	.40
102	1	3	4	34001	50000	.50	.50
102	1	3	5	5000 1	999999999999	.50	.50
103	2	1	1	0	10000	.90	.10
103	2	1	2	10001	30000	.60	.40
103	2	1	3	30001	100000	.40	.60
103	2	1	4	100001	500000	.40	.60
103	2	1	5	500001	999999999999	.20	.80

Current table sample:

Trans ID	Account ID	Type ID	Region ID	GrossAmt
100001	101	1	2	42650
100002	102	1	3	42650
100003	103	2	1	42650

Desired output:

Trans ID	Account ID	Type ID	Region ID	GrossAmt	Total A %	Total A $	Net Amt
100001	101	1	2	42650	0.611	26059.99	16589.99
100002	102	1	3	42650	0.628	26784.98	15864.99
100003	103	2	1	42650	0.40	17060.00	25590.00

I've been able to make some edits to a previous post to get the accounts, but can't seem to figure out the logic for the tiered lookup value in TransID 100003.

Ideally, I'd prefer to create this logic in a table value function (or two) and then incorporate it into a View that I'll use for reporting in a web form using C#.

SELECT
  c.*,
  [Total A %] = t.Total / c.GrossAmt,
  [Total A $] = t.Total,
  [Net Amt]   = c.GrossAmt - t.Total
FROM #temp c
INNER JOIN Accounts a ON a.[Account ID] = c.[Account ID]
CROSS APPLY (
    SELECT
      Total = SUM((v.ActualMax - t.[Min]) * t.[Total A %])
    FROM [dbo].[Tiered Table] t
    CROSS APPLY (VALUES(
      CASE WHEN c.GrossAmt < t.[Max] THEN c.GrossAmt ELSE t.[Max] END
    )) v(ActualMax)
    WHERE c.GrossAmt > t.[Min] AND t.[Account ID] = c.[Account ID]
) t;

Any ideas or guidance would be extremely helpful and appreciated.

CodePudding user response：

It seems to be a simple matter of AND OR logic. You need to exclude rows which are TypeID = 2 and also have their maximum below the level of GrossAmt.

Then you just conditionally aggregate either the total amount (for tiered rows only, there will be only one row) or just the amount for that tier (for cumulative tiers).

SELECT
  c.*,
  [Total A %] = t.Total / c.GrossAmt,
  [Total A $] = t.Total,
  [Net Amt]   = c.GrossAmt - t.Total
FROM CurrentData c
INNER JOIN Accounts a ON a.[AccountID] = c.[AccountID]
CROSS APPLY (
    SELECT
      Total = SUM(CASE WHEN t.TypeID = 2 THEN v.GrossAmt ELSE (v.ActualMax - t.[Min]) END * t.[Total A])
    FROM [dbo].[Tiers] t
    CROSS APPLY (VALUES(
      CASE WHEN c.GrossAmt < t.[Max] THEN c.GrossAmt ELSE t.[Max] END,
      c.GrossAmt
    )) v(ActualMax, GrossAmt)
    WHERE t.[AccountID] = c.[AccountID]
      AND t.TypeID = c.TypeID
      AND t.RegionID = c.RegionID
      AND c.GrossAmt > t.[Min]
      AND (t.TypeID = 1 OR c.GrossAmt <= t.Max)
) t;

db<>fiddle

The second CROSS APPLY is only necessary because of aggregating outer values. You don't need this if you place it in a function, as shown in your previous question.

Note that you should use half-open intervals here. In other words, either Min or Max should be exclusive. Otherwise there may be values that can "fall through the cracks".