SQL query for summary of change between tables-CodePudding

Have two tables EmployeeA and EmployeeB that contain employee info. Both have the same columns like Guid, FullName, Age, Salary, and Address. both the tables have many records, and each employee has max 1 record in each table.

EmployeeA is kind of original table and data can't be changed. so its a read only where data into the EmployeeA gets imported from API.

Initially the data in EmployeeB table comes from the EmployeeA table through UI and user can modify it from UI. So EmployeeB can have modified data.

I need a result of change for only those columns which are modified in EmployeeB table.

Example :

Column Name    EmployeeA's Data  EMployeeB's Data
--------------------------------------------------
Salary         $98000            $110000
Address        ABC, USA          PQR, USA

(There are no changes in FullName and Age columns).

What could a query look like to return the above result set?

CodePudding user response：

Join both table and compare the value from both the tables. Use APPLY to pivot it

select a.GUID, c.ColumnName, c.EmpA, c.EmpB
from   EmployeeA a
       inner join EmployeeB b on a.GUID = b.GUID
       cross apply
       (
           select ColumnName = 'FullName', 
                  EmpA = a.FullName, 
                  EmpB = b.FullName
           where  a.FullName <> b.FullName

           union all

           select ColumnName = 'Age', 
                  EmpA = convert(varchar(100), a.Age), 
                  EmpB = convert(varchar(100), b.Age)
           where  a.Age <> b.Age

           union all

           select ColumnName = 'Salary', 
                  EmpA = convert(varchar(100), a.Salary), 
                  EmpB = convert(varchar(100), b.Salary)
           where  a.Salary <> b.Salary

           union all

           select ColumnName = 'Address', 
                  EmpA = a.Address, EmpB = b.Address
           where  a.Address <> b.Address
       ) c

Note : you might want to change above query to handle null value

CodePudding user response：

There are no sample data, but it could be something like:

WITH 
    A AS
        (
            SELECT 1 "ID", 'NAME 1' "NAME", 100 "SALARY", 'ABC' "ADDRESS", 24 "AGE" FROM DUAL UNION ALL
            SELECT 2 "ID", 'NAME 2' "NAME", 200 "SALARY", 'DEF' "ADDRESS", 30 "AGE" FROM DUAL UNION ALL
            SELECT 3 "ID", 'NAME 3' "NAME", 300 "SALARY", 'GEH' "ADDRESS", 32 "AGE" FROM DUAL 
        ),
    B AS
        (
            SELECT 1 "ID", 'NAME 1' "NAME", 100 "SALARY", 'ABC' "ADDRESS", 24 "AGE" FROM DUAL UNION ALL
            SELECT 2 "ID", 'NAME 2' "NAME", 220 "SALARY", 'DEF, DEF' "ADDRESS", 30 "AGE" FROM DUAL UNION ALL
            SELECT 3 "ID", 'NAME 3' "NAME", 330 "SALARY", 'GEH' "ADDRESS", 32 "AGE" FROM DUAL 
        )
    
    SELECT
        A.NAME "NAME", 'SALARY' "COLUMN_NAME", To_Char(A.SALARY) "A_DATA", To_Char(B.SALARY) "B_DATA"
    FROM
        A
    INNER JOIN
        B ON(A.ID = B.ID)
    WHERE
        A.SALARY <> B.SALARY
UNION ALL
    SELECT
        A.NAME "NAME", 'ADDRESS' "COLUMN_NAME", A.ADDRESS "A_DATA", B.ADDRESS "B_DATA"
    FROM
        A
    INNER JOIN
        B ON(A.ID = B.ID)
    WHERE
        A.ADDRESS <> B.ADDRESS
  ORDER BY 1
-- 
-- Result
-- NAME     COLUMN  A_DATA  B_DATA
-- NAME 2   SALARY  200     220
-- NAME 2   ADDRESS DEF     DEF, DEF
-- NAME 3   SALARY  300     330