MySQL – Select when last entry before timestamp has specific value

There is a manual page on Time and Date functions.

The best way to compare date and time and get minutes back is probably to use one of the following:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60
SELECT TIMESTAMPDIFF(MINUTE,timeb,timea)

Note that while TIMEDIFF works with times, TIMESTAMPDIFF requires a date component - and the values are switched around for TIMESTAMPDIFF.

You can return hours with either of these:

SELECT TIME_TO_SEC(TIMEDIFF(timea,timeb)+0)/60/60
SELECT TIMESTAMPDIFF(HOUR,timeb,timea)

Note that when using DATEDIFF, the value returned is a difference between the date components of the given time - thus, a value of 23:59 on one day compared to a value of 0:01 on the next (a 2 minute difference) results in a date difference of 1 (a full day).

To get a difference between two times in 24-hour periods (length of a day) first calculate the difference in minutes then divide by the proper values to find the number of 24-hour periods. Try this:

SELECT TRUNCATE(TIMESTAMPDIFF(MINUTE,timeb,timea)/60.0/24.0,0)

This truncates the decimal portion, returning only the number of complete 24-hour periods in the specified timespan.

Allow me to start with a review of your table design decisions (or of those that preceded you) that directly led you to this problem, and how to avoid it. If you do not want to read it, go directly to the end, where I suggest on an edit the actual solution.

You have a single table that is crammed by several design entities. While this is not bad per se, you must be aware that you are denormalizing your table design, and that can lead to problems.

I see 3 concepts on the same table:

• Employee intrinsic details (First name, Family name)
• Department membership
• Job position holding by the employee within the department

According to your description, an employee can be part of several departments (and vice-versa) and you are implementing that as a bit field. I find interesting that you have chosen a binary data type, when a bit or a set data types would be potentially smaller and more legible respectively. However, those data types are usually avoided, not only because they are not very standard, but because they are used to denormalize columns.

Assuming there is a departments table, a better option would be to create a separte table: DepartmentEmployees, like this

Employees Table
===============
EmployeeId Int Primary Key auto_increment
Firstname  Varchar(40)
Surname    Varchar(40)
JobCode    Char(1)

DepartmentEmployees
===================
EmployeeId Int
DepartmentId Int
Primary Key(EmployeeID, DepartmentId)

To query all employees from given deparment "?":

SELECT E.*
FROM Employees E
JOIN DepartmentEmployees DE
ON E.Id = DE.EmployeeID
WHERE DepartmentId = ?

Now let's have a look at JobCode. You have to ask yourself if a Job Position is a property of the employee or of the employee within a department. If the latter, please consider adding that property there:

Employees Table
===============
EmployeeId Int Primary Key auto_increment
Firstname  Varchar(40)
Surname    Varchar(40)

DepartmentEmployees
===================
EmployeeId Int
DepartmentId Int
JobCode Char(1)
Primary Key(EmployeeID, DepartmentId)

To query all employees from given deparment "?", with its Job Code:

SELECT E.*, DE.JobCode
FROM Employees E
JOIN DepartmentEmployees DE
ON E.Id = DE.EmployeeID
WHERE DepartmentId = ?

This way you can get the JobCode, which can be different for each employee-department tuple (if needed).

You can consider creating (if they do not exist already), separate tables for Department, Jobs (as in, positions, for JobCode) and add unique Primary key everywhere.

In general, creating an intermediate table whenever there is an N:N relationship is a good option, unless there is a reason to denormalize the relationship (leading to more complex code).

Edit: The problem you are facing is horrible from all points- aside from the normalization problems, adding one extra record makes no sense. But here is the wanted solution:

Knowing there is nothing you can do about it, you are lucky because it is a very common query- that not only has a name "GROUPWISE MAXIMUM", it even has its own page on the MySQL manual: http://dev.mysql.com/doc/refman/5.7/en/example-maximum-column-group-row.html

There is both a subquery and a JOIN solution, I usually like the JOIN solution more, it would go something like this:

SELECT E1.FirstName, E1.Surname, E1.Department, s1.JobCode
FROM Employees E1
LEFT JOIN Employees E2
ON E1.FirstName = E2.FirstName AND E1.Surname = E2.Surname -- use an id for the SELF JOIN if possible, whatever is unique
AND E1.JobCode < E2.JobCode -- change the direction of the comparison depending if you want the lowest or the highest JobCode
WHERE E2.FirstName IS NULL  -- again, use the id if available
  AND E1.Department = ...;  -- or whatever the department filter should look like in your current setup

Allow me to shower after writing such a solution.