April 27, 2012 at 8:50 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)
birthday (datetime, 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.
Using a join on the two tables?
April 27, 2012 at 8:54 am
Please don't cross post. It just fragments responses. Please direct all replies here. http://www.sqlservercentral.com/Forums/Topic1291608-1292-1.aspx
_______________________________________________________________
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply