A Basic Recursive CTE and a Money Lesson

, 2016-05-25

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:

2016-05-17 18_48_04-Start

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

2016-05-17 18_48_16-Start

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

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads