CODEWITHSUNDEEP
sql
sandilya M
sandilya M

Reputation: 109

An SQL query to pull count of employees absent under each manager on all dates

The objective of the query is get a count of employees absent under each manager.

Attendance (Dates when employees are present)

id  date
1   16/05/2020
2   16/05/2020
1   17/05/2020
2   18/05/2020
3   18/05/2020

Employee

id  manager_id
1   2
2   3
3   NA

The desired output should be in this format:

Date    manager_id  Number_of_absent_employees
16/05/2020  NA         1
17/05/2020  3          1
17/05/2020  NA         1
18/05/2020  2          1

I have tried writing code but partially understood it, intuition being calculating total number of actual employees under each manager and subtracting it from number of employees present on given day. Please help me in completing this query, many thanks!

with t1 as  /* for counting total employees under each manager */
(
select employee.manager_id,count(*) as totalc
from employee as e
inner join employee on e.employee_id=employee.employee_id
group by employee.manager_id
)

,t2 as /* for counting total employees present each day */
(
select Attendence.date, employee.manager_id,count(*) as present
from employee
Left join Attendence on employee.employee_id=Attendence.employee_id
group by Attendence.date, employee.manager_id 
)

select * from t2
Left join t1 on t2.manager_id=t1.manager_id 
order by date

Upvotes: 0

Views: 1426

Answers (3)

Olga Romantsova
Olga Romantsova

Reputation: 1081

Try this query. In first cte just simplify your code. And in the last query calculate absent employees.

--in this CTE just simplify counting

with t1 as  /* for counting total employees under each manager */
(
 select employee.manager_id,count(*) as totalc
 from employee 
 group by manager_id
)

,t2 as 
(
 select Attendence.date, employee.manager_id,count(*) as present
 from employee
 Left join Attendence on employee.employee_id=Attendence.employee_id
 group by Attendence.date, employee.manager_id 
)


select t2.date,t2.manager_id, (t1.totalc-t2.present) as employees_absent  from t2
Left join t1 on t2.manager_id=t1.manager_id 
order by date

Upvotes: 1

Himanshu Agrawal
Himanshu Agrawal

Reputation: 271

Select ec.manager_id, date, (total_employees - employee_attended) as employees_absent from
(Select manager_id, count(id) as total_employees
from employee
group by manager_id) ec,
(Select distinct e.manager_id, a.date, count(a.id) over (partition by e.manager_id, a.date) as employee_attended 
from Employee e, attendence, a
where e.id = a.id(+)) ea
where ec.manager_id = ea.manager_id (+)

I guess this should work

Upvotes: 0

forpas
forpas

Reputation: 164139

Cross join the distinct dates from Attendance to Employee and left join Attendance to filter out the matching rows.
The remaining rows are the absences so then you need to aggregate:

select d.date, e.manager_id, 
       count(*) Number_of_absent_employees
from (select distinct date from Attendance) d
cross join Employee e
left join Attendance a on a.date = d.date and a.id = e.id
where a.id is null
group by d.date, e.manager_id

See the demo.
Results:

| date       | manager_id | Number_of_absent_employees |
| ---------- | ---------- | -------------------------- |
| 16/05/2020 | NA         | 1                          |
| 17/05/2020 | 3          | 1                          |
| 17/05/2020 | NA         | 1                          |
| 18/05/2020 | 2          | 1                          |

Upvotes: 2

Related Questions