Price calculations help

Hello,

I run into a problem, and i cant find a good solution. I could use some advice 🙂

My problem:

I have a price table that contains gross prices, productid, start date, end date(nullable) . Its a historic table.

ID  productid       start            end        gross
1    1                2022.06.01 2022.06.04    100 

2    1                2022.06.05       null          200 

3    1                2022.06.07       null          240  

4    1                2022.06.07       null          300 

i have a date slicer and i need to know the current price based on that slicer. (I have a dedicated measure table, for measures.)

2022.06.02 --> 100

2022.06.05 --> 200 

2022.06.07 --> 300 (if it start at the same time table id decide the price)

The SQL:

SELECT ProductId, Net, Gross FROM (SELECT ProductId, Net, Gross, ROW_NUMBER() OVER(PARTITION BY PriceItems.ProductId ORDER BY PriceHeaders.PriceHeaderId DESC) nr FROM PriceItems INNER JOIN PriceHeaderRows ON PriceHeaderRows.PriceHeaderRowId = PriceItems.PriceHeaderRowId INNER JOIN PriceHeaders ON PriceHeaders.PriceHeaderId = PriceHeaderRows.PriceHeaderId WHERE PriceHeaders.Status = 0 AND PriceHeaderRows.PriceRowId = @PriceRowId AND PriceHeaders.Start <= @Date AND (PriceHeaders.[End] IS NULL OR PriceHeaders.[End] > @Date)) AS Prices WHERE Prices.nr = 1