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 →
Today I am going to show you one way to calculate a decimal year. This is not a easy as it sounds because some years (leap years) include a leap day so the number of days in a year is not consistent.
Read more →
In same cases, truncation of a long string is necessary. In most cases you just need to truncate it at the spot and have every section be exactly the same length, like this: DECLARE@String VARCHAR(MAX), @Size TINYINT SELECT@String = 'Hello my name is Jeff.
Read more →
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 →
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 →
This Excel ERF clone works for with SQL Server 2000 and later. I have found that 10 iterations will give enough acccuracy (maximum float accuracy) in most cases, so you can call the function with SELECTdbo.
Read more →
Today I am going to talk about how to efficiently reuse identity values in a column, even if this is something that normally not should be bothered. The reason for this solution was a request for help from a member here on SQLTeam, who was near run out of identity values.
Read more →
Today I am going to write about how to extract the XML structure from a file. The basic idea is to bulk read the file from disk, place the content in an XML variable and traverse elements in the variable and ultimately output a resultset showing the structure of xml file.
Read more →
CREATEFUNCTION dbo.fnIsLeapYear ( @Year SMALLINT ) RETURNSBIT AS BEGIN RETURN CASE DATEPART(DAY, DATEADD(YEAR, @Year - 1904, '19040229')) WHEN29 THEN 1 ELSE 0 END END
Legacy Comments
Brian Tkatch
2009-02-25
re: Fastest LeapYear checker?
Read more →
Some time ago, I wrote this article about how DATEDIFF works. http://www.sqlteam.com/article/datediff-function-demystified At the end I suggested two functions to calculate the number of months according to how human mind works.
Read more →
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 →
-- 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 →
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 →
CREATEFUNCTION dbo.fnResolveFractionals ( @data VARCHAR(20) ) RETURNSFLOAT AS BEGIN RETURN CASE WHEN LEN(@data) - LEN(REPLACE(@data, ' ', '')) = 1 AND LEN(@data) - LEN(REPLACE(@data, '/', '')) = 1 THEN CAST(LEFT(@data, CHARINDEX(' ', @data) - 1) AS FLOAT) + 1.
Read more →
DECLARE@Sample TABLE ( StudentID INT, Class VARCHAR(20) ) INSERT@Sample SELECT1, 'Maths' UNION ALL SELECT1, 'English' UNION ALL SELECT1, 'Science' UNION ALL SELECT2, 'Maths' UNION ALL SELECT2, 'English' UNION ALL SELECT2, 'Science' UNION ALL SELECT2, 'History' UNION ALL SELECT3, 'English' UNION ALL SELECT3, 'Maths' UNION ALL SELECT3, 'Science' UNION ALL SELECT3, 'RE' UNION ALL SELECT4, 'Science' UNION ALL SELECT4, 'Maths' UNION ALL SELECT4, 'English' UNION ALL SELECT4, 'History' UNION ALL SELECT4, 'French' ;WITHYak(ClassName, ClassPath, Combinations) AS ( SELECT Class, CAST(Class AS VARCHAR(MAX)), CAST(1 AS INT) FROM @Sample GROUP BY Class UNION ALL SELECT s.
Read more →
Lately I have seen a number of questions regarding incremental update of some sort of counter placed in a central table. The original posters will for some reason not use an IDENTITY column which has a minimum overhead and use of system resources.
Read more →
Today I come across an interesting approach to a networking algorithm. The question was about how to use recursive to expand a network and still avoid circular reference. See topic here http://www.
Read more →
If you use sp_start_job you have noticed that the code will continue ro run and the job you started is run asynchrously. What if you want to wait for the job to finished?
Read more →
Yesterday I came across this question on another forum. I am trying to come up with a way to identify the half hour impact from several exceptions across multiple days.
Read more →
A good article about finding streaks in your data is this article by MVP Jeff Smith http://www.sqlteam.com/article/detecting-runs-or-streaks-in-your-data. Also see http://weblogs.sqlteam.com/peterl/archive/2008/05/13/Lightning-fast-collapsed-date-ranges-and-missing-date-ranges.aspx
And this is an alternative way to find your streaks
Read more →