Get all employees that belong to exact same location list as the passed in employee

user_465876 0
Viewed 430

I have a table called EmployeeLocationAssn:

CREATE TABLE EmployeeLocationAssn (
[EmployeeLocationAssnId] [int]  IDENTITY(1,1) NOT NULL, 
[EmployeeId] [int] NOT NULL,
[LocationId] [int] NOT NULL
)

This table contains data for employees and their associated locations.

INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (1, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (1, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (2, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (2, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (3, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (3, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (4, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (4, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (4, 3)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (4, 4)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (5, 3)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (5, 4)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (6, 1)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (6, 2)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (6, 3)
INSERT INTO EmployeeLocationAssn (EmployeeId, LocationId) VALUES (6, 4)

I want to get all employees that have the exactly the same location list as passed in employee id.

Example: If the user passes EmployeeId = 1, then the query should return all employees that have the same locations.

Output: 
@EmployeeId = 1
1
2
3

Employees 4 and 6 has locations 1, 2, 3 & 4. It doesn't exactly match with location 1 & 2 that Employee 1 has and Employee 5 has a completely different location list (3, 4).

@EmployeeId = 4
4
6

Employees 1, 2, and 3 has locations 1 & 2. It doesn't exactly match with locations 1, 2, 3 & 4 that Employee 4 has and Employee 5 has a partial location list (3, 4). Only Employee 4 & 6 has the same location list (1, 2, 3, 4).

@EmployeeId = 5
5

Employees 1, 2, and 3 has locations 1 & 2. It doesn't exactly match with locations 3 & 4 that Employee 5 has and Employee 4 & 6 has a bigger location list (1, 2, 3, 4).

I started writing a query but got all confused, here is what I have which of course is not correct.

DECLARE @EmployeeId int = 1

Select ELA.EmployeeId, ELA.LocationId from EmployeeLocationAssn ELA
Where not exists 
(Select ELA.LocationId from EmployeeLocationAssn ELA2 where ELA2.EmployeeId = @EmployeeId
EXCEPT
Select ELA.LocationId from EmployeeLocationAssn ELA3 where ELA3.EmployeeId = ELA.EmployeeId)
and ELA.EmployeeId <> @EmployeeId;
tsql sql-server sql database
2 Answers

You can use string_agg (if using SQL Server 2017+) to compare the Employees:

declare @EmployeeId int = 1;

with cte as (
    select E.EmployeeId
        , string_agg(E.LocationId,',') within group (order by LocationId asc) LocationGroup
    from EmployeeLocationAssn E
    group by E.EmployeeId
)
select EmployeeId
from cte
where LocationGroup = (select LocationGroup from cte where EmployeeId = @EmployeeId);

Or for xml path if using a lower version:

declare @EmployeeId int = 1;

with cte as (
  select E.EmployeeId, 
    substring(
      (
        select ',' + convert(varchar(12),E1.LocationId) as [text()]
        from #EmployeeLocationAssn E1
        where E1.EmployeeId = E.EmployeeId
        order by E1.LocationId
        for xml path ('')
      ), 2, 1000) LocationGroup
  from #EmployeeLocationAssn E
  group by E.EmployeeId
)
select EmployeeId
from cte
where LocationGroup = (select LocationGroup from cte where EmployeeId = @EmployeeId);

dbfiddle

user_11274280

Here's an alternative query that works for your criteria, checking the Locations match those of the selected employee, are not in those not used by the selected employee, and the number of locations match.

 declare @EmployeeId int=1

       ;with x as (
            select locationid, Count(*) over() Qty
            from EmployeeLocationAssn
            where employeeid=@EmployeeId group by LocationId
        )
        select distinct EmployeeId
        from x join EmployeeLocationAssn e on e.LocationId=x.LocationId
        where e.employeeid not in (
            select EmployeeId
            from EmployeeLocationAssn e2
            where LocationId not in (select locationId from x)
        )
        and x.qty=(select Count(*) from EmployeeLocationAssn e3 where e3.EmployeeId=e.EmployeeId)
user_153326500
Related