# Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

## May 2009 Blog Posts

##### How to check Danish personal number

CREATE FUNCTION dbo.fnCheckDanSSN (             @SSN CHAR(10) ) RETURNS BIT 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)...

posted @ Friday, May 29, 2009 2:26 PM | Feedback (2) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to calculate Finnish personal number

CREATE FUNCTION dbo.fnCalculateFinSSN (             @SSN CHAR(10) ) RETURNS CHAR(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

posted @ Friday, May 29, 2009 2:07 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### How to check Finnish personal number

CREATE FUNCTION dbo.fnCheckFinSSN (             @SSN CHAR(11) ) RETURNS BIT 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

posted @ Friday, May 29, 2009 1:46 PM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### 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. This can be done in a number of ways, but I wanted to write an efficiently running query. DECLARE     @Sample TABLE             (                         MeetingID INT,                          TicketID INT             )   INSERT      @Sample SELECT      1, 11 UNION ALL SELECT      1, 22 UNION ALL SELECT      1, 33 UNION ALL SELECT      2, 22 UNION ALL SELECT      2, 33 UNION ALL SELECT      3, 11 UNION ALL SELECT     ...

posted @ Friday, May 29, 2009 10:57 AM | Feedback (0) | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

##### Extended ISO week function

CREATE FUNCTION    dbo.fnISOWEEK (     @theDate DATETIME ) RETURNS TINYINT 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                         )...

posted @ Wednesday, May 27, 2009 9:52 AM | Feedback (0) | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]