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 →

## 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 →

## 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 →

## The internal storage of a DATETIME value

SELECT [Now], BinaryFormat, SUBSTRING(BinaryFormat, 1, 4) AS DayPart, SUBSTRING(BinaryFormat, 5, 4) AS TimePart, CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT) AS [Days], DATEADD(DAY, CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT), 0) AS [Today], CAST(SUBSTRING(BinaryFormat, 5, 4) AS INT) AS [Ticks], DATEADD(MILLISECOND, 1000. 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 →

## Simple script to get referenced table and their column names

-- Setup user supplied parameters DECLARE @WantedTable SYSNAME SET@WantedTable = 'Sales.factSalesDetail' -- Wanted table is "parent table" SELECT PARSENAME(@WantedTable, 2) AS ParentSchemaName, PARSENAME(@WantedTable, 1) AS ParentTableName, cp.Name AS ParentColumnName, OBJECT_SCHEMA_NAME(parent_object_id) AS ChildSchemaName, OBJECT_NAME(parent_object_id) AS ChildTableName, cc. Read more →

## Get date from ISO week number

This function is just the opposite of this one, http://weblogs.sqlteam.com/peterl/archive/2009/05/27/Extended-ISO-week-function.aspx. The function in the link returns the ISO week number from a given date, and the function below returns the monday's date from an ISO week. 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 →

## Problem with SQL Server service restart

The few last days, our hosting company have updated their VMware environment a number of times and thus have forced equal number of restarts for our database servers. The problem with this is that one of the databases, Yoda, needed 1. 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 →

## NOT IN not equal to <>

Hi! I am between session of 24 hour of PASS right now, so I just post this piece of code I helped out with on a forum. The original poster was amazed that Col1 <> 'Some value' didn't return same records as Col1 NOT IN ('Some value') See this example code to understand the implications of fiddling with SET ANSI_NULLS option setting. Read more →

## CHAR(0) is not that innocent you may think

Some days ago I posted a solution for a simple problem on a forum about to delete multiple spaces in one statement (not using loop). My suggestion was declare@s varchar(100) set@s = 'xxxx yyyyy zzzzzz' SELECTREPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(2)), CHAR(2) + ' ', ''), CHAR(2), '') I used CHAR(2) because that is not commonly used in normal texts. Read more →

## Complement of ISNUMERIC function

Today I was involved in an interesting topic about how to check if a text string really is integer or not. This is what I finally suggested. CREATEFUNCTION dbo.fnIsINT ( @Data NVARCHAR(11) ) RETURNSINT AS BEGIN RETURN CASE WHEN SUBSTRING(@Data, 2, LEN(@Data)) LIKE '%[^0-9]%' COLLATE LATIN1_GENERAL_BIN THEN NULL WHEN SUBSTRING(@Data, 1, 1) NOT LIKE '[-+0-9]' COLLATE LATIN1_GENERAL_BIN THEN NULL WHEN @Data IN('-', '+') THEN NULL WHEN CAST(@Data AS BIGINT) NOT BETWEEN -2147483648 AND 2147483647 THEN NULL ELSE CAST(@Data AS INT) END END Read more →