Issue with the way from date is used in dept_emp table.

[Steve Krall]

I would expect the count(*) from v_full_employees to add up to the sum of the count from show_departments.

mysql> select count(*)
    -> from v_full_employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.20 sec)

mysql> call show_departments();
+---------+--------------------+-------------------+----------+
| dept_no | dept_name | manager | count(*) |
+---------+--------------------+-------------------+----------+
| d001 | Marketing | Vishwani Minakawa | 18428 |
| d002 | Finance | Isamu Legleitner | 15579 |
| d003 | Human Resources | Karsten Sigstam | 16072 |
| d004 | Production | Oscar Ghazalie | 66681 |
| d005 | Development | Leon DasSarma | 76972 |
| d006 | Quality Management | Dung Pesch | 18295 |
| d007 | Sales | Hauke Zhang | 46926 |
| d008 | Research | Hilary Kambil | 19287 |
| d009 | Customer Service | Yuchang Weedman | 21814 |
+---------+--------------------+-------------------+----------+
9 rows in set (8.42 sec)

The total sum of the count from show departments is: 300054

The reason for this is there are 30 records in dept_emp that have two rows with the same from_date.
 SELECT de.emp_no, count(*)
   FROM v_full_departments fd
    INNER JOIN dept_emp de ON (fd.dept_no = de.dept_no AND de.from_date = (SELECT max(from_date)
                                                                              FROM dept_emp de1
                                                                             WHERE de1.emp_no = de.emp_no))
GROUP BY de.emp_no
HAVING count(*) > 1

+--------+----------+
| emp_no | count(*) |
+--------+----------+
| 21076 | 2 |
| 37429 | 2 |
| 44683 | 2 |
| 49509 | 2 |
| 64098 | 2 |
| 69836 | 2 |
| 82648 | 2 |
| 91899 | 2 |
| 109363 | 2 |
| 206466 | 2 |
| 219624 | 2 |
| 228322 | 2 |
| 246188 | 2 |
| 282558 | 2 |
| 285052 | 2 |
| 285338 | 2 |
| 290639 | 2 |
| 401752 | 2 |
| 433010 | 2 |
| 433358 | 2 |
| 435075 | 2 |
| 435183 | 2 |
| 440659 | 2 |
| 452212 | 2 |
| 468620 | 2 |
| 469544 | 2 |
| 475919 | 2 |
| 491049 | 2 |
| 496147 | 2 |
| 499964 | 2 |
+--------+----------+
30 rows in set (3.20 sec)

It seems like the to_date is the date that should be used. Or both the from_date, and the to_date.

Overall, not a huge deal - but it was confusing for me :)

[Giuseppe Maxia (giuseppe-maxia)]

Fixed in revision 4 (version 1.0.6)