quote:

---

Originally posted by iloveorangesoda

I have the following script below which checks my employee table for duplicate employees based on surname, forename, dateofbirth and NINumber and inserts the duplicates into a temp table i.e. @temptable. I now want to change how NINumber is matched. For example i want to now say:

match on the 1st 8 characters of NI Number where the NI Number does not start with TN.
For NI Numbers that start with TN match on all 9 chars.

Can anyone help with the syntax?

code is as follows:

DECLARE @temptable table(f_id int primary key identity(1,1), NhssNumber int, Surname varchar(18), Forename varchar(11), DateOfBirth datetime, NINumber varchar(9), MaxNhssNumber int)
INSERT INTO @temptable
SELECT
a.NhssNumber,
CAST(a.Surname as varchar(18)) as Surname,
CAST(a.Forename as varchar(11)) as Forename,
CONVERT(char(8),a.DateOfBirth,112) as DateOfBirth,
CAST(a.NINumber as varchar(9)) as NINumber, NULL
FROM dbo.t_Employee a
JOIN
(SELECT Surname, Forename,DateOfBirth,NINumber
FROM dbo.t_Employee
GROUP BY Surname, Forename,DateOfBirth,NiNumber
HAVING COUNT(*) > 1) b
ON a.Surname = b.Surname
AND a.Forename = b.Forename
AND a.DateOfBirth = b.DateOfBirth
AND ((a.NiNumber = b.NiNumber AND LEFT(a.NiNumber,2)='TN')
OR (LEFT(a.NiNumber,8)= LEFT(b.NiNumber,8) AND LEFT(a.NiNumber,2)<>'TN')) ORDER BY a.Surname, a.Forename,a.DateOfBirth,a.NiNumber

select * from @temptable

---

quote:

---

Originally posted by iloveorangesoda

Thanks

But I am still having a problem with returning matches on the 1st 8 characters of NI Number where the NI Number does not start with TN?

---