Cprnr tips til SQL udregning af fødselsdato og alder samt køn

CPR nummer tips til SQL udregning af fødselsdato og alder samt køn

af Joakim Dalby

CPR nummer beregning af fødselsdato og alder sker på basis af regler fra www.cpr.dk, hvor der anvendes en regel tabel, så der fås det rigtige århundrede. Tabellen kaldes:

DSA_RULE_CprnrFødtÅrhundrede og anvendes af de to nedenstående user defined functions ved beregning af dbo.Get_Fødselsdato og dbo.Get_Alder:

Id	7 pos	FødselsårFra	FødselsårTil	LøbenummerFra	LøbenummerTil	Århundrede
1	0,1,2,3	0	99	0	3999	19
2	4	0	36	4000	4999	20
3	4	37	99	4000	4999	19
4	5	0	36	5000	5999	20
5	5	58	99	5000	5999	18
6	6	0	36	6000	6999	20
7	6	58	99	6000	6999	18
8	7	0	36	7000	7999	20
9	7	58	99	7000	7999	18
10	8	0	36	8000	8999	20
11	8	58	99	8000	8999	18
12	9	0	36	9000	9999	20
13	9	37	99	9000	9999	19

Kilde: CPR, Hvordan udregnes alder, side 17.pdf.

Modulus-11 kontrol gælder ikke mere for det sidste kontrolciffer.

Hurtig udregning af en persons alder ud fra personens CPR nummer

At kalde en user defined function i en Select sætning på mange rækker i en tabel koster på svartiden. For at få en bedre svartid og query performance på mange personer anvender jeg her kolonne 7 position af et CPR nummer sammen med alderen i position 5-6 til at bestemme om det er 18, 19 eller 20 århundrede. Jeg omdanner CPR nummerets dato til et heltal med ååååmmdd f.eks. 7. februar 1966 bliver til 19660207, og det fratrækker jeg dagsdato som heltal for at få den præcise alder. Select sætningen er ikke pæn at læse, men hvad gør man ikke for performance:

SELECT CPR, Navn,

((cast(format(getdate(),'yyyyMMdd') as int)) -

(

CASE

WHEN TRY_CAST(concat('20',substring(CPR, 5, 2), substring(CPR, 3, 2),substring(CPR, 1, 2)) as date) is not null

THEN --Valid date for calculation of Age but do not cover 1900-02-29 because I am use fixed 20 century above.

CASE WHEN substring(CPR, 7, 1) in('0','1','2','3')

THEN cast(concat('19',substring(CPR, 5, 2),substring(CPR, 3, 2),substring(CPR, 1, 2)) as int)

WHEN substring(CPR, 7, 1) = '4' and substring(CPR, 5, 2) between '00' and '36'

THEN cast(concat('20',substring(CPR, 5, 2),substring(CPR, 3, 2),substring(CPR, 1, 2)) as int)

WHEN substring(CPR, 7, 1) = '4' and substring(CPR, 5, 2) between '37' and '99'

THEN cast(concat('19',substring(CPR, 5, 2),substring(CPR, 3, 2),substring(CPR, 1, 2)) as int)

WHEN substring(CPR, 7, 1) in('5','6','7','8') and substring(CPR, 5, 2) between '00' and '36'

THEN cast(concat('20',substring(CPR, 5, 2),substring(CPR, 3, 2),substring(CPR, 1, 2)) as int)

WHEN substring(CPR, 7, 1) in('5','6','7','8') and substring(CPR, 5, 2) between '58' and '99'

THEN cast(concat('18',substring(CPR, 5, 2),substring(CPR, 3, 2),substring(CPR, 1, 2)) as int)

WHEN substring(CPR, 7, 1) = '9' and substring(CPR, 5, 2) between '00' and '36'

THEN cast(concat('20',substring(CPR, 5, 2),substring(CPR, 3, 2),substring(CPR, 1, 2)) as int)

WHEN substring(CPR, 7, 1) = '9' and substring(CPR, 5, 2) between '37' and '99'

THEN cast(concat('19',substring(CPR, 5, 2),substring(CPR, 3, 2),substring(CPR, 1, 2)) as int)

END

)

) / 10000 as Alder --Calculation of Age/Alder.

FROM dbo.Person

Når Alder beregningen bliver Null, eller er uden for intervallet fra 0 til 130, så er der tale om et forkert CPR nummer, og derved kan ovenstående bruges til at validere et CPR nummer om det er gyldigt eller ikke gyldigt. Med undtagelsen for datoen 29-02-1900 som ikke en gyldig dato, fordi et århundrede er skudår når 400 går op i årstallet f.eks. årene 1600 og 2000 men ikke 1900 selvom 4 går på i årstallet som er den normale regel for skudår.

Funktion udregning af en persons fødseldato og alder ud fra personens CPR nummer

Nedenfor vises en klassisk funktion som anvender de to Løbenummer kolonner.

CREATE FUNCTION [dbo].[Get_Fødselsdato](@Cprnr nchar(10))

RETURNS date

BEGIN

DECLARE @Fødselsdato date

DECLARE @dd int

DECLARE @mm int

DECLARE @åå int

DECLARE @lb int -- løbenummer

DECLARE @åh int

DECLARE @str nvarchar(10)

IF @Cprnr IS NULL

RETURN GetDate()

SET @dd = CAST(SUBSTRING(@Cprnr, 1, 2) AS int)

SET @mm = CAST(SUBSTRING(@Cprnr, 3, 2) AS int)

SET @åå = CAST(SUBSTRING(@Cprnr, 5, 2) AS int)

SET @lb = CAST(SUBSTRING(@Cprnr, 7, 4) AS int)

SELECT @åh = Århundrede

FROM dbo.DSA_RULE_CprnrFødtÅrhundrede

WHERE @åå BETWEEN FødselsårFra AND FødselsårTil AND

@lb BETWEEN LøbenummerFra AND LøbenummerTil

SET @åå = (@åh * 100) + @åå

SET @str = CAST(@åå AS nvarchar) +

RIGHT('0' + CAST(@mm AS nvarchar),2) +

RIGHT('0' + CAST(@dd AS nvarchar),2) -- ååååmmdd

IF ISDATE(@str) = 1

SET @Fødselsdato = @str

ELSE

SET @Fødselsdato = GetDate()

RETURN @Fødselsdato

END

CREATE FUNCTION [dbo].[Get_Alder](@Cprnr nchar(10), @Dato date)

RETURNS smallint

BEGIN

DECLARE @Fødselsdato datetime

DECLARE @Alder smallint

DECLARE @dd int

DECLARE @mm int

DECLARE @åå int

DECLARE @lb int -- løbenummer

DECLARE @åh int

DECLARE @str nvarchar(10)

IF @Cprnr IS NULL

RETURN 0

SET @dd = CAST(SUBSTRING(@Cprnr, 1, 2) AS int)

SET @mm = CAST(SUBSTRING(@Cprnr, 3, 2) AS int)

SET @åå = CAST(SUBSTRING(@Cprnr, 5, 2) AS int)

SET @lb = CAST(SUBSTRING(@Cprnr, 7, 4) AS int)

SELECT @åh = Århundrede

FROM dbo.DSA_RULE_CprnrFødtÅrhundrede

WHERE @åå BETWEEN FødselsårFra AND FødselsårTil AND

@lb BETWEEN LøbenummerFra AND LøbenummerTil

SET @åå = (@åh * 100) + @åå

SET @str = CAST(@åå AS nvarchar) +

RIGHT('0' + CAST(@mm AS nvarchar),2) +

RIGHT('0' + CAST(@dd AS nvarchar),2) -- ååååmmdd

IF ISDATE(@str) = 0

RETURN 0

SET @Fødselsdato = @str

IF @Dato IS NULL

SET @Dato = Getdate()

SET @Alder = DATEDIFF(year, @Fødselsdato, @Dato)

-- Alders korrektion giver præcis alder i forhold til @Dato

IF (DATEPART(month, @Dato) < DATEPART(month, @Fødselsdato))

OR (DATEPART(month, @Dato) = DATEPART(month, @Fødselsdato)

AND DATEPART(day, @Dato) < DATEPART(day, @Fødselsdato))

SET @Alder = @Alder - 1

RETURN @Alder

END

CREATE FUNCTION [dbo].[Get_Køn](@Cprnr nchar(10))

RETURNS nchar(1)

BEGIN

DECLARE @Køn nchar(1)

DECLARE @kc int -- Kontrolciffer, sidste nummer bestemmer køn.

IF @Cprnr IS NULL

RETURN 'M'

SET @kc = CAST(SUBSTRING(@Cprnr, 10, 1) AS int)

IF @kc % 2 = 0

SET @Køn = 'K'

ELSE

SET @Køn = 'M'

RETURN @Køn

END

Mange ID-numre i Danmark

For personer normalt CPR-NR (personnummer).
For juridiske personer (selskaber, foreninger m.v.) CVR-NR / SE-NR.
I de tilfælde, hvor ingen af de foranstående numre haves, tildeles et individuelt ENR

(erstatningsnummer), der opbygges som et personnummer, men hvor de sidste fire cifre er erstattet af bogstaver. [Mangler fødselsårstallet?]

I sundhedssektoren får udenlandske personer et ErstatningsCPR-NR på følgende måde: AAAAAA-BCCD:

A = Fødselsdato på formen DDMMÅÅ.

B = Århundrede for fødsel hvor 5 er før 1900, 0 er 1900-1999, og 6 er fra 2000.

C = Initialer på personen (efternavn fornavn).

D = Løbenummer (lige for kvinder (2), ulige for mænd (1)).

Når flere personer har samme fødselsdag og initialer, anvendes løbenummeret til at gøre dem unikke f.eks.: 290288-0GP2, 290288-0GP4, 290288-0GP6.

NemId og MitId brugerid skal være unikt for alle borgere og brugere i Danmark. Mange har brugt deres CPR nummer som brugerid for NemId, men det usikkert fordi man indtaster det ved login. Derfor kræver MitId både bogstaver og tal. Man kan f.eks. bruge sit telefonnummer som brugerid: Telefon39662613.