Sql-server – How to link data from two spreadsheets imported to sql

sql serversql-server-2008

I imported 2 spreadsheets to SQL Server tables. One sheet has data containing persons name & surname, the project they are working on as well as there banking details. The other spreadsheet has only the names & surnames of persons and banking details.

Sheet 2 data needs to be grouped to the right projects like in sheet 1. The only connection I found is that the Reference column in Sheet 2 has numbers that match the ParentID on sheet 1 but with a suffix appended.

Beneficiaries with the same ParentID belong to the same Project. I need a way to extract the numeric values of Reference and match them to the corresponding ParentID in order to group the Beneficiaries in the right project.

sheet 1 : 

BeneficiaryID      float         Checked
BeneficiaryName    nvarchar(255) Checked
BeneficiarySurname nvarchar(255) Checked
IDNumber           float         Checked
DOB                nvarchar(255) Checked
BranchnameID       nvarchar(255) Checked
BranchCode         float         Checked
AccountType        nvarchar(255) Checked
AccountNumber      float         Checked
TotalWages         float         Checked
[(No column name)] float         Checked
ParentId           float         Checked
PPRDate            datetime      Checked

sheet 2 :

Reference     nvarchar(255) Checked
[Account No]  nvarchar(255) Checked
[Branch No]   nvarchar(255) Checked
[Bank Name]   nvarchar(255) Checked
[Acc Type]    nvarchar(255) Checked
Amount        nvarchar(255) Checked
[Action Date] nvarchar(255) Checked
Status        nvarchar(255) Checked

Here is some sample
sheet 1

BeneficiaryID   BeneficiaryName     BeneficiarySurname   IDNumber   DOB Branchname ID   BranchCode  AccountNumber   TotalWages      ParentId    PPRDate    Caption

1202            Chris               Manda                123456     4/6/1995 1               ABC         123             120          10125    June 2018  Agriculture

sheet 2

Reference       Account_No Branch_No    Bank_Name   Amount  Action_Date Status


10125_Motsomane 1234       151258865    CDF         1200    15/01/2016 ERROR

Best Answer

The only connection I found is that the Reference column in Sheet 2 has numbers that match the ParentID on sheet 1 but with a suffix appended.

You need to match float value 10125 (column ParentID) on Sheet1 with NVarchar(255) value 10125_Motsomane (column Reference) from Sheet2.

If data types would match you would simply do a direct INNER JOIN:

SELECT
    S1.ParentID,
    S2.Reference,
    AnyOtherColumnYouNeed
FROM
    Sheet1 AS S1 
    INNER JOIN Sheet2 AS S2 ON S1.ParentID = S2.Reference

But to extract the number before the underscore you can use CHARINDEX to find the position of the underscore and then LEFT to get the value up to that position:

DECLARE @ExampleValue NVARCHAR(255) = N'10125_Motsomane'

SELECT
    Original = @ExampleValue,
    Trimmed = LEFT(
        @ExampleValue,
        CHARINDEX(N'_', @ExampleValue) - 1)

Result:

Original            Trimmed
10125_Motsomane     10125

Applying this on the join (will do an implicit conversion from NVARCHAR to FLOAT):

SELECT
    S1.ParentID,
    S2.Reference,
    AnyOtherColumnYouNeed
FROM
    Sheet1 AS S1 
    INNER JOIN Sheet2 AS S2 ON S1.ParentID = LEFT(S2.Reference, CHARINDEX(N'_', S2.Reference) - 1)

Keep in mind that the LEFT function will fail if there is no underscore on a value for Reference, since it will return -1 as position.

Related Question