# Thinking outside the box

Patron Saint of Lost Yaks

## August 2010 Blog Posts

##### Married with children

will happen in just 4 days. The child part is taken care of. Multiple times. Now it's time for the married part. Me and my girlfriend Jennie will be married in Bjuv church at 3pm, Saturday the 28th of August 2010. Pictures of Bjuv church can be found here. After the ceromony we'll goto Hovs Hallar for dinner and party. The famous parts (The chess scenes) of the movie The Seventh Seal by Ingmar Bergman was shot here. We'll sleep there since most of Jennie's family comes from Stockholm (300 miles away). So I'll soon wear a ring on my finger. I tried the "rule them...

posted @ Tuesday, August 24, 2010 12:37 PM | Feedback (3) | Filed Under [ Miscellaneous ]

##### Excel XIRR function

First create a table type like this CREATE TYPE dbo.MyXirrTable AS TABLE ( theValue DECIMAL(19, 9) NOT NULL, theDate DATETIME NOT NULL ) GO And then you create a function like this CREATE FUNCTION dbo.XIRR ( @Sample MyXirrTable READONLY, @Rate DECIMAL(19, 9) = 0.1 ) RETURNS DECIMAL(38, 9) AS BEGIN DECLARE @LastRate DECIMAL(19, 9), @RateStep DECIMAL(19, 9) = 0.1, @Residual DECIMAL(19, 9) = 10, @LastResidual DECIMAL(19, 9) = 1, @i TINYINT = 0 IF @Rate IS NULL SET @Rate = 0.1 SET @LastRate = @Rate WHILE @i < 100 AND ABS((@LastResidual - @Residual) / @LastResidual) > 0.00000001 BEGIN SELECT @LastResidual = @Residual, @Residual = 0 SELECT @Residual = @Residual + theValue / POWER(1 + @Rate, theDelta / 365.0E) FROM ( SELECT theValue, DATEDIFF(DAY, MIN(theDate) OVER (), theDate) AS theDelta FROM @Sample ) AS d SET @LastRate = @Rate If @Residual >= 0 SET...

posted @ Thursday, August 19, 2010 9:46 PM | Feedback (1) | Filed Under [ SQL Server 2008 Algorithms ]

##### CHECKSUM weakness explained

The built-in CHECKUM function in SQL Server is built on a series of 4 bit left rotational xor operations. See here in a previous forum post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832 for more explanation. Today, I wanted to see how often a collision (or false positive) can occur. Let's take a very simple CHECKSUM value, for example 123. Decimal 123 is "01111011" in binary representation. Since CHECKSUM function rotates the iterative checksum value 4 bits to the left (same thing as multiplying by 16), how many permutations of two characters returns the same CHECKSUM value of 123? The answer is 16 permutations. Let's investigate by writing down the solution of this...

posted @ Thursday, August 19, 2010 4:15 PM | Feedback (0) | Filed Under [ Algorithms Miscellaneous ]