Hi im a beginner in SQL and new here at the forum.
I have a problem trying to create a function where we use modulus-10 method.
its creating a SSN using this method
Multiply the numbers by year of birth, month, day and number alternately by 2 and 1.
Add the numbers in the products. In numbers exceeding 9, the numbers are added, e.g. 12 is calculated as 1 + 2.
Subtract the singular digit from the number from the number 10.
The residual digit becomes the control digit.
If the remainder is 10, the check digit becomes 0.
So i want to Create this by either
yyMMddXXXX
or with a hyphen in between
yyMMdd-XXXX
Hello again, im sorry english is not my first language. so ill try to describe here what im trying to do
The input parameter into the function is a social number in one of these formats:
• YYMMDDXXXX
• YYMMDD-XXXX
The control numer is the last X which is the number you get after all calculations which verifies your birthdate.
The function should return 1 if the check digit is correct, 0 if the check digit is incorrect or if there is another error.
Using this method:
Multiply the numbers by year of birth, month, day and number alternately by 2 and 1.
Add the numbers in the products. In numbers exceeding 9, the numbers are added, e.g. 12 is calculated as 1 + 2.
So e.g if i put 19900210- 2450 it counts it times 1 and 2 alternately.
Subtract the singular digit from the number from the number 10.
The residual digit becomes the control digit.
If the remainder is 10, the check digit becomes 0.
I can't speak for anyone else but SSNs (Social Security Numbers) don't come in the form of YYMMDDXXXX or YYMMDD-XXXX. I believe that might be why people are confused. I certainly am.
Sorry for that, but its how swedish SSNs look like. basically you get the Control number (the last X of the four Xs).
eg. like this: 8 (*2) + 9 (*1) + 1(*2) + 1(*1) + 1(*2) + 0(*1) - XXXX (birthdate 891110-XXXX)
so that gets 16 + 9 + 2 + 1 + 2 + 0
you then add them like single digits: 1+6+9+2+1+2+0 = 21
The Control number (last X) is now the value you must add to the digit sum for it to be evenly divisible by 10. If your digit sum is 21, you must add 9 for it to be 30, which is divisible by 10.
so 9 is the Control number (last X in SSN) because that what i have to add to get evenly devisible 30 (21 + 9 = 30).
so if your SSN is 891110 - 1526 its wrong which my function should show (0)
but if my SSN is 891110 - 1529 its right according to my function which should show (1)
ive tried by going by '[0-9][0-9][0-9][0-9][0-9][0-9][0-9] and also tried different DATE forms but its hard getting a formula done for me which controls the whole birthdate to get the right controlnumber.
I also have a hard time getting past the hyphen.
I hope you guys get it this time, and also thank you in advance
For me it's still not clear how you get the 152. But this is how far I got:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER FUNCTION SwedishHouseMafia
(
@DontYouWorryChild BIGINT
)
RETURNS BIT
AS
BEGIN
DECLARE @DayOfBirth DATE
DECLARE @ControlNumber TINYINT
DECLARE @Result BIT
SET @DayOfBirth=DATEFROMPARTS(1989,11,10)
SELECT @ControlNumber= 10 -
(CASE
WHEN CONVERT(INT,(LEFT(RIGHT(YEAR(@DayOfBirth),2),1) * 2)) >= 10 THEN
CONVERT(INT,LEFT((LEFT(RIGHT(YEAR(@DayOfBirth),2),1) * 2),1)) +
CONVERT(INT,RIGHT((LEFT(RIGHT(YEAR(@DayOfBirth),2),1) * 2),1))
ELSE
(LEFT(RIGHT(YEAR(@DayOfBirth),2),1) * 2)
END +
(RIGHT(YEAR(@DayOfBirth),1) * 1) +
(LEFT(RIGHT(MONTH(@DayOfBirth),2),1) * 2) +
(RIGHT(MONTH(@DayOfBirth),1) * 1) +
(LEFT(RIGHT(DAY(@DayOfBirth),2),1) * 2) +
(RIGHT(DAY(@DayOfBirth),1) * 1)) % 10
IF RIGHT(@DontYouWorryChild,1)=@ControlNumber
SET @Result= 1
ELSE
SET @Result= 0
RETURN @Result
END
GO
SELECT dbo.SwedishHouseMafia(8911101526);
SELECT dbo.SwedishHouseMafia(8911101529);
