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 →
Today I had the opportunity to debug a system with a client. I have to confess it took a while to figure out the bug, but here it is SELECTCOUNT(*) OfflineData
Read more →
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 →
For the next few months, I will be involved in an interesting project for a mission critical application that our company have outsourced to a well-known and respected consulting company here Sweden.
Read more →
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 →
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 →
This bug has haunted me for a while, until today when I decided to not accept it anymore. So I filed a bug over at connect.microsoft.com, https://connect.microsoft.com/SQLServer/feedback/details/636074/some-datatypes-doesnt-honor-localization, and if you feel the way I do, please vote for this bug to be fixed.
Read more →
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 →
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 →
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 →
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 →
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 →
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 →
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 →
I got an email from Mr Celko and he correctly stated that my previous solution was not truly working with sets, so he posted me some solutions and proper sample data.
Read more →
I came across an interesting post on Microsoft SQL Server forum this afternoon. It was a question about Relational algebra and the poster wanted to have an efficient query to solve his problem.
Read more →
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 →
SELECTu.name, l.dbname FROMsys.sysusers AS u INNERJOIN sys.syslogins AS l ON l.sid = u.sid
Read more →
-- 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 →
Here is a generic algorithm to get the Excel column name according to it's position. By changing the @Base parameter, you can do this for any sequence according to same style as Excel.
Read more →