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`

:

E_{n}=B_{n}* (1 – SUM(E_{1},E_{2}, …E_{n-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 ║ ╚═══╩═════════════╩═════════════╝ `

E

_{0}= 0.143 * (1 – 0) = 0.143

E_{1}= 0.286 * (1 – 0.143) = 0.245

E_{2}= 0.286 * (1 – (0.143 + 0.245)) = 0.175

E_{3}= 0.286 * (1 – (0.143 + 0.245 + 0.175)) = 0.125

E_{4}= 0.286 * (1 – (0.143 + 0.245 + 0.175 + 0.125)) = 0.089

E_{5}= 0.400 * (1 – (0.143 + 0.245 + 0.175 + 0.125 + 0.089)) = 0.089

E_{6}= 0.667 * (1 – (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089)) = 0.089

E_{7}= 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.