CODEWITHSUNDEEP
mysqljoin
webmonger
webmonger

Reputation: 41

MySQL join conditional between dates

I need to do a MySQL join that combines temperature measurements per day and the maximum temperature for that day.

I have two tables: temperatures (containing the measurements) and max_temperatures (containing the maximum temperature for a period of time). 

table temperatures
================
id (int)
temperature (decimal)
measure_date (date)

table thresholds
================
id (int)
max_temperature (decimal)
startdate (date)

Say, I have two records in table thresholds:

1 | 15 | 2014-12-31
2 | 14 | 2015-01-04

Is there any way to get a recordset like this?

measure_date | temperature | max_temperature
--------------------------------------------
2015-01-01   | 12          | 15
2015-01-02   | 11          | 15
2015-01-03   | 12          | 15
2015-01-04   | 14          | 14
2015-01-05   | 16          | 14
2015-01-06   | 13          | 14

Unfortunately, the thresholds table does not hold enddates, or I could do a join where temperatures.measure_date is between thresholds.startdate and thresholds.enddate.

Upvotes: 2

Views: 91

Answers (2)

lp_
lp_

Reputation: 1178

This is a little bit different approach than others, but the result should be the same, so I still post it, mostly because this is the closest to the way that you explain it yourself.

You can get the startdate-enddate that you wish you'd have in your question this way:

SELECT *, (SELECT MIN(startdate)
    FROM thresholds t1
    WHERE t1.startdate>t.startdate) AS enddate
FROM thresholds t

id  max_temperature startdate   enddate
1   15              2014-12-31  2015-01-04
2   14              2015-01-04  NULL

Once you have that, you can join it with temperatures just like you write it:

SELECT measure_date, temperature, max_temperature
FROM temperatures temps
LEFT JOIN (SELECT *, (SELECT MIN(startdate)
        FROM thresholds t1
        WHERE t1.startdate>t.startdate) AS enddate
    FROM thresholds t) tt
ON temps.measure_date >= tt.startdate
   AND (temps.measure_date<enddate OR enddate IS NULL);

Upvotes: 0

Jean-Fran&#231;ois Savard
Jean-Fran&#231;ois Savard

Reputation: 21004

Since you don't have the end date, you will have to find yourself the maximum ending value using a subquery... Here how I solved it :

select b.measure_date, b.temperature, a.max_temperature
        from thresholds a
        inner join temperatures b on b.measure_date >= a.startdate
        where a.startdate = (select max(startdate)
                                from thresholds a2
                                where a2.startdate <= b.measure_date);

Basically what I do here is :

  • Join the both table with condition that the measure_date is higher or equals than the startdate.
  • Make sure the startdate is the highest possible as we don't have the end date.

Upvotes: 3

Related Questions