April 27, 2012 at 8:44 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:55 am
What is wrong with the answer you got in your other thread?
http://www.sqlservercentral.com/Forums/FindPost1290018.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/
April 27, 2012 at 3:22 pm
Using “_” which would only match a single character and thus would not match the intended records. Also, I updated to add the birthday field which basically is necessary to complete the exercise. Finally, missing a join to the company table and your GROUP BY is used incorrectly (and would not be needed).
April 27, 2012 at 3:27 pm
Please don't start multiple threads for the same problem.
No further replies to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/Topic1289821-1292-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply