# Peter Larsson Blog

Patron Saint of Lost Yaks

## The one feature that would make me invest in SSIS 2012

This week I was invited my Microsoft to give two presentations in Slovenia. My presentations went well and I had good energy and the audience was interacting with me. When I had some time over from networking and partying, I attended a few other presentations. Read more →

## How to calculate the covariance in T-SQL

DECLARE @Sample TABLE ( x INT NOT NULL, y INT NOT NULL ) INSERT@Sample VALUES (3, 9), (2, 7), (4, 12), (5, 15), (6, 17) ;WITHcteSource(x, xAvg, y, yAvg, n) AS ( SELECT 1E * x, AVG(1E * x) OVER (PARTITION BY (SELECT NULL)), 1E * y, AVG(1E * y) OVER (PARTITION BY (SELECT NULL)), COUNT(*) OVER (PARTITION BY (SELECT NULL)) FROM @Sample ) SELECTSUM((x - xAvg) *(y - yAvg)) / MAX(n) AS [COVAR(x,y)] FROMcteSource Legacy Comments dmSQL 2012-10-24 re: How to calculate the covariance in T-SQL Thanks. Read more →

## Convert UTF-8 string to ANSI

CREATEFUNCTION dbo.fnConvertUtf8Ansi ( @Source VARCHAR(MAX) ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE@Value SMALLINT = 160, @Utf8 CHAR(2), @Ansi CHAR(1) IF @Source NOT LIKE '%[ÂÃ]%' RETURN@Source WHILE @Value <= 255 BEGIN SELECT@Utf8 =CASE WHEN @Value BETWEEN 160 AND 191 THEN CHAR(194) + CHAR(@Value) WHEN @Value BETWEEN 192 AND 255 THEN CHAR(195) + CHAR(@Value - 64) ELSE NULL END, @Ansi = CHAR(@Value) WHILE CHARINDEX(@Source, @Utf8) > 0 SET@Source = REPLACE(@Source, @Utf8, @Ansi) SET@Value += 1 END RETURN@Source END Read more →

## Simple function to get beginning or end of month

CREATEFUNCTION dbo.fnIsOnMonthEdge ( @theDate DATETIME ) RETURNSSMALLINT AS BEGIN RETURN CASE @theDate WHEN '99991231' THEN 1 ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, 1, @theDate)) END + CASE @theDate WHEN '17530101' THEN -1 ELSE DATEDIFF(MONTH, @theDate, DATEADD(DAY, -1, @theDate)) END END   Read more →

## Unified Relational Division algorithm

Finally! Today I finished my presentation about finding a unified algorithm for Relational Division, which should work for all types of division; singlerecord and multirecord, singlecolumn and multicolumn and both exact division and with remainder. Read more →

## Sweet and simple "first and last" weekday calculation

DECLARE @Year SMALLINT = 2011, @NumberOfYears TINYINT = 3 ;WITHcteCalendar(FirstOfMonth, LastOfMonth) AS ( SELECT DATEADD(MONTH, 12 * @Year + Number - 22801, 6) AS FirstOfMonth, DATEADD(MONTH, 12 * @Year + Number - 22800, -1) AS LastOfMonth FROM master. Read more →

## Fast easter day function

CREATEFUNCTION dbo.fnGetEasterDate ( @Year SMALLINT ) RETURNSDATE AS BEGIN RETURN ( SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CONVERT(DATETIME, CAST(@Year AS CHAR(4)) + BaseDate, 112)) / 7 * 7, 6) FROM ( SELECT CASE @Year % 19 WHEN 0 THEN '0415' WHEN 1 THEN '0404' WHEN 2 THEN '0324' WHEN 3 THEN '0412' WHEN 4 THEN '0401' WHEN 5 THEN '0419' WHEN 6 THEN '0409' WHEN 7 THEN '0329' WHEN 8 THEN '0417' WHEN 9 THEN '0406' WHEN 10 THEN '0326' WHEN 11 THEN '0414' WHEN 12 THEN '0403' WHEN 13 THEN '0323' WHEN 14 THEN '0411' WHEN 15 THEN '0331' WHEN 16 THEN '0418' WHEN 17 THEN '0408' WHEN 18 THEN '0328' ELSE NULL END WHERE @Year BETWEEN 1900 AND 9999 )AS d(BaseDate) ) END Legacy Comments iStan 2011-01-17 re: Fast easter day function Hi, Read more →

## Joe Celko's Puzzles and Answers - The Restaurant seat assignment Problem

This problem is designed to come up with a solution that uses the smallest amount of storage possible for a 1,000 seat restaurant. I've come up with a solution that need only 125 bytes of storage. Read more →

## String concatenation and entitization

This question has been asked over and over again, and instead of having to redirect to any of my previous answers, I will cover the solution here in my blog. Consider this sample data DECLARE@Sample TABLE Read more →

## Another bin-packaging algorithm using recursion and XML

This time I will show you an algorithm to do the dreaded bin-packaging using recursion and XML. First, create some sample data like this -- Prepare sample data DECLARE@Sample TABLE ( RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Expense SMALLMONEY NOT NULL ) -- Populate sample data INSERT@Sample ( Expense ) VALUES (12. Read more →

## SQL Server 2008 Compression

Hi! Today I am going to talk about compression in SQL Server 2008. The data warehouse I currently design and develop holds historical data back to 1973. The data warehouse will have an other blog post laster due to it's complexity. Read more →

## Celko Stumper - The Class Scheduling Problem

Joe Celko has posted a new Stumper - The Class Scheduling Problem here http://www.simple-talk.com/sql/t-sql-programming/celkos-sql-stumper-the-class-scheduling-problem/ Here is one suggestion to solve the problem. It's linear in time so it should be very fast. Read more →

## Collapse date range, with safety date range

This example is based on AdventureWorks database. More details about the task is found here http://sqlblog.com/blogs/adam_machanic/archive/2009/12/08/t-sql-tuesday-001-exploring-fuzzy-interval-islands-using-sqlclr.aspx. The task is to produce a set of dateranges where a product has been sold, with a "latency" of 7 days. Read more →

## Get the Nth weekday of any arbitrary period

I have previously posted some algorithm how to find the Nth weekday for a fixed period such as month, quarter or year. See http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx http://weblogs.sqlteam.com/peterl/archive/2009/06/18/How-to-get-the-Weekday-and-Nth-from-a-date.aspx http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx This algorithm in the function allows you to get a weekday for any arbitrary period, both forward and backwards. Read more →

## Superfast sequence generators!

Based on Itzik's suggestion for sequence generators, I rewrote them and adapted them for my needs. Itzik showed how to work around a big issue for some cases where the query optimizer actually tried to produce all possible combinations before returning the wanted records. Read more →

## New article series going on by Itzik Ben-Gan

I recommend you read it. Next part will contain a brilliant solution for calculating concurrent sessions in a linear algorithm. The math involved is very good indeed. First part is found here http://www. Read more →