Retrieving several time series datasets in one SQL query

Data and Databases
1

I have a database table with product information that looks like this:

record_date product_code cost end_date
2020-02-15 X 5.0 2020-04-15
2020-02-15 Y 7.5 2020-05-20
2020-02-15 Z 9.2 2020-06-10
2020-02-16 X 5.1 2020-04-15
2020-02-16 Y 7.8 2020-05-20
2020-02-16 Z 9.0 2020-06-10

I need to find products based on specific criteria and then get their complete historical data. For example, I want to find the product that on 2020-02-15 had a cost closest to 5.2 and end date closest to 2020-04-20. Then I need all the time series data for that specific product.

Right now I am doing this in two steps. First I find the product ID with this query:

WITH filtered_data AS (
  SELECT *, 
    ABS(DATEDIFF(day, end_date, '2020-04-20')) AS day_difference,
    ABS(cost - 5.2) AS cost_difference
  FROM products 
  WHERE record_date = '2020-02-15'
  ORDER BY day_difference ASC, cost_difference ASC
)
SELECT TOP 1 product_code FROM filtered_data

Then I use that product code to get all historical records. But I need to do this for multiple products at once. Is there a way to write one SQL query that can handle several target criteria and return all the matching time series data together? This would be much faster than running separate queries for each target.

2

this is super interesting! have you thought about using window functions like ROW_NUMBER() to rank products based on your criteria? then it could help you join back the historical data. what database system are you on? i hear they all have their quirks!

3

You can do this with one query using a CTE to rank products, then join back for the historical data. Use ROW_NUMBER() OVER (PARTITION BY …) to handle multiple scenarios at once. Here’s how: build a CTE that calculates distance metrics and ranks products for each criteria set, then join the ranked results back to your main table on product_code to pull the full time series. Structure your input criteria as a table or VALUES clause with target costs and dates, cross join with your filtered data, then apply the ranking. This cuts out multiple database trips and really speeds things up when you’re working with lots of target criteria.

4

try CROSS APPLY with a table of your target criteria. put all your target costs/dates in a VALUES clause, then cross apply that with your ranking logic. you’ll get one query that finds the best match for each criteria set and pulls the full history. it’s a bit tricky but works well.