Hello,
I need to extract the names of the employees which have not been active
during the last 3 months from the following tables
EMPLOYEES:
employee_id
name
COOPERATIVE
cooperative_id
company_name
HOURS
employee_id
customer_id
year_month <-- e.g. 06/2006 (Date/Time field)
regular_hours
nightly_hours
I need to know the names of the employees which either do not appear in the
HOURS table in the last three months or do appear but have 0 total hours
(regular+nightly) in the three months.
The following is the way I got the result, but it is definitely too slow: it
starts displaying the results in the query results, but it hangs when I
change record page.
SELECT distinct name, company_name from employees a, cooperative b
where a.cooperative_id=b.cooperative_id AND a.employee_id not in
(SELECT distinct hours.employee_id FROM hours where year_month>#06/2006#
group by employee_id HAVING sum(regular_hours+nightly_hours)=0)
Why is this query so slow? I have:
16000 rows in HOURS,
2000 rows in employees
10 rows in cooperative
How may I get the same result with a faster query ?
Thank you
Regards