SQL CPR - borgerens alder

Hvis du er på SQLServer 2005 eller 2008 kunne du kode en C# function (CLR UDF).

Til den slags her tror jeg at C# er langt hurtigere end T-SQL.

Hvis det kun er alderen har du vel fødselsdato i de først 6 cifre?

Men kan vi lave det i en function så?

Som udgangspunkt undgår jeg altid de he inline case-when, de er nemig totalt performance killers :)

@sladrehank.dk:
I et personnummer som 010203-**** kan personen være født i 1903 eller 2003, ligesom 010260 kan være født i 1960 eller 1860.
Det kommer an på det første af de 4 numre, hvilken det er.

@janus_007 & arne_v
det er en god ide - jeg må lige prøve om det er en mulighed, da vores deploy procedure som udgangspunkt ikke tillader stored procedure, fordi vi tømmer databasen flere gang under deploy. Men måske kunne man definere en function i selve scriptet.

Så kan denne her bruges:



alter function dbo.fnCalculateBirthDate(@cpr char(10))
returns datetime
as
begin
    declare @y char(2), @mm char(2), @dd char(2)
    declare @bnum int
    declare @yy char(4)

    set @dd = left(@cpr, 2)
    set @mm = substring(@cpr, 3, 2)
    set @y = substring(@cpr, 5, 2)
    set @bnum = SUBSTRING(@cpr, 7, 4)

    set @yy = '19' + @y
    if @bnum between 4000 and 4999 and @y < 37
        set @yy = '20' + @y
   
    return cast(@yy + @mm + @dd as datetime)

end

select dbo.fnCalculateBirthDate('1104713145')
select dbo.fnCalculateBirthDate('1104114145')

select dbo.fnCalculateBirthDate('1104353145')
select dbo.fnCalculateBirthDate('1104124100')

Jeg har lige lavet den og har angrebet det lidt anderledes end dit forslag.

Hyg dig.

Perfekt - det var lige den inspiration jeg ledte efter.

1> CREATE FUNCTION udf_birthday_1(@cpr CHAR(10)) RETURNS DATETIME AS
2> BEGIN
3>    RETURN CONVERT(DATETIME,
4>    CASE CONVERT(INTEGER, SUBSTRING(@cpr,7,1))
5>        WHEN 0 THEN '19'
6>        WHEN 1 THEN '19'
7>        WHEN 2 THEN '19'
8>        WHEN 3 THEN '19'
9>        WHEN 4 THEN
10>            CASE
11>                WHEN CONVERT(INTEGER, SUBSTRING(@cpr,5,2)) < 37 THEN '20'
12>                ELSE '19'
13>            END
14>        WHEN 5 THEN
15>            CASE
16>                WHEN CONVERT(INTEGER, SUBSTRING(@cpr,5,2)) < 58 THEN '20'
17>                ELSE '18'
18>            END
19>        WHEN 6 THEN
20>            CASE
21>                WHEN CONVERT(INTEGER, SUBSTRING(@cpr,5,2)) < 58 THEN '20'
22>                ELSE '18'
23>            END
24>        WHEN 7 THEN
25>            CASE
26>                WHEN CONVERT(INTEGER, SUBSTRING(@cpr,5,2)) < 58 THEN '20'
27>                ELSE '18'
28>            END
29>        WHEN 8 THEN
30>            CASE
31>                WHEN CONVERT(INTEGER, SUBSTRING(@cpr,5,2)) < 58 THEN '20'
32>                ELSE '18'
33>            END
34>        WHEN 9 THEN
35>            CASE
36>                WHEN CONVERT(INTEGER, SUBSTRING(@cpr,5,2)) < 37 THEN '20'
37>                ELSE '19'
38>            END
39>    END
40>    + SUBSTRING(@cpr,5,2) + '-' + SUBSTRING(@cpr,3,2) + '-' + SUBSTRING(@cpr
,1,2), 102)
41> END
42> GO
1>
2> CREATE FUNCTION udf_birthday_2(@cpr CHAR(10)) RETURNS DATETIME AS
3> BEGIN
4>    DECLARE @yy CHAR(2), @mm CHAR(2), @dd CHAR(2)
5>    DECLARE @bnum INTEGER
6>    DECLARE @yyyy CHAR(4)
7>    SET @dd = LEFT(@cpr, 2)
8>    SET @mm = SUBSTRING(@cpr, 3, 2)
9>    SET @yy = SUBSTRING(@cpr, 5, 2)
10>    SET @bnum = SUBSTRING(@cpr, 7, 4)
11>    IF @bnum between 4000 and 4999 and @yy < 37
12>        SET @yyyy = '20' + @yy
13>    ELSE
14>        SET @yyyy = '19' + @yy
15>    RETURN CAST(@yyyy + @mm + @dd AS DATETIME)
16> END
17> GO
1>
2> CREATE ASSEMBLY cpr FROM 'C:\CPR.dll' WITH PERMISSION_SET = SAFE
3> GO
1>
2> CREATE FUNCTION udf_birthday_3(@cpr NCHAR(10)) RETURNS DATETIME AS
3> EXTERNAL NAME cpr.[E.CPR].Birthday
4> GO
1>
2> SELECT dbo.udf_birthday_1('1104713145'),dbo.udf_birthday_2('1104713145'),dbo.
udf_birthday_3('1104713145')
3> GO

----------------------- ----------------------- -----------------------
1971-04-11 00:00:00.000 1971-04-11 00:00:00.000 1971-04-11 00:00:00.000

(1 row affected)
1> SELECT dbo.udf_birthday_1('1104114145'),dbo.udf_birthday_2('1104114145'),dbo.
udf_birthday_3('1104114145')
2> GO

----------------------- ----------------------- -----------------------
2011-04-11 00:00:00.000 2011-04-11 00:00:00.000 2011-04-11 00:00:00.000

(1 row affected)
1> SELECT dbo.udf_birthday_1('1104353145'),dbo.udf_birthday_2('1104353145'),dbo.
udf_birthday_3('1104353145')
2> GO

----------------------- ----------------------- -----------------------
1935-04-11 00:00:00.000 1935-04-11 00:00:00.000 1935-04-11 00:00:00.000

(1 row affected)
1> SELECT dbo.udf_birthday_1('1104124100'),dbo.udf_birthday_2('1104124100'),dbo.
udf_birthday_3('1104124100')
2> GO

----------------------- ----------------------- -----------------------
2012-04-11 00:00:00.000 2012-04-11 00:00:00.000 2012-04-11 00:00:00.000

(1 row affected)
1> SELECT dbo.udf_birthday_1('1104126100'),dbo.udf_birthday_2('1104126100'),dbo.
udf_birthday_3('1104126100')
2> GO

----------------------- ----------------------- -----------------------
2012-04-11 00:00:00.000 1912-04-11 00:00:00.000 2012-04-11 00:00:00.000

(1 row affected)
1> SELECT dbo.udf_birthday_1('1104826100'),dbo.udf_birthday_2('1104826100'),dbo.
udf_birthday_3('1104826100')
2> GO

----------------------- ----------------------- -----------------------
1882-04-11 00:00:00.000 1982-04-11 00:00:00.000 1882-04-11 00:00:00.000

(1 row affected)
1>
2> CREATE PROCEDURE usp_test AS
3> BEGIN
4>    DECLARE @n INTEGER, @rep INTEGER
5>    DECLARE @dt DATETIME, @startproc DATETIME, @endproc DATETIME
6>    SET @rep = 1
7>    WHILE @rep <= 3
8>    BEGIN
9>        SET @startproc = GETDATE()
10>        SET @n = 1
11>        WHILE @n <= 100000
12>        BEGIN
13>            SET @dt = dbo.udf_birthday_1('1104826100')
14>            SET @n = @n + 1
15>        END
16>        SET @endproc = GETDATE()
17>        PRINT '1: ' + CAST(DATEDIFF(ms, @startproc, @endproc) AS VARCHAR(6))

18>        SET @startproc = GETDATE()
19>        SET @n = 1
20>        WHILE @n <= 100000
21>        BEGIN
22>            SET @dt = dbo.udf_birthday_2('1104826100')
23>            SET @n = @n + 1
24>        END
25>        SET @endproc = GETDATE()
26>        PRINT '2: ' + CAST(DATEDIFF(ms, @startproc, @endproc) AS VARCHAR(6))

27>        SET @startproc = GETDATE()
28>        SET @n = 1
29>        WHILE @n <= 100000
30>        BEGIN
31>            SET @dt = dbo.udf_birthday_3('1104826100')
32>            SET @n = @n + 1
33>        END
34>        SET @endproc = GETDATE()
35>        PRINT '3: ' + CAST(DATEDIFF(ms, @startproc, @endproc) AS VARCHAR(6))

36>        SET @rep = @rep + 1
37>    END
38> END
39> GO
1>
2> usp_test
3> GO
1: 2970
2: 3030
3: 2093
1: 2970
2: 3030
3: 2080
1: 2970
2: 3030
3: 2093
1>
2> DROP PROCEDURE usp_test
3> GO
1> DROP function udf_birthday_1
2> GO
1> DROP function udf_birthday_2
2> GO
1> DROP function udf_birthday_3
2> GO
1> DROP ASSEMBLY cpr
2> GO

using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

namespace E
{
    public class CPR
    {
        [SqlFunction]
        public static SqlDateTime Birthday(string cpr)
        {
            int dd = int.Parse(cpr.Substring(0, 2));
            int mm = int.Parse(cpr.Substring(2, 2));
            int yy = int.Parse(cpr.Substring(4, 2));
            int digit = int.Parse(cpr.Substring(6,1));
            int yyyy = Century(digit, yy) + yy;
            if(yyyy >= 1800)
            {
                return new DateTime(yyyy, mm, dd);
            }
            else
            {
                return SqlDateTime.Null;
            }
        }
        private static int Century(int digit, int yy)
        {
            switch(digit)
            {
                case 0 :
                case 1 :
                case 2 :
                case 3 :
                    return 1900;
                case 4 :
                case 9 :
                    if(yy < 37)
                        return 2000;
                    else
                        return 1900;
                case 5 :
                case 6 :
                case 7 :
                case 8 :
                    if(yy < 37)
                        return 2000;
                    else if(yy > 57)
                        return 1800;
                    else
                        return 0;
                default :
                    return 0;
          }
        }
    }
}

okay - det er ret tydeligt at en C# function giver bedste hastighed. Slutningen på bliver op til deploy drengene.

Tak for jeres indlæg.