Peter Larsson Blog

Patron Saint of Lost Yaks

Greatest Common Divisor function

This approach is not dependent on the 32-level recursion as most other algorithms are for this problem. CREATEFUNCTION dbo.fnGCD ( @a INT, @b INT ) RETURNSINT AS BEGIN DECLARE @c INT IF @a IS NULL OR @b IS NULL OR (@a = 0 AND @b = 0) RETURN NULL IF @a = 0 OR @b = 0 RETURN ABS(@a) + ABS(@b) IF ABS(@a) < ABS(@b) SELECT @c = ABS(@a), @a = ABS(@b), @b = @c ELSE SELECT @a = ABS(@a), @b = ABS(@b) SET @c = @a % @b WHILE @c > 0 SELECT @a = @b, @b = @c, @c = @a % @b RETURN @b END Legacy Comments RBarryYoung 2009-04-15 re: Greatest Common Divisor function That's the iterative version of the chinese remainder algorithim, isn't it Peso? Read more →

Composable DML

With the arrival of SQL Server 2005 the new OUTPUT operator was introduced. The OUTPUT operator works much like a "local trigger" on the current statement. The drawback is that there is no way to filter the returned resultset directly. Read more →

Excel DAYS360 clone

CREATEFUNCTION dbo.DATEDIFF360 ( @source DATETIME, @target DATETIME, @style BIT = 0 ) RETURNSINT AS BEGIN RETURNCASE @style -- European style WHEN 1 THENCASE WHEN DATEPART(DAY, @target) = 31 THEN 30 ELSE DATEPART(DAY, @target) END - CASE WHEN DATEPART(DAY, @source) = 31 THEN 30 ELSE DATEPART(DAY, @source) END -- US style ELSECASE WHEN DATEPART(DAY, @target) = 31 AND DATEPART(DAYOFYEAR, @source) = 60 AND DATEPART(MONTH, @source) = 2 THEN 30 WHEN DATEPART(DAY, @target) = 31 AND DATEPART(DAY, @source) < 30 THEN 31 WHEN DATEPART(DAY, @target) = 31 THEN 30 ELSE DATEPART(DAY, @target) END - CASE WHEN @source = DATEADD(MONTH, DATEDIFF(MONTH, -1, @source), -1) THEN 30 ELSE DATEPART(DAY, @source) END END + 30 * DATEDIFF(MONTH, @source, @target) END Read more →

Excel ERF clone for two variables

I previously posted a solution for one variable here http://weblogs.sqlteam.com/peterl/archive/2009/03/11/Excel-ERF-clone.aspx and here is a solution for using two variables. CREATEFUNCTION dbo.fnErf ( @z1 FLOAT, @z2 FLOAT, @MaxIterations TINYINT = 10 ) RETURNSFLOAT AS BEGIN IF @z1 IS NULL OR @z2 IS NULL RETURN NULL DECLARE @n TINYINT, @s1 FLOAT, @s2 FLOAT, @p1 FLOAT, @p2 FLOAT, @a1 FLOAT, @a2 FLOAT SELECT @n = 1, @p1 = 1, @p2 = 1, @a1 = @z1, @a2 = @z2, @MaxIterations = COALESCE(ABS(@MaxIterations), 10) WHILE @p1 <> 0. Read more →

Alternative approach to calculate most used time interval

In the past I have given the advice to break down all date intervals into the smallest part, most often minutes. Then OP should group by the minute. -- Prepare sample data DECLARE@Data TABLE ( RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, CreateDate DATETIME, DeleteDate DATETIME ) -- Populate sample data INSERT@Data ( CreateDate, DeleteDate ) SELECT'2009-01-14 22:33', '2009-01-14 22:35' UNION ALL SELECT'2009-01-14 22:33', '2009-01-14 22:33' UNION ALL SELECT'2009-01-14 22:34', '2009-01-14 22:35' UNION ALL SELECT'2009-01-14 22:35', '2009-01-14 22:35' UNION ALL SELECT'2009-01-14 22:35', '2009-01-14 22:36' UNION ALL SELECT'2009-01-14 22:37', '2009-01-14 22:37' UNION ALL SELECT'2009-01-14 22:39', '2009-01-14 22:39' UNION ALL SELECT'2009-01-14 22:38', '2009-01-14 22:38' UNION ALL SELECT'2009-01-14 22:39', '2009-01-14 22:39' UNION ALL SELECT'2009-01-14 22:41', '2009-01-14 22:41' UNION ALL SELECT'2009-01-14 22:43', '2009-01-14 22:44' UNION ALL SELECT'2009-01-14 22:52', '2009-01-14 22:52' UNION ALL SELECT'2009-01-14 22:53', '2009-01-14 22:53' UNION ALL SELECT'2009-01-14 22:53', '2009-01-14 22:53' UNION ALL SELECT'2009-01-14 22:56', '2009-01-14 22:57' UNION ALL SELECT'2009-01-14 22:57', '2009-01-14 22:57' UNION ALL SELECT'2009-01-14 22:58', '2009-01-14 22:58' UNION ALL SELECT'2009-01-14 22:58', '2009-01-14 22:59' UNION ALL SELECT'2009-01-14 22:59', '2009-01-14 22:59' DECLARE@From INT, @To INT SELECT@From = MIN(DATEDIFF(MINUTE, 0, CreateDate)), @To = MAX(DATEDIFF(MINUTE, 0, DeleteDate)) FROM@Data SELECTw. Read more →

Create another nested XML hierarchy

-- Prepare sample data DECLARE@Sample TABLE ( GalaxyID INT, ObjectID VARCHAR(16), ObjectType VARCHAR(5), ObjectTitle VARCHAR(200) ) INSERT@Sample SELECT1, 'T022520001611242', 'Topic', 'Business Strategy' UNION ALL SELECT1, 'T021320001145243', 'Topic', 'Decision Making' UNION ALL SELECT1, 'T8150310322032', 'Topic', 'New Growth' UNION ALL SELECT1, 'T97200019493829', 'Topic', 'Marketing Strategy' UNION ALL SELECT2, 'T1210018575047', 'Topic', 'Strategic Relationships' UNION ALL SELECT2, 'T1027001655860', 'Topic', 'Globalization' UNION ALL SELECT2, 'T95200015582307', 'Topic', 'Strategic Sourcing' UNION ALL SELECT2, 'T021120001714561', 'Topic', 'Business Processes & Architectures' UNION ALL SELECT3, 'T011820001527219', 'Topic', 'Business Models' UNION ALL SELECT3, 'T022520001622334', 'Topic', 'Venture Capital Processes' UNION ALL SELECT3, 'T524200010114538', 'Topic', 'Entrepreneurial Thinking' UNION ALL SELECT3, 'T011820001524538', 'Topic', 'Drivers of Change & Growth' -- Display the XML SELECTs. Read more →

Easy script for calculating weekday or weekend for a date

SELECT d.theDate, DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekend, 1 - DATEDIFF(DAY, '17530101', d.theDate) % 7 / 5 AS IsWeekday FROM ( SELECT CAST('20081124' AS SMALLDATETIME) AS theDate UNION ALL SELECT '20081125' UNION ALL SELECT '20081126' UNION ALL SELECT '20081127' UNION ALL SELECT '20081128' UNION ALL SELECT '20081129' UNION ALL SELECT '20081130' )AS d Legacy Comments Uri Dimant 2009-01-14 re: Easy script for calculating weekday or weekend for a date Hi Peter. Read more →