CODEWITHSUNDEEP
mysqldatejoin
FlyingMolga
FlyingMolga

Reputation: 1616

MySQL find minimum and maximum date associated with a record in another table

I am trying to write a query to find the number of miles on a bicycle fork. This number is calculated by taking the distance_reading associated with the date that the fork was installed on (the minimum reading_date on or after the Bicycle_Fork.start_date associated with the Bicycle_Fork record) and subtracting that from the date that the fork was removed (the maximum reading_date on or before the Bicycle_Fork.end_date or, if that is null, the reading closest to today's date). I've managed to restrict the range of odometer_readings to the appropriate ones, but I cannot figure out how to find the minimum and maximum date for each odometer that represents when the fork was installed. It was easy when I only had to look at records matching the start_date or end_date, but the user is not required to enter a new odometer reading for each date that a part is changed. I've been working on this query for several hours now, and I can't find a way to use MIN() that doesn't just take the single smallest date out of all of the results.

Question: How can I find the minimum reading_date and the maximum reading_date associated with each odometer_id while maintaining the restrictions created by my WHERE clause?

If this is not possible, I plan to store the values retrieved from the first query in an array in PHP and deal with it from there, but I would like to be able to find a solution solely in MySQL.

Here is an SQL fiddle with the database schema and the current state of the query: http://sqlfiddle.com/#!2/015642/1

SELECT OdometerReadings.distance_reading, OdometerReadings.reading_date,
OdometerReadings.odometer_id, Bicycle_Fork.fork_id
    FROM Bicycle_Fork
    INNER JOIN (Bicycles, Odometers, OdometerReadings)
    ON (Bicycles.bicycle_id = Bicycle_Fork.bicycle_id
    AND Odometers.bicycle_id = Bicycles.bicycle_id AND OdometerReadings.odometer_id = Odometers.odometer_id)
    WHERE (OdometerReadings.reading_date >= Bicycle_Fork.start_date) AND
    ((Bicycle_Fork.end_date IS NOT NULL AND OdometerReadings.reading_date<= Bicycle_Fork.end_date) XOR (Bicycle_Fork.end_date IS NULL AND OdometerReadings.reading_date <= CURRENT_DATE()))

This is the old query that didn't take into account the possibility of the database lacking a record that corresponded with the start_date or end_date:

SELECT MaxReadingOdo.distance_reading, MinReadingOdo.distance_reading
FROM
(SELECT OdometerReadings.distance_reading, OdometerReadings.reading_date,
OdometerReadings.odometer_id 
    FROM Bicycle_Fork
    LEFT JOIN (Bicycles, Odometers, OdometerReadings)
    ON (Bicycles.bicycle_id = Bicycle_Fork.bicycle_id
    AND Odometers.bicycle_id = Bicycles.bicycle_id AND OdometerReadings.odometer_id = Odometers.odometer_id)
    WHERE Bicycle_Fork.start_date = OdometerReadings.reading_date) AS MinReadingOdo
INNER JOIN
    (SELECT OdometerReadings.distance_reading, OdometerReadings.reading_date, 
    OdometerReadings.odometer_id 
    FROM Bicycle_Fork
    LEFT JOIN (Bicycles, Odometers, OdometerReadings)
    ON (Bicycles.bicycle_id = Bicycle_Fork.bicycle_id AND Odometers.bicycle_id 
    = Bicycles.bicycle_id AND OdometerReadings.odometer_id = Odometers.odometer_id)
    WHERE Bicycle_Fork.end_date = OdometerReadings.reading_date) AS 
    MaxReadingOdo
ON MinReadingOdo.odometer_id = MaxReadingOdo.odometer_id

I'm trying to get the following to return from the SQL schema: I will eventually sum these into one number, but I've been working with them separately to make it easier to check the values.

min_distance_reading    | max_distance_reading  | odometer_id
=============================================================
75.5                    | 2580.5                | 1
510.5                   | 4078.5                | 2
17.5                    | 78.5                  | 3

Upvotes: 0

Views: 88

Answers (1)

Strawberry
Strawberry

Reputation: 33945

I don't understand the final part of the puzzle, but this seems close...

SELECT MIN(ro.distance_reading) min_val
     , MAX(ro.distance_reading) max_val
     , ro.odometer_id 
  FROM OdometerReadings ro
  JOIN odometers o
    ON o.odometer_id = ro.odometer_id
  JOIN Bicycle_Fork bf
    ON bf.bicycle_id = o.bicycle_id
   AND bf.start_date <= ro.reading_date
 GROUP
    BY ro.odometer_id;

http://sqlfiddle.com/#!2/015642/8

Upvotes: 2

Related Questions