CODEWITHSUNDEEP
sqlsql-server
Naila Akbar
Naila Akbar

Reputation: 3358

SQL Server : query to update record with latest entry

I have a table that maintains records of employers and employees' data. Something like this

EmployerName  EmployerPhone EmployerAddress  EmployeeName  EmployeePhone  EmployeeAddress   Date  
-------------------------------------------------------------------------------------------------------
John              12345        NewYork         Harry        59786            NewYork        12-1-1991
Mac               22345        Bankok          John         12345            Delhi          12-3-1991
Smith             54732        Arab            Amar         59226            China          21-6-1991
Sarah             12345        Bhutan          Mac          22345            NewYork        5-9-1991
Root              85674        NewYork         Smith        54732            Japan          2-11-1991

I have another table that will have generic records on the basis of phone number (both employers and employees).

Table structure is as following

Phone Name Address

I want to put latest records according to date from Table1 to Table2 on the basis of phone..

Like this

Phone  Name    Address
-----------------------
59786  Harry  NewYork
22345  Mac    NewYork
59226  Amar   China
12345  Sarah  Bhutan
22345  Mac    NewYork
85674  Root   NewYork
54732  Smith  Arab

I've written many queries but couldn't find anyone resulted as required.

Any kind of help will be appreciated.

Upvotes: 0

Views: 65

Answers (2)

Ian Edwards
Ian Edwards

Reputation: 46

I think this is what you are looking for if I understand your question correctly. Should work for a once-off

DECLARE @restbl TABLE
(
    Name            varchar(100),
    Phone           varchar(20),
    Addr            varchar(100),
    [Date]          date,
    RecType         varchar(100)
)
INSERT INTO @restbl
SELECT  EmployerName, EmployerPhone, NULL, MAX([Date]), 'Employer'
FROM    @tbl
GROUP BY EmployerName, EmployerPhone

INSERT INTO @restbl
SELECT  EmployeeName, EmployeePhone, NULL, MAX([Date]), 'Employee'
FROM    @tbl
GROUP BY EmployeeName, EmployeePhone;

WITH    LatestData (Name, Phone, [Date])
        AS
        (
            SELECT  Name, Phone, MAX([Date])
            FROM    @restbl
            GROUP BY Name, Phone
        )
INSERT INTO FinalTable (Name, Phone, [Address])
SELECT  DISTINCT ld.Name, ld.Phone, ISNULL(tEmployer.EmployerAddress, tEmployee.EmployeeAddress) AS [Address]
FROM    LatestData ld
        LEFT JOIN @tbl tEmployer ON ld.Name = tEmployer.EmployerName AND ld.Phone = tEmployer.EmployerPhone AND ld.Date = tEmployer.Date
        LEFT JOIN @tbl tEmployee ON ld.Name = tEmployee.EmployeeName AND ld.Phone = tEmployee.EmployeePhone AND ld.Date = tEmployee.Date

Upvotes: 1

Plebios
Plebios

Reputation: 835

For initialize the table without phone duplicates:

   INSERT IGNORE INTO Table2 (Phone, Name, Address)
       SELECT X.* FROM (
            SELECT EmployeeName,EmployeePhone,EmployeeAddress FROM Table1             
            UNION
            SELECT EmployerName,EmployerPhone,EmployerAddress FROM Table1
           ) X
             WHERE NOT EXISTS (SELECT Phone FROM Table2 WHERE Phone=X.Phone)

Upvotes: 1

Related Questions