Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1

    Exclamation Unanswered: Checking for Last Name, First Name from one Table to Another

    Hey all This is my situation. I have a 2012 MS SQL database that have the following tables:

    -Schedule_2_16_2016
    -EmployeeInfo

    The Schedule_2_16_2016 is created with a different program and I am only able to parse the information out and save it into that database from a PDF.

    The employee names in the Schedule_2_16_2016 table look like this:
    Code:
    empName                |Date_Started |
    -----------------------|-------------| 
    Barker, Bob            | 1/12/2014   |
    Gill, Sam Marco        | 1/1/2014    |
    Scott, Greg D.         | 12/14/2014  |
    Gates, Bill            | 5/19/2014   |
    Jobs, Steve            | 8/18/2014   |
    Allred, Nick E. (Elly) | 7/28/2015   |
    Nuc, Ted               | 9/18/2015   |
    The Employee names in the EmployeeInfo table (That I control) look like this:
    Code:
    empName       |FName  |LName   |
    --------------|-------|--------|
    Bob Barker    | Bob   | Barker |
    Sam Gill      | Sam   | Gill   |
    Greg Scott    | Greg  | Scott  |
    Bill Gates    | Bill  | Gates  |
    Steve Jobs    | Steve | Jobs   |
    Elly Allred   | Elly  | Allred |
    Ted Nuc       | Ted   | Nuc    |
    Notice how my version does not have the middle name and some don't have nicknames like the Schedule_2_16_2016 table does.

    How can I go about an inner join and find the correct employee even though some of the names will not match up with each other?

    The MS SQL query code I am currently using is:
    Code:
    SELECT * 
    FROM [store1234].[dbo].[Schedule_2_16_2016] AS empSchedule
    INNER JOIN [store1234].[dbo].[EmployeeInfo] AS empInfo 
       ON empInfo.empName = empSchedule.empName
    WHERE empSchedule.empName LIKE '%Gill, Sam%'
    This comes up with no records which it should have the record but like I said, it's calling it (in the example above) Sam Gill instead of Gill, Same Marco or Elly Allred instead of Allred, Nick E. (Elly).

    Is there any type of RegEX or something that I can use in order to combine the needed 2 tables together for any 1 person in the WHERE clause?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you want to do it efficiently, use SSIS to convert the names from the Schedule_2_16_2016 table into the format of the EmployeeInfo table. I'd the correct the data for preferred names like Elly.

    There are other ways to get this to (more or less) work, but they are all problematic in one way or another.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2016
    Posts
    1
    Idle way will be like suggest to split the data

    A crude way will be calculating the firstname and last name and joining them ,(assuming comma separated name etc and lot more assumptions ) , more and more chnaces of breaking and so a testing will be needed

    A script like below may give you the output

    SELECT *
    FROM [Schedule_2_16_2016] AS empSchedule
    INNER JOIN [EmployeeInfo] AS empInfo
    ON empInfo.lname= left(empSchedule.empname,charindex(',',empSchedule .empname)-1)
    and empInfo.fname =ltrim(substring(empSchedule.empname,charindex(',' ,empSchedule.empname)+1,charindex(' ',empSchedule.empname,charindex(',',empSchedule.em pname))-2))
    WHERE empSchedule.empName LIKE '%Gill, Sam%'

  4. #4
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    Using this query:
    Code:
    SELECT * 
    FROM [store1234].[dbo].[Schedule_2_16_2016] AS empSchedule
    INNER JOIN [store1234].[dbo].[EmployeeInfo] AS empInfo 
       ON CHARINDEX(empInfo.LName + ', ' + empInfo.FName, empSchedule.empName) = 1
    Gives me 85 returned rows. The schedule table has about 114 rows in it...

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I'd guess that some of your names do not have comma separated value. Try changing your inner join to an outer join, then you can see which one's aren't getting properly translated.
    Dave

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here's a function that you can use to parse western-style names into their components, though you will have to massage your data to deal with those nicknames in parenthesis.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •