Peter Larsson Blog

Patron Saint of Lost Yaks

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 →

The internal storage of a SMALLDATETIME value

SELECT[Now], BinaryFormat, SUBSTRING(BinaryFormat, 1, 2) AS DayPart, SUBSTRING(BinaryFormat, 3, 2) AS TimePart, CAST(SUBSTRING(BinaryFormat, 1, 2) AS INT) AS [Days], DATEADD(DAY, CAST(SUBSTRING(BinaryFormat, 1, 2) AS INT), 0) AS [Today], SUBSTRING(BinaryFormat, 3, 2) AS [Ticks], DATEADD(MINUTE, CAST(SUBSTRING(BinaryFormat, 3, 2) AS SMALLINT), 0) AS Peso FROM ( SELECT CAST(GETDATE() AS SMALLDATETIME) AS [Now], CAST(CAST(GETDATE() AS SMALLDATETIME) AS BINARY(4)) AS BinaryFormat )AS d   Legacy Comments Rob Volk 2010-12-15 re: The internal storage of a SMALLDATETIME value Have you done this for the datetime2 types? 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 →

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. Read more →

More on XML and encoding

Today, let's examine encoding with SQL Server and XML datatype. DECLARE@Inf XML SET@Inf = '<?xml version="1.0" encoding="utf-16"?> <root> <names> <name>test</name> </names> <names> <name>test1</name> </names> </root> ' SELECTx. Read more →