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

When I was a six or seven year old, my Mom asked me a question. She asked if I’d rather have $1,000,000 at the end of the month, or a penny on day 1, with the note that each day of the month, she’d double what I’d gotten the first day. Doing quick math in my head, $0.01, $0.02, $0.04, etc, I said a million.

Was I right? Let’s build a recursive CTE.

Recursion is an interesting computer science technique that stumps lots of people. When I was learning programming, it seemed that recursion (in Pascal) and pointers (in C), were the weed out topics.

However, they aren’t that bad, and with CTEs, we can write recursion in T-SQL. I won’t cover where this might be used in this post, though I will give you a simple CTE to view.

There are two parts you need: the anchor and the recursive member. These are connected with a UNION ALL. There can be multiple items, but we’ll keep things simple.

I want to first build an anchor, which is the base for my query. In my case, I want to start with the day of the month, which I’ll represent with a [d]. I also need the amount to be paid that day, which is represented with [v]. I’ll include the $1,000,000 as a scalar at the end. My anchor looks like this:

WITH myWealth ( d, v)

AS (
— anchor, day 1

SELECT

‘d’ = 1

, ‘v’ = CAST( 0.01 AS numeric(38,2))

UNION ALL

Now I need to add in the recursive part. In this part, I’ll query the CTE itself, calling myWealth as part of the code. For my query, I want to increment the day by 1 with each call, so I’ll add one to that value.

SELECT

myWealth.d + 1

For the payment that day, it’s a simple doubling of the previous day. So I can do this a few days: addition or multiplication. I’ll use multiplication since it’s easier to read.

SELECT

myWealth.d + 1

, myWealth.v * 2

My FROM clause is the CTE itself. However I need a way to stop the recursion. In my case, I want to stop after 31 days. So I’ll add that.

UPDATE: The original code (<= 31) went to 32 days. This has been corrected to stop at 31 days.

FROM

myWealth

WHERE

myWealth.d <= 30

Now let’s see it all together, with a little fun at the end for the outer query.

WITH  myWealth ( d, v )

AS (

— anchor, day 1)

SELECT

‘d’ = 1

, ‘v’ = CAST(0.01 AS NUMERIC(38, 2))

UNION ALL

— recursive part, get double the next value, end at one month

SELECT

myWealth.d + 1

, myWealth.v * 2

FROM

myWealth

WHERE

myWealth.d <= 31

)

SELECT

‘day’ = myWealth.d

, ‘payment’ = myWealth.v

, ‘lump sum’ = 1000000

, ‘decision’ = CASE WHEN myWealth.v < 1000000 THEN ‘Good Decision’

ELSE ‘Bad decision’

END

FROM

myWealth;

When I run this, I get some results:

Did I make a good choice? Let’s look for the last few days of the month.

That $1,000,000 isn’t looking too good. If I added a running total, it would be worse.

SQLNewBlogger

If you want to try this yourself, add the running total and explain how it works.

Filed under: Blog Tagged: SQLNewBlogger, syndicated, T-SQL