design – Finding/determining future data for pricing

I’m honestly not even sure how to go about how to ask what I’m looking for/need. lol.

I’m currently using Sql Server for my database and the ‘ 5. There are multiple pricing tables that each have their own purpose for pricing lineitems on an order. These pricings get updated quite often, ranging from either monthly to once a year.

In the system they’re currently using(super old), when a new pricing system comes out they create an entirely new table with those values, everytime. They do this because when an order is placed, it needs to get what the price would be on it’s due date.

For example:

‘orderA’ is placed December 20th with a due date of December 30th. ‘orderA’ uses the current pricing data that’s in effect.

‘orderB’ is placed on the same day, December 20th, but it has a due date of Jan 1st. ‘orderB’ would use the future data for pricing.

The example above is why they currently just create an entirely new table every time any pricing gets updated. I obviously do not want to do that, for a multitude of reasons.

I currently have functionality in place where users can schedule updates to different tables. This is basically just a stored procedure that creates a Sql Agent Job that gets triggered to execute an update query on a specific date.

Now this handles the ability to schedule updates, but it does not handle the ability to view if or what future data may be, which is the problem I’m running into.

I’ve looked into time-series data a bit, but I’m not entirely sure if that’s the best course of action.

I’m also not entirely sure what kind of information can be pulled from a specific scheduled job.
I feel that if I am able to get the jobs command, I’d need to essentially execute that query against current data without actually updating it to get the future data values or parse the query into a usable format I can use. Plus doing this, would probably cause big performance issues as it’d need to get ran multiple times for each line item in an order, depending on various pricing conditions.

So, what is the best course of action I should take? Whether it’s something I have mentioned or there’s something I haven’t mentioned, please let me know.