0

Suppose I have a table...

Calibrations:

  • Date
  • CalibrationFactor

and another table...

Readings:

  • Date
  • Reading

Readings are made regularly... calibrations (which don't alter subsequent readings, but just figure out how much the readings are off at the time of the calibration) happen infrequently. So, at any given time, a reading needs to be corrected by whatever the most-recent CalibrationFactor is. I don't need this correction to be done by the SELECT statement; I just want them in the same row.

So, I'd like some SELECT which will give me: Readings.Date, Readings.Reading, Calibrations.CalibrationFactor where the CalibrationFactor is the latest one which is still earlier than Readings.Date. I've looked at some of the previous questions, here, but the closest I've found is one which would only find the latest calibration, period (SQL join: selecting the last records in a one-to-many relationship)

Ideas?

Community
  • 1
  • 1
Jemenake
  • 2,092
  • 1
  • 20
  • 16

2 Answers2

1

I think the question you linked gives you pretty much what you need - but if you want another way you can try (mysql version)

select reading.dated, reading.reading, ( 
  select  factor 
  from calibration 
  where calibration.dated < reading.dated
  order by calibration.dated desc limit 1 
) factor
from reading

See this Sql Fiddle

Ian Kenney
  • 6,376
  • 1
  • 25
  • 44
  • Yeah, the question I linked to was pretty much it... I just didn't see, initially, how to keep it from giving me the *overall* latest calibration, and not the most-recent-before-the-reading. Thanks for the sub-select solution, though. – Jemenake Apr 25 '13 at 20:45
0

Okay, I think I figured out the tweak:

SELECT R.Date, R.Reading, C1.CalibrationFactor
FROM Readings R
JOIN Calibrations C1 ON C1.Date < R.Date 
LEFT OUTER JOIN Calibrations C2 ON C2.Date < R.Date AND C1.Date < C2.Date
WHERE C2.Date is NULL

The first JOIN joins Calibrations on all dates which are before the date of the given reading. So, for example (I'll use day names for readability. Assume readings were taken on Monday, Tuesday, and Wednesday):

R.Date | C1.Date
Friday | Monday
Friday | Tuesday
Friday | Wednesday

The second LEFT OUTER JOIN joins Calibrations again, on all dates which are (like the first join) before the date of the reading, but after the date in C1...

R.Date | C1.Date   | C2.Date
Friday | Monday    | Tuesday
Friday | Monday    | Wednesday
Friday | Tuesday   | Wednesday
Friday | Wednesday | NULL

Only in the case in which C2.Date will be null will be when there is no date in C2 which is both after C1.Date and still before the reading, which means that C1 will be the most-recent row... thus, the "WHERE C2.Date IS NULL"

Jemenake
  • 2,092
  • 1
  • 20
  • 16