Patron Saint of Lost Yaks

## Fast and Simple Prime Number Factorization

This algorithm requires an existing Prime numbers table. You can easily create one of your own or importing the primes ranging from 2 to 3,037,000,493 from the Internet. If you only is interested in primes with INT range {2. Read more →

## How to get a batch of identity values without OUTPUT

Late this evening I stumbled across a post where OP wanted to get all IDENTITY values for the latest INSERT statement, and correctly OP stated that SCOPE_IDENTITY() only returns last IDENTITY value of the batch. Read more →

## EXISTS is my new favorite tool

Since this blog post http://weblogs.sqlteam.com/peterl/archive/2007/09/24/Finding-group-of-records-with-a-certain-status.aspx I have kept the technique in similar situations and perhaps never bothered to rethink my prerogative. Well, today I have. Recently I blogged here http://weblogs. Read more →

## How to calculate the number of weekdays for any given period

Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-in-a-month.aspx is a specialized version for a month, and here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-number-of-weekdays-in-a-year.aspx is a specialized version for a year. This code below calculates the number of weekdays for any given period. Read more →

## How to calculate the number of weekdays in a month

Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period. CREATEFUNCTION dbo.fnMonthWeekDays ( @Year SMALLINT, @Month TINYINT ) RETURNSTINYINT AS BEGIN RETURN ( SELECT 20 + SUM(1 - DATEDIFF(DAY, '17530101', dt) % 7 / 5) FROM ( SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -1) AS dt WHERE ISDATE(10000 * @Year + 100 * @Month + 31) = 1 UNION ALL SELECT DATEADD(MONTH, 12 * @Year - 22800 + @Month, -2) WHERE ISDATE(10000 * @Year + 100 * @Month + 30) = 1 UNION ALL SELECT CASE ISDATE(10000 * @Year + 100 * @Month + 29) WHEN 1 THEN DATEADD(MONTH, 12 * @Year - 22800 + @Month, -3) ELSE '18991231' END ) AS d WHERE @Year BETWEEN 1753 AND 9999 AND@Month BETWEEN 1 AND 12 ) END Legacy Comments Sowmya 2009-10-22 re: How to calculate the number of weekdays in a month Can u please explain the logic ? Read more →

## How to calculate number of weekdays in a year

This function calculates the number of weeksdays in a year, and has error-checking for invalid years. It is also language independant. Here http://weblogs.sqlteam.com/peterl/archive/2009/07/12/How-to-calculate-the-number-of-weekdays-for-any-given.aspx you can find a specialized version that calculates number of weekdays for any given period. Read more →

## Extended Get Nth Weekday of period

Recently I posted a function which returned the Nth weekday of a month, either from the beginning of month or from the end of month. Function is found here http://weblogs.sqlteam.com/peterl/archive/2009/06/17/How-to-get-the-Nth-weekday-of-a-month.aspx I have fiddled around with it and have now extended the function to find the Nth weekday not only for a month, but also for a quarter or a year. Read more →

## Timings of different techniques for finding missing records

I did some tests today to measure the different approaches for finding records present in one table but not in another. The results of CPU and Duration are presented below with some help from SQL Profiler. Read more →

## How to check Danish personal number

CREATEFUNCTION dbo.fnCheckDanSSN ( @SSN CHAR(10) ) RETURNSBIT AS BEGIN IF @SSN NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' RETURN 0 DECLARE @x CHAR(6) SET @x = SUBSTRING(@SSN, 5, 2) + SUBSTRING(@SSN, 3, 2) + SUBSTRING(@SSN, 1, 2) IF SUBSTRING(@SSN, 7, 1) IN('0', '1', '2', '3') AND ISDATE('19' + @x) = 0 RETURN 0 IF SUBSTRING(@SSN, 7, 1) IN('4', '9') SUBSTRING(@SSN, 5, 2) BETWEEN '00' AND '36' AND ISDATE('20' + @x) = 0 RETURN 0 IF SUBSTRING(@SSN, 7, 1) IN('4', '9') SUBSTRING(@SSN, 5, 2) BETWEEN '37' AND '99' AND ISDATE('19' + @x) = 0 RETURN 0 IF SUBSTRING(@SSN, 7, 1) IN('5', '6', '7', '8') SUBSTRING(@SSN, 5, 2) BETWEEN '00' AND '57' AND ISDATE('20' + @x) = 0 RETURN 0 IF SUBSTRING(@SSN, 7, 1) IN('5', '6', '7', '8') SUBSTRING(@SSN, 5, 2) BETWEEN '58' AND '99' AND ISDATE('18' + @x) = 0 RETURN 0 DECLARE @Digits INT SET @Digits = 4 * SUBSTRING(@SSN, 1, 1) + 3 * SUBSTRING(@SSN, 2, 2) + 2 * SUBSTRING(@SSN, 3, 2) + 7 * SUBSTRING(@SSN, 4, 2) + 6 * SUBSTRING(@SSN, 5, 2) + 5 * SUBSTRING(@SSN, 6, 2) + 4 * SUBSTRING(@SSN, 7, 2) + 3 * SUBSTRING(@SSN, 8, 2) + 2 * SUBSTRING(@SSN, 9, 2) + 1 * SUBSTRING(@SSN, 10, 2) RETURN1 - SIGN(@Digits % 11) END Legacy Comments Peso 2009-05-30 re: How to check Danish personal number Which is another variant of this http://www. Read more →

## How to calculate Finnish personal number

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 →

## How to check Finnish personal number

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 →

## Get this group but not that group

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 →

## Extended ISO week function

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 →

## Double duty

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 →

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

## @@ERROR, BEGIN TRY/CATCH and XACT_ABORT

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 →

## One way to calculate decimal year

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 →

## NULL NOT IN conundrum

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 →