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
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
Reference Account_No Branch_No Bank_Name Amount Action_Date Status 10125_Motsomane 1234 151258865 CDF 1200 15/01/2016 ERROR