CREATEFUNCTION dbo.fnCalculateFinSSN ( @SSN CHAR(10) ) RETURNSCHAR(11) AS BEGIN IF LOWER(@SSN) NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][-+a][0-9][0-9][0-9]' RETURN NULL IF SUBSTRING(@SSN, 7, 1) IN('-', '+') AND ISDATE('19' + SUBSTRING(@SSN, 1, 6)) = 0 RETURN NULL IF LOWER(SUBSTRING(@SSN, 7, 1)) = 'a' AND ISDATE('20' + SUBSTRING(@SSN, 1, 6)) = 0 RETURN 0 DECLARE @Digits INT SET @Digits = CAST(SUBSTRING(@SSN, 1, 6) + SUBSTRING(@SSN, 8, 3) AS INT) % 31 RETURN @@SSN + SUBSTRING('0123456789abcdefghijklmnopqrstuvwxy', @Digits + 1, 1) END
Read more →
CREATEFUNCTION dbo.fnCheckFinSSN ( @SSN CHAR(11) ) RETURNSBIT AS BEGIN IF LOWER(@SSN) NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][-+a][0-9][0-9][0-9][0-9a-y]' RETURN 0 IF SUBSTRING(@SSN, 7, 1) IN('-', '+') AND ISDATE('19' + SUBSTRING(@SSN, 1, 6)) = 0 RETURN 0 IF LOWER(SUBSTRING(@SSN, 7, 1)) = 'a' AND ISDATE('20' + SUBSTRING(@SSN, 1, 6)) = 0 RETURN 0 DECLARE @Digits INT SET @Digits = CAST(SUBSTRING(@SSN, 1, 6) + SUBSTRING(@SSN, 8, 3) AS INT) % 31 IF SUBSTRING('0123456789abcdefghijklmnopqrstuvwxy', @Digits + 1, 1) <> LOWER(RIGHT(@SSN, 1)) SET@Digits = -1 RETURN @Digits + 1 END
Read more →
Wow! This seems to be blog post number 100. Today I stumbled across an interesting problem where OP had a set of records. Every record were "typed" and "grouped". Now OP wanted to get distinct group id's for those groups having at least one record typed 33, but not having a record typed 11.
Read more →
CREATEFUNCTION dbo.fnISOWEEK ( @theDate DATETIME ) RETURNSTINYINT AS BEGIN RETURN ( SELECT CASE WHEN @theDate >= '99990104' THEN (DATEPART(DAYOFYEAR, @theDate) - 4) / 7 WHEN @theDate >= '99990101' THEN 52 WHEN NextYear <= @theDate THEN 0 WHEN CurrentYear <= @theDate THEN DATEDIFF(DAY, CurrentYear, @theDate) / 7 ELSE DATEDIFF(DAY, PreviousYear, @theDate) / 7 END + 1 FROM ( SELECT DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, -1, Jan4)) / 7) * 7, '17530101') AS PreviousYear, DATEADD(DAY,(DATEDIFF(DAY, '17530101', Jan4) / 7) * 7, '17530101') AS CurrentYear, DATEADD(DAY,(DATEDIFF(DAY, '17530101', DATEADD(YEAR, 1, Jan4)) / 7) * 7, '17530101') AS NextYear FROM ( SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, @theDate), 3) AS Jan4 )AS x ) AS d ) END
Read more →
I was browsing some of my older solutions, and I stumbled across this interesting piece. The object was to mark any employees with Duty 'B' for those employees having both Duty 'O' and Duty 'D'.
Read more →
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 showing you the difference between @@ERROR, BEGINTRY/CATCH and XACT_ABORT. The three alternatives to error handling works little different and let us intercept the error handling in different stages in the process.
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 →
The scenario is very simple. You want to get all ID’s from one table that does not exists in another table. This is how the two simple tables are set up.
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 →
DECLARE@Stats TABLE ( SomeDate DATETIME )
INSERT @Stats SELECT 20000 + ABS(CHECKSUM(NEWID())) % 30000 FROM master..spt_values
DECLARE@Style INT
SET @Style = 100
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 →
SELECT@@SERVERNAME AS SqlServerInstance, db.name AS DatabaseName, SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE f.size / 128.0E END) AS DatabaseSize, SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize, SUM(af.
Read more →
SELECTdb.name AS DatabaseName, bf.logical_name AS LogicalName, CASE bs.[type] WHEN 'D' THEN 'Database' WHEN 'I' THEN 'Differential database' WHEN 'L' THEN 'Log' WHEN 'F' THEN 'File or filegroup' WHEN 'G' THEN 'Differential file' WHEN 'P' THEN 'Partial' WHEN 'Q' THEN 'Differential partial' ELSE 'Unknown' END AS BackupType, CASE bf.
Read more →