1

How would I convert the following CTE into a recursive subquery? It's an implementation of Newtons Method.

Reasons:

1) I have no permissions to create functions or stored procs in the DB

2) I must do everything in TSQL

3) Not using Oracle

TESTDATA Table

PMT         t      V
6918.26     6    410000
3636.51     14   460000
3077.98     22   630000
1645.14     18   340000
8591.67     13   850000

Desired Output

PMT          t    V         Newton
6918.26      6   410000   0.066340421
3636.51      14  460000   0.042449138
3077.98      22  630000   0.024132674
1645.14      18  340000   0.004921588
8591.67      13  850000   0.075982984

_

DECLARE @PMT AS FLOAT
DECLARE @t   AS FLOAT
DECLARE @V   AS FLOAT

--These will be only for 1 example.
SET @PMT = 6918.26740930922 
SET @t = 6
SET @V = 410000

;With Newton (n, i,Fi,dFi) AS (

    --base
    SELECT 
    1,
    CAST(0.1 AS FLOAT)
    ,@PMT * (1 - POWER((1 + CAST(0.1 AS FLOAT) / 12), (-@t * 12))) - @V * CAST(0.1 AS FLOAT) / 12
    ,@PMT * @t * 12 * POWER((1 + CAST(0.1 AS FLOAT) / 12), (-@t * 12 - 1)) - @V

    UNION ALL

    --recursion 
    SELECT 
    n + 1
    ,i - Fi/dFi
    ,@PMT * (1 - POWER((1 + i / 12), (-@t * 12))) - @V * i / 12
    ,@PMT * @t * 12 * POWER((1 + i / 12), (-@t * 12 - 1)) - @V

    FROM Newton WHERE n < 500)

--to get the desired value for params above
SELECT [x].i 
         FROM (
            SELECT n, i, Fi, dFi
            FROM Newton
            WHERE n = 500
            ) [x]
OPTION (MAXRECURSION 500)

_

I want Newton to evaluate on Every record of TestData as a stand alone column.

Any thoughts?

SQALEX101
  • 209
  • 1
  • 3
  • 16
  • Why do you need to convert at all? What problem is such a conversion solving here? – DigiFriend May 03 '17 at 20:51
  • If the CTE was in subquery form, then I can use it in the TestData Select clause to calculate on every row. I can then pass the columns into the subquery to evaluate Newton as opposed to being limited to 1 local variable... – SQALEX101 May 03 '17 at 21:53

0 Answers0