I am trying to create a daily point-in-time table by expanding a smaller table via a join with a Range table. The Range table defines the dates for which I want to create data. The metric table holds periodically persisted data. The tables look like the following:

Range

Metric

The Dates table should define the date range by which to build the Output table. I want to join the tables on Range.date greater than Metric.date so the output looks like the following.

Output

I have attempted a few different versions of this without success. Here's what I have thus far:

CREATE TABLE OUTPUT
SELECT * FROM [db].Range AS d LEFT JOIN [db].Metric AS m ON d.date > 
(SELECT m.date 
         FROM [db].Metric AS m2
         WHERE m2.date < d.date
         ORDER BY m2.date ASC
         LIMIT 1
);

The above returns rows for every date, metric combination where the metric is greater than the date. I could see how an outer GROUP BY date might solve this issue but it seems ugly and the performance will be pretty awful. Is there an easier method?

Thanks in advance :)

CodePudding user response：

Seems like you want to join on closest date that's smaller. Have you tried:

Select Range.date, A, B
from Range inner join Metric
on Metric.date = (select max(date) from Metric where date < Range.date);