Please review query for efficiency / best practices

[jalinidy]

I'm hoping someone more experienced than I can review the following and let me know if there is better / more efficient query I

could be running to get the same results.  Basically I just want some re-assurance that I'm going about this right! 

 

From the 3 tables below I'm using the following query to pull the last period of employment (not current, i.e., termdate

is not null) from hire_history) and last rate paid (from rate_history) for that period of employement for each employee

in the employee's table.

 

select *
from (
select e.ee_id, e.firstname, e.lastname, h.dept_id, h.jobtitle_id, h.hiredate, h.termdate, r.rate, SubQ.date as EffDate
from 
(
select hh.ee_id, rh.hire_id, max(rh.id) as id, max(rh.effectivedate) date 
from rate_history rh 
join hire_history hh on rh.hire_id = hh.id 
where hh.termdate is not null 
and hh.dept_id in ('2')
and hh.clock_id in ('1')
group by rh.hire_id
) SubQ
join rate_history r on SubQ.id = r.id AND SubQ.date = r.effectivedate
join hire_history h on SubQ.hire_id = h.id
join employees e on h.ee_id = e.ee_id
group by r.id
order by e.lastname, e.firstname
) Q
group by ee_id;

 

 

[pre]

EMPLOYEES TABLE

+-------+-----------+----------+

| ee_id | firstname | lastname |

+-------+-----------+----------+

|    1 | J.        | Smith    |

|    13 | Patricia  | Jones    |

+-------+-----------+----------+

 

 

HIRE_HISTORY TABLE

+----+-------+------------+------------+---------+-------------+

| id | ee_id | hiredate  | termdate  | dept_id | jobtitle_id |

+----+-------+------------+------------+---------+-------------+

|  1 |    1 | 2004-10-25 | 2007-04-13 |      2 |          5 |

| 11 |    1 | 2007-04-16 | 2007-06-08 |      2 |          9 |

| 12 |    1 | 2007-06-11 | 2009-07-16 |      4 |          25 |

| 13 |    1 | 2009-07-17 | NULL      |      2 |          5 |

| 14 |    13 | 2008-05-05 | 2012-02-14 |      2 |          9 |

+----+-------+------------+------------+---------+-------------+

 

 

RATE_HISTORY TABLE

+----+---------+---------+---------------+

| id | hire_id | rate    | effectivedate |

+----+---------+---------+---------------+

|  1 |      1 | 16.0000 | 2004-10-25    |

|  2 |      1 | 16.9700 | 2006-01-02    |

|  3 |      1 | 17.6500 | 2007-01-01    |

|  4 |      11 | 19.2400 | 2007-04-16    |

|  5 |      12 | 17.3100 | 2007-06-11    |

|  6 |      12 | 17.6600 | 2007-12-31    |

|  7 |      13 | 20.1923 | 2009-07-19    |

|  8 |      13 | 21.1600 | 2010-03-08    |

|  9 |      13 | 21.1800 | 2011-05-16    |

+----+---------+---------+---------------+

 

 

RESULT

+-------+-----------+----------+---------+-------------+------------+------------+---------+------------+

| ee_id | firstname | lastname | dept_id | jobtitle_id | hiredate  | termdate  | rate    | EffDate    |

+-------+-----------+----------+---------+-------------+------------+------------+---------+------------+

|    1 | J.        | Smith    |      2 |          9 | 2007-04-16 | 2007-06-08 | 19.2400 | 2007-04-16 |

|    13 | Patricia  | Jones    |      2 |          9 | 2008-05-05 | 2012-02-14 | 18.2800 | 2011-05-16 |

+-------+-----------+----------+---------+-------------+------------+------------+---------+------------+

[/pre]

 

Thanks in advance.  Please let me know if I've been unclear or need to provide further information.

 

Adam

[jalinidy]

Hello ... I see a number of people have viewed this post but so far no replies.  If there's any other information I need to provide, please let me know.  I did see that MySQL version is required information in posts:

 

5.0.22-Debian_0ubuntu6.06.10-log

 

Even if the reply is "your code's a mess!" - that's fair. 

 

In any event, thanks for looking.

 

Best,

Adam

[sunfighter]

I don't think you'll get an answer from me, but I did make the database and ran your code. It didn't run. Got this message back

"Unknown column 'hh.clock_id' in 'where clause'"

[jalinidy]

sunfighter, thank you.  I was trying to supply only *relevant* information in my post and in so doing I now see I posted a bad query (as you pointed out) as well as bad sample data in the tables (the rate_history table doesn't list the rate data for hire_history id 14, e.g., and yet it's in the result).

 

I thought I was being careful, but I guess not!

 

When I have more time tomorrow I'll try to repost.

 

Thanks, as always, to all for reading ... and sorry for not proof-reading my example more closely!

 

Adam

[Muddy_Funster]

erm...your code's a mess (or your structure is a mess, either way, somethings not right).

 

what you have stated as your desired result set should be doable in a fraction of that code.  I don't have the time to review it properly just now, but will have tomorrow.

[Barand]

You need

the latest termdate for each employee

the latest effective date for each hire

 

so these are the subqueries we need

 

(Note from the data you supplied, emp 1 is the only emp with matching hire and rate histories)

 

SELECT e.ee_id, e.firstname, e.lastname, h.dept_id, h.jobtitle_id,
  h.hiredate, h.termdate, r.rate, r.effectivedate
FROM employees e
INNER JOIN hire_history h ON e.ee_id = h.ee_id
INNER JOIN rate_history r ON h.id = r.hire_id
INNER JOIN
  (
    SELECT ee_id, MAX(termdate) as termdate
    FROM hire_history
    GROUP BY ee_id
  ) xh
  ON h.ee_id = xh.ee_id AND h.termdate = xh.termdate
INNER JOIN
  (
    SELECT hire_id, MAX(effectivedate) as edate
    FROM rate_history
    GROUP BY hire_id
  ) xr
  ON r.hire_id = xr.hire_id AND r.effectivedate = xr.edate
ORDER BY e.lastname

 

Results:

+-------+-----------+----------+---------+-------------+------------+------------+---------+---------------+
| ee_id | firstname | lastname | dept_id | jobtitle_id | hiredate   | termdate   | rate    | effectivedate |
+-------+-----------+----------+---------+-------------+------------+------------+---------+---------------+
|     1 | J.        | Smith    |       4 |          25 | 2007-06-11 | 2009-07-16 | 17.6600 | 2007-12-31    |
+-------+-----------+----------+---------+-------------+------------+------------+---------+---------------+