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
CodePudding user response:
Try this:
SELECT
EmpId, Dept
FROM dbo.employee
WHERE date BETWEEN '2022-02-10' AND '2022-02-11'
GROUP BY EmpId, Dept