9

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. – mustaccio Jun 7 at 22:56
  • 1
    @mustaccio Because the value of E depends on the previous values of E. I'll elaborate with concrete numbers. – Sunny Patel Jun 7 at 22:58
  • Also, your expected results don't make sense; why E doesn't change for ID 4 to 6? – mustaccio Jun 7 at 23:01
  • 1
    @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. :) – Sunny Patel Jun 7 at 23:15
  • @mustaccio - the numbers for E do change slightly but when rounded to 9 decimal places they appear the same – Martin Smith Jun 7 at 23:49
13

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 
| improve this answer | |
  • 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! – Sunny Patel Jun 7 at 23:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.