## Another Recursive CTE–Doing Math

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I showed how to write a simple recursive query recently that calculated an amount of money paid out each day. Now I want to extend this a bit to include a few math formulas.

Note: These aren’t terribly useful, but they are good practice for just writing recursive queries.

## Fibonacci Series

I’m sure many of you have dealt with a Fibonacci series at some point in your life. This is a series where the current value is the sum of the two previous values. In other words,

term 3 = term 1 + term 2

term 4 = term 2 + term 3

etc.

The series starts with 0, 1 and goes from there. Can we do this in SQL? Sure.

Let’s start by looking at what we need. We need some counter, a current term, and a previous term. That’s 3 columns in our query. We start by building an anchor, which has the first two terms. The counter is n and the two terms are i and j.

— Anchor

select n = 1

, i = 0

, j = 1

Now we add the recursive part. In this case, the counter increases by 1. I only include the counter so I know when to stop. SQL Server has a finite size of various values, and we can exceed that without a way to stop.

The first value will be calculated from the current value + the next call. The current value will become the second one on the next last call, so we move that over. This gives us.

select counter + 1

, first = first + second

, second = first

With this, we can then add a WHERE clause to stop. I’ll stop at the first ten terms. Here’s the CTE.

WITH myFib (n, i, j)
AS
(
— anchor
SELECT ‘n’ = 1
, ‘i’ = 0
, ‘j’ = 1
UNION ALL
— recursive section
SELECT n + 1
, i + j
, i
FROM myFib
WHERE myFib.n < 10
)
SELECT
‘Level’ = myFib.n
, ‘Fibonacci’ = i FROM myFib

If we run this, we see:

We can extend this by altering the WHERE clause.

A Little Calculus

What about math functions? Have any of you worked with a series in calculus? If so, you might remember something like this:

This is a repetitive calculation, and should either converge or diverge. Can we implement this as a recursive CTE? Sure.

This one is really simple. I use the POWER() function in my recursive member to raise –1 to whatever counter I’m using for n. I then use a SUM() across all previous values in the outer query to get the sum.

WITH myPartialSum (n, s)
AS
(
SELECT ‘n’ = 1
, ‘s’ = POWER( -1, 0)
UNION ALL
SELECT n + 1
, POWER(-1, n)
FROM myPartialSum
WHERE n < 100
)
SELECT myPartialSum.n
,myPartialSum.s
, ‘partialsum’ = SUM(s) OVER (ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM myPartialSum

Note: This series does not converge, as it alternates to infinity.

Neither of these is terribly useful, but they do allow some practice in writing CTEs that will recurse.

## SQLNewBlogger

Implement some other series or sequence yourself and explain how it works.