Sql-server – Find Employees With Multiple Managers Listed

sql serversql-server-2008-r2t-sql

We outsourced a project and are paying for it! In our table which holds roughly 1500 employees a manager is assigned, but some employees have duplicate managers assigned. How can I using SQL Server 2008 R2 return only each employee who has more than 1 manager assigned (so query window should return the employee and both managers assigned)?

Sample DDL to show table structure and how bad it is messed up atm

Create Table #DuplicationIdentifier
(
  empName varchar(500)
  ,empID varchar(25)
  ,managerID varchar(25)
)

Insert Into #DuplicationIdentifier VALUES
('Blue Bell', 'bb12', 'fj12')
,('Joe Jones', 'jj30','bb41')
,('Mark Jones', 'mj91','ll98')
,('Surge Drink', 'sd22','ll98')
,('Paul Pills', 'pp23', 'ss10')
,('Red Color', 'rc84','ss10')
,('Pink Flamingo', 'pf04','bb41')
,('Blue Bell', 'bb12','bb41')
,('Joe Jones', 'jj30','ss10')
,('Red Color', 'rc84', 'rd22')
,('Pink Flamingo', 'pf04','fj12')

Best Answer

Here is one approach

WITH DuplicateManagers
AS (
    SELECT empid
        ,count(*) AS rcount
    FROM #DuplicationIdentifier
    GROUP BY empid
    HAVING count(*) > 1
    )
SELECT a.empid
    ,a.managerid
FROM #DuplicationIdentifier a
JOIN DuplicateManagers b ON b.empid = a.empID
ORDER BY a.empid
Related Question