April 25, 2012 at 6:59 am
I would like to write a query that returns any Employees (id, ssn, and name) with an invalid SSN along with the company name. From the following two tables information.
tblCompany
co (PK, varchar(10), not null)
name (varchar (50), not null)
tblEmployees
co (PK, FK, varchar(10), not null)
id (PK, varchar(10), not null)
ssn (varchar(11), not null)
lastName (varchar(40), not null)
firstName (varchar(40), not null)
An invalid or impossible SSN is one that we never assigned.
We have never assigned an SSN with the first three digits of:
000
666
900 series
Additionally, prior to June 25, 2011, we never assigned an SSN with the first three digits of:
000
666
Above 772 in the 700 series
800 series
900 series
We have never assigned an SSN with the second two digits of 00 or the last four digits of 0000.
What i have so far is;
SELECT id, ssn, lastName, firstName
FROM employees
WHERE ssn
GROUP BY name
April 25, 2012 at 7:17 am
WHERE ssn NOT LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
will check that all data is numeric with the dashes in the right place. Beyond that, you need to specify any other validation rules.
April 25, 2012 at 7:21 am
Looks like Steve beat me to this... However, here is a quick test for what Steve is showing.
-- Define the tables
DECLARE @tblCompany TABLE (co varchar(10), name varchar (50));
DECLARE @tblEmployees TABLE (co varchar(10),id varchar(10), ssn varchar(12),lastName varchar(40), firstName varchar(40));
-- Populate the tables
INSERT @tblCompany (co, name) VALUES ('A','Company A');
INSERT @tblCompany (co, name) VALUES ('B','Company B');
INSERT @tblEmployees (co, id, ssn, lastName, firstName) VALUES ('A',1,'123-23-1234','Tester', 'Joe');
INSERT @tblEmployees (co, id, ssn, lastName, firstName) VALUES ('A',2,'23121-2341','Tester', 'Jane');
INSERT @tblEmployees (co, id, ssn, lastName, firstName) VALUES ('A',3,'312-22-3412','User', 'John');
INSERT @tblEmployees (co, id, ssn, lastName, firstName) VALUES ('A',4,'234234123','User', 'Janet');
INSERT @tblEmployees (co, id, ssn, lastName, firstName) VALUES ('B',5,'342-2N-3234','Manager', 'Sam');
INSERT @tblEmployees (co, id, ssn, lastName, firstName) VALUES ('B',6,'432-254234','Tiger', 'Scott');
-- Verify the data
SELECT * FROM @tblCompany
SELECT * FROM @tblEmployees
-- First Basic Check
SELECT * FROM @tblEmployees WHERE ssn NOT LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'
And here is the books online information about it.
http://msdn.microsoft.com/en-us/library/ms179859(v=sql.105).aspx
April 25, 2012 at 9:12 am
This is only a regular expression and does not verify that the number is in fact valid.
The middle two numbers, referred to as the group number, have some additional specific rules. There also some specific numbers that are reserved and other that are not valid. They can't start with 666, none of the groups can contain all 000 etc. There are some other specific rules but I don't know them all off the top of my head.
If you just want to validate the format this will work. If you want to verify that the numbers is in fact valid you will probably want to use CLR because the rules are incredibly complex.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 9:28 am
If I also remember, the SSN 555-55-5555 is invalid.
Not sure about other single repeating values, but the one above is usually used in various illustrations.
April 25, 2012 at 9:34 am
Figured I would dig a little and try to refresh my memory, it has been awhile since I had to deal with SSNs.
http://en.wikipedia.org/wiki/Social_Security_number
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 9:35 am
Something like this will work:
SELECT *
FROM ( VALUES ( '123-45-6789'), ( '123-54-6789'), ( '923-00-1234'), ( '123-00-1234'), ( '123-45-0000'),
( '000-12-1234') ) AS V (SSN)
WHERE SSN LIKE '000-__-____'
OR SSN LIKE '___-00-____'
OR SSN LIKE '___-__-0000'
OR SSN LIKE '666-__-____'
OR SSN LIKE '9[0-9][0-9]-__-____' ;
You'd have to have an issue-date (might be able to use date-of-birth, but it's not really valid) for the rules about those issued before a certain date. Otherwise, it could be legit or could be not.
Could do something like:
SELECT *,
CASE WHEN SSN LIKE '77[3-9]-__-____' THEN 'Check Date-of-Issue'
WHEN SSN LIKE '7[8-9]_-__-____' THEN 'Check Date-of-Issue'
WHEN SSN LIKE '8__-__-____' THEN 'Check Date-of-Issue'
ELSE 'Invalid'
END AS Rule2
FROM ( VALUES ( '123-45-6789'), ( '823-54-6789'), ( '123-54-6789'), ( '923-00-1234'), ( '123-00-1234'), ( '123-45-0000'),
( '000-12-1234') ) AS V (SSN)
WHERE SSN LIKE '000-__-____'
OR SSN LIKE '___-00-____'
OR SSN LIKE '___-__-0000'
OR SSN LIKE '666-__-____'
OR SSN LIKE '9__-__-____'
OR SSN LIKE '8__-__-____'
OR SSN LIKE '7[8-9]_-__-____'
OR SSN LIKE '77[3-9]-__-____' ;
If it finds a positive rule-break, it says "Invalid", but if it's just a possible rule-break, depending on date, it says so.
That only covers the rules mentioned in the post. It doesn't check for malformed SSNs. It doesn't check for duplicate SSNs. It doesn't check for SSNs issued from Connecticut for people born and raised in Hawaii. And so on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 25, 2012 at 9:38 am
It doesn't check for SSNs issued from Connecticut for people born and raised in Hawaii.
ROFL!!!! 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 9:41 am
Sean Lange (4/25/2012)
It doesn't check for SSNs issued from Connecticut for people born and raised in Hawaii.
ROFL!!!! 😛
Yeah, I couldn't resist. Or, more honestly, I could have, but chose not to for the humor of it all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 25, 2012 at 3:26 pm
GSquared (4/25/2012)
Sean Lange (4/25/2012)
It doesn't check for SSNs issued from Connecticut for people born and raised in Hawaii.
ROFL!!!! 😛
Yeah, I couldn't resist. Or, more honestly, I could have, but chose not to for the humor of it all.
ok lets leave the politics for the anything else forum.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 25, 2012 at 3:30 pm
Well he just using that as an example that it does not validate the state of origin of the SSN. I have no idea what you mean about politics. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 3:34 pm
Sean Lange (4/25/2012)
Well he just using that as an example that it does not validate the state of origin of the SSN. I have no idea what you mean about politics. 😉
ROTFLMAO
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 27, 2012 at 3:46 pm
To what level of validation are you attempting to complete?
April 27, 2012 at 3:59 pm
Basically looking to use a JOIN on the two tables to find invalid ssn using the invalid rules listed.
April 27, 2012 at 4:17 pm
mwaniki1 (4/27/2012)
Basically looking to use a JOIN on the two tables to find invalid ssn using the invalid rules listed.
You keep saying join to validate. What are you joining to what to validate ssn? From what I see, you have two tables (Company and Employee) and there is nothing in either with which to validate the SSN against. Your query has to validate the SSNs and report which ones are invalid.
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply