Am trying to get a list of all vacation time used by each employee
since 1-1-2002. I can do it with 2 separate queries. The first looks
like...
SELECT e.employee_pk, e.first_name, e.last_name,
a.rate,a.effective_date,
((((YEAR(CURRENT_DATE)-YEAR(effective_date))*12))+(MONTH(CURRENT_DATE)-MONTH(effective_date)))
AS month_count, ((((YEAR(CURRENT_DATE)-2002)*12))+(MONTH(CURRENT_DATE)-1))
AS max_count FROM employee e, hr_vacation_accrual a WHERE
e.employee_pk = a.employee_uid AND e.active=1 AND exempt_status != 'H'
AND exempt_status != 'C' ORDER BY e.last_name, a.effective_date
What the above is doing is getting the employee's name, their accrual
rate (we accrue at various numbers of hours per month based on
seniority), the date the new seniority kicks in, and the number of
months at this rate, as well as how many months there are since
1-1-2002.
It may return something like...
John Smith, 8hours/month, starting 1-1-1999
John Smith, 10hours/month, starting 1-1-2002
John Smith, 13.33hours/month, starting 5-1-2003
Jane Smith.... etc.
I'd like to add to this a hit to the time table to get the sum of
vacation hours that have been taken by the employee. It's ok if it
displays the same sum multiple times.
Here's the query I'd like to work into the above...
select sum(hours) from hr_timesheet
where employee_uid = (the employee_pk above)
and category='VAC'
What I am doing now is running the first query, and then while it
loops, I run the second query over and over for each loop. This is far
from efficient.
Any suggestions would be greatly appreciated.
Thanks,
Don
NOTE: We are not using MySQL 4.1, so I can't do sub-selects.
