# 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.