# Calculate difference of values for records on outer ends of timeframe

• May 21, 2013, 05:38
Hieronymus
Calculate difference of values for records on outer ends of timeframe
Hy there,

I'm working on a problem I can't seem te solve in MySQL.

I have a table with snapshots of cummulative values. The information I need is the difference of those values within a timeframe.
At the moment I use PHP to calculate thoses difference because I can't seem te get the min and max date from the table AND the values corresponding to those records.

Code:

```SELECT fms1.* FROM fms1 WHERE AND fms1.gps_unit_id = 114 AND fms1.created >= "2013-03-01 00:00:00" ORDER BY fms1.created ASC LIMIT 1 ) UNION ( SELECT fms1.* FROM fms1     WHERE fms1.gps_unit_id = 114     AND fms1.created >= "2013-03-01 00:00:00"     AND fms1.created <= "2013-03-01 23:59:59"     ORDER BY fms1.created DESC LIMIT 1 )```
This way I get two rows returned for which I calculate the difference for each of those values. (say fms1.odometer, fms1.idle_time and so on)

I tried something like this:

Code:

```SELECT     fms1.*,     max(fms1.created) AS MaxDate,     min(fms1.created) AS MinDate FROM fms1 WHERE fms1.created BETWEEN "2013-03-01 00:00:00" AND "2013-03-01 23:59:59" AND fms1.gps_unit_id = 114```
This does return the correct MinDate and MaxDate for the records on that day (over a 100), but it only returns the complete record for the MinDate. Which in this case is the correct one. You cannot sort the records by id, because causality is not guarantied.

The thing I can't get my head around is how to select complete records based on the outcome of funtions like min() and max(). For instance if you use
Code:

```SELECT     max(fms1.created) AS MaxDate,     min(fms1.created) AS MinDate FROM fms1 WHERE fms1.created BETWEEN "2013-03-01 00:00:00" AND "2013-03-01 23:59:59" AND fms1.gps_unit_id = 114```
I do get the correct dates, but how to get the records belonging to those values? You can't use min() and max() in subqueries...

Many thanks!
• May 21, 2013, 11:41
Hieronymus
I solved it!

At least for the most part:
Code:

```SELECT SQL_NO_CACHE     (b.odometer - a.odometer) AS diff_odo FROM fms1 AS a, (     SELECT         max(fms1.created) AS MaxDate,         min(fms1.created) AS MinDate     FROM fms1     WHERE fms1.created BETWEEN  "2013-03-01 00:00:00" AND "2013-03-01 23:59:59" AND fms1.gps_unit_id = 114 ) AS t INNER JOIN fms1 AS b ON b.created = t.MaxDate AND b.gps_unit_id = 114 WHERE (a.created = t.MinDate) AND a.gps_unit_id = 114```
Giving everything an alias and joining the table with itself gives you a way to do this. With an index on both date and gps_unit_id it is also superfast.

The only thing I still can't figure out is how to select the id's from the rows in the subquery. So I don't want to find the value of the max and min date, but the Id belonging to that record. That would remove the need to check on gps_unit_id in the join and where clause of the outher query (and a nice trick to have up your sleeve anyway)