Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to store and analyze employee's work time.

Currently, I've developed this table structure

CREATE TABLE `hirefire` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `employeeid` int(10) unsigned NOT NULL,
  `hired` date NOT NULL,
  `fired` date DEFAULT NULL,
  `firereason` enum('fired','left','parental','vacation','death') COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `hired` (`hired`),
  KEY `fired` (`fired`),
  KEY `employee` (`employeeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

The fields employeeid, hired, and fired are self-explanatory (fired may be NULL when a person was hired but not yet fired).

firereason:

  • 'fired' - forced to be fired by the boss
  • 'left' - left job by his own will
  • 'parental' - parental leave
  • 'vacation' - his non-paid vacation
  • 'death' - he or she died

This my table scheme may be (I think) good to check whether a given employee is listed as working at least a day in a given month (BTW, an SQL code for this would be appreciated),

But now a complexity comes out:

We will also need to be able to count employee's total working years. Note that parental leave and non-paid vacations should be not subtracted from the working years. This time my database structure goes non convenient for this kind of calculation.

Which DB structure would you suggest to use? (Is my (above) variant is the best structure for these tasks, or can it be improved?)

What's about allocating a whole DB row for every hire/fire event (that is having a separate row for hire data and fire date not one the same row for paired fire/hire)? For which tasks this would be better and for which worse?

share|improve this question

1 Answer 1

Perhaps naming the columns "hired" and "fired" instead "start_date","end_date" would be more accurate, because a person in vacation hasn't been fired, and indeed is still employed. Same with "firereason", which is misleading, a simple "reason" would be more accurate.

SELECT employeeid,       
       SUM(      
           DATEDIFF(
             IF(end_date IS NULL,CURRENT_DATE,end_date),
                start_date))/365 AS "Years worked" 
FROM hirefire 
WHERE (firereason IS NULL 
    OR firereason = 'death' 
    OR firereason = 'left'
    OR firereason = 'fired') 
GROUP BY employeeid;

For example:

mysql> select * from hirefire;
+----+------------+------------+------------+------------+
| id | employeeid | start_date | end_date   | firereason |
+----+------------+------------+------------+------------+
|  1 |       1234 | 2013-01-01 | 2013-08-01 | death      |
|  2 |       1234 | 2013-02-01 | 2013-06-01 | parental   |
|  3 |       2345 | 2012-07-01 | NULL       | NULL       |
|  4 |       2345 | 2012-08-01 | 2012-09-01 | vacation   |
|  5 |       3456 | 2009-01-01 | NULL       | NULL       |
+----+------------+------------+------------+------------+

gives the result:

+------------+--------------+
| employeeid | Years worked |
+------------+--------------+
|       1234 |       0.5808 |
|       2345 |       1.0904 |
|       3456 |       4.5890 |
+------------+--------------+

Dividing by 365 is slightly incorrect, because it doesn't take the leap years into account.

Another problem with this answer is, that overlapping hire/fire periods are ignored. For example, if an employee was employed at 50% time from Aug to Dec, and additionally at 30% time just in Dec, the query would fail - but you don't take percentage into account anyway, so I assumed that the employment times don't overlap. This has been confirmed by the original poster.

For more date functions, review the documentation: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

The different date/time functions will also help you answer your other SQL question.

share|improve this answer
    
Your SQL statement is wrong. Consider for example one hired 2013-01-01, gone parental 2013-02-01, returned 2013-06-01 and worked until his death in 2013-08-01 –  porton Aug 2 '13 at 11:26
    
@porton The SQL statement was indeed wrong, as I needed to group by employee id instead of the incrementing id column. I corrected it in the table. Otherwise, I don't understand why it wouldn't work, added a specific example to the answer. Could you please be more specific? –  Ursula Aug 2 '13 at 17:58
    
OK, suppose the table has only one row which is with parental leave (with returning date of the employee not yet known). Then your SQL code will count it like zero (actually NULL) time of work, what is very wrong –  porton Aug 3 '13 at 11:46
    
Overlapping fire/hire should not happen –  porton Aug 3 '13 at 11:55
    
Your scenario about the parental leave is not defined correctly - if the employee is currently on parental leave, that employee has to have been hired before, thus there should be two rows. Additionally, you asked that the parental and vacation entries should get ignored. I revised my answer to add a couple of employees that are still employed, and set the end_date for the sake of calculation to the current date. –  Ursula Aug 3 '13 at 20:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.