Skip to content
Advertisement

Get same employeeid who belongs to different deptno at a particular interval

I am trying to list each employee who belongs more than one dept for a given period of time.

Empid Dept Date
001 10 10/02/2022
002 20 10/02/2022
003 30 10/02/2022
001 20 10/02/2022
002 30 10/02/2022
001 10 11/02/2022
002 20 11/02/2022
003 30 11/02/2022
001 20 11/02/2022
002 30 11/02/2022

From the above source. I need a Output

empid Dept
001 10
001 20
002 20
002 30
003 30

I have written something like below. But it is not giving proper result.

select count(employeeid), dept
from dbo.employee
where date between '2022-02-10' and '2022-02-11'
group by dept
having count(employeeid) > 1

Could someone help me to get the desired output

Advertisement

Answer

Try this:

SELECT 
    EmpId, Dept
FROM dbo.employee
WHERE date BETWEEN '2022-02-10' AND '2022-02-11'
GROUP BY EmpId, Dept
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement