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