SQLTeam.com | Weblogs | Forums

Help with creating T-SQL function syntax

Transact-SQL
#1

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

Thanks in advance

#3

Welcome

Please provide sample data with desired output?

declare @mushroom table(x int)

insert into @mushroom
select 1 union
select 2
1 Like
#4

The problem description is nonsense.

#5

this might help?

1 Like
#6

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.

#7

Thank you :slight_smile:

#8

@kevinj ,

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. :smiley:

2 Likes
#9

This is as far as I get based on your information:

DECLARE @DayOfBirth DATE
SET @DayOfBirth=DATEFROMPARTS(1990,02,10)

SELECT @DayOfBirth

SELECT YEAR(@DayOfBirth) * MONTH(@DayOfBirth) * DAY(@DayOfBirth)
-- Result: 39800

SELECT 3 + 9 + 8 + 0+ 0
-- RESULT: 20

1 Like
#10

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

#11

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);
1 Like
#12

nice name :smiley:, thanks i think i got it.