@Patrick wrote:
@mkeintz Appears to be a "duplicate" question with an accepted solution here
True.
The code I offered above takes advantage of the fact that the data are sorted, which AFAICT, SQL does not. There is likely to be a significant performance benefit.
It uses the same technique as the "carry the future back" section in a presentation I gave at PharmaSUG 2019 (History Carried Forward, Future Carried Back: Mixing Time Series of Differing Frequencies), except it mixes a subset of a series with the whole series.
One of the reasons I have come to promote this technique is that it is fairly simple to expand mixing of two series to three or more series, which I think can become rather messy in SQL. Applying it to finding the "closest" value only explicitly occurred to me when contemplating this OP's post.
Plug: I'll be presenting content from the PharmaSUG paper at next month's Boston Area SAS User's group. And I hope to include an example of "finding the closest" as well.
... View more