# Simplifying Function with Recursive CTE and/or Window Function

I'm trying to come up with a Recursive CTE and/or Window Function to create a function.

After days, I've boiled the function down to (pseudocode) where I have `N` and `B`, and need to generate `E`:

En = Bn * (1 - SUM(E1, E2, ... En-1))

### Examples:

``````╔═══╦═════════════╦═════════════╗
║ N ║ B           ║ E           ║
╠═══╬═════════════╬═════════════╣
║ 0 ║ 0.142857143 ║ 0.142857143 ║
║ 1 ║ 0.285714286 ║ 0.244897959 ║
║ 2 ║ 0.285714286 ║ 0.174927114 ║
║ 3 ║ 0.285714286 ║ 0.124947938 ║
║ 4 ║ 0.285714286 ║ 0.089248527 ║
║ 5 ║ 0.4         ║ 0.089248527 ║
║ 6 ║ 0.666666667 ║ 0.089248527 ║
║ 7 ║ 1           ║ 0.044624264 ║
╚═══╩═════════════╩═════════════╝
``````

E0 = 0.143 * (1 - 0) = 0.143
E1 = 0.286 * (1 - 0.143) = 0.245
E2 = 0.286 * (1 - (0.143 + 0.245)) = 0.175
E3 = 0.286 * (1 - (0.143 + 0.245 + 0.175)) = 0.125
E4 = 0.286 * (1 - (0.143 + 0.245 + 0.175 + 0.125)) = 0.089
E5 = 0.400 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089)) = 0.089
E6 = 0.667 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089)) = 0.089
E7 = 1.000 * (1 - (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089 + 0.089)) = 0.044

If the table above was in Excel, `C2 = B2 * (1 - 0)` (base) and `C3 = B3 * (1 - SUM(C\$2:C2))` (recursive)

## What I've tried:

### Windowed Functions

Tried `SUM(...) OVER(ORDER BY [N] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)`, but can't reference the column recursively.

### Recursive CTE

Tried several iterations of:

``````WITH B AS ([Num], [Best], [Effective Rate]) AS (
SELECT *
, [Best]
FROM A
WHERE [Num] = 0
UNION ALL
SELECT A.*
, (1 - [Effective Rate]) * A.[Best]
FROM B
JOIN A ON A.[Num] = B.[Num] + 1
)
``````

and some with an extra column in the CTE, but it only covers 1 previous row and results after 2nd row are wrong.

### Recursive CTE with Windowed Function

From all that I've tried, it seems that the recursive segment of the CTE is calculated independently of the other results, and `SUM(...) OVER(...)` only works on the current row. (With regard to the above table, all values of `E` would be `0.142857143`).

I assume this is because the `UNION ALL` happens all at once, and not incrementally.

## Alternative Solutions

What I would really like to happen is to simplify the above equation, and/or transform it into an iterative function.

Bonus: If anyone cares to know the source of this information, it's used to calculate MACRS depreciation for tax purposes.

• Why do you think you need a recursive query? Just a subselect with the window function should do it. Jun 7, 2020 at 22:56
• @mustaccio Because the value of `E` depends on the previous values of `E`. I'll elaborate with concrete numbers. Jun 7, 2020 at 22:58
• Also, your expected results don't make sense; why E doesn't change for ID 4 to 6? Jun 7, 2020 at 23:01
• @mustaccio, That's magic of these numbers! I love math, and am a professional full-stack engineer, so I'm trying to derive these "arbitrary" numbers these tax people use. :) Jun 7, 2020 at 23:15
• @mustaccio - the numbers for E do change slightly but when rounded to 9 decimal places they appear the same Jun 7, 2020 at 23:49

You need an extra column to carry along the running total (fiddle).

In the recursive part of the CTE below `R` refers to the "previous" row and `A` the current one so referencing the column from `R` is your `SUM(E1, E2, ... En-1)`.

``````WITH R
AS (SELECT N,
B,
E = B,
RunningTotalE = B
FROM   A
WHERE  N = 0
UNION ALL
SELECT A.N,
A.B,
E = A.B * ( 1 - R.RunningTotalE ),
RunningTotalE = A.B * ( 1 - R.RunningTotalE ) + R.RunningTotalE
FROM   R
JOIN A
ON A.N = R.N + 1)
SELECT N,
B,
E = CAST(E AS DECIMAL(10,9))
FROM   R
``````
• I did play around with a `[Running Total]` column, but I wasn't good enough :). This worked out great when plugging this in, and works! Thank you! Jun 7, 2020 at 23:35