Finding correlated values from second table without resorting to PL/SQL

Answer 1

It seems pretty straight forward.

1. Find the sensor values for each range. Find a row - I will call acquired of this row just X - where X > start_date and not exists any other row with acquired > start_date and acquired < X. Do the same for end date.

2. Select only the ranges that meet the query - start_date before and end_date after the dates supplied by the query.

In SQL this would be something like that.

SELECT R1.*, SV1.aquired, SV2.aquired
FROM ranges R1
INNER JOIN sensor_values SV1 ON SV1.sensor_id = R1.sensor_id
INNER JOIN sensor_values SV2 ON SV2.sensor_id = R1.sensor_id  
WHERE SV1.aquired > R1.start_date
AND NOT EXISTS (
    SELECT *
    FROM sensor_values SV3
    WHERE SV3.aquired > R1.start_date
    AND SV3.aquired < SV1.aquired)
AND SV2.aquired > R1.end_date
AND NOT EXISTS (
    SELECT *
    FROM sensor_values SV4
    WHERE SV4.aquired > R1.end_date
    AND SV4.aquired < SV2.aquired)
AND R1.start_date < @range_start
AND R1.end_date > @range_end

Answer 2

You can give this a try. Note the caveats at the end though.

SELECT
    RNG.sensor_id,
    RNG.start_date,
    RDG1.value AS v1,
    RNG.end_date,
    RDG2.value AS v2,
    RNG.description
FROM
    Ranges RNG
INNER JOIN Readings RDG1 ON
    RDG1.sensor_id = RNG.sensor_id AND
    RDG1.acquired => RNG.start_date
LEFT OUTER JOIN Readings RDG1_NE ON
    RDG1_NE.sensor_id = RDG1.sensor_id AND
    RDG1_NE.acquired >= RNG.start_date AND
    RDG1_NE.acquired < RDG1.acquired
INNER JOIN Readings RDG2 ON
    RDG2.sensor_id = RNG.sensor_id AND
    RDG2.acquired => RNG.end_date
LEFT OUTER JOIN Readings RDG1_NE ON
    RDG2_NE.sensor_id = RDG2.sensor_id AND
    RDG2_NE.acquired >= RNG.end_date AND
    RDG2_NE.acquired < RDG2.acquired
WHERE
    RDG1_NE.sensor_id IS NULL AND
    RDG2_NE.sensor_id IS NULL

This uses the first reading after the start date of the range and the first reading after the end date (personally, I'd think using the last date before the start and end would make more sense or the closest value, but I don't know your application). If there is no such reading then you won't get anything at all. You can change the INNER JOINs to OUTER and put additional logic in to handle those situations based on your own business rules.