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