Eliminating duplicate customer records

[tribalsushi]

G'day,

I have a table (Customers) that contains customer records in something
similar to the following format....

CustomerID | ReferralDate | OutcomeDate | FirstName | LastName
4567 | 01/01/2008 | 02/04/2008 | Jane | Bloggs
1231 | 01/02/2008 | 02/05/2008 | Dave | Bloggs

and so on.

However, this table contains several duplicate customer records -- whenver a
customer is re-referred to us, the original source for this table will create
another record, duplicating all the customer information except the referral
date.

Furthermore, there are a handful of instances where the jobseeker is
referred to us twice on the same day (in these instances, ReferralDate and
OutcomeDate are identical).

Is there a way to append only the records with unique CustomerIDs and the
most recent ReferralDate to another table using a SQL query?

 

[Ken Snell \(MVP\)]

Something like this, perhaps:

INSERT INTO NewTable
(CustomerID, ReferralDate, OutcomeDate, FirstName, LastName)
SELECT CustomerID, Max(ReferralDate), OutcomeDate, FirstName, LastName
FROM Customers
GROUP BY CustomerID, OutcomeDate, FirstName, LastName;