Calculate the total price of changes between different dates in a SQL Server result set

Question

I have a SQL Server table which holds records for changes in fuel prices. When the fuel price changes and new record is added to the table with the changed date and price. I need to calculate the final total of fuel price between two dates. In this example I want to calculate the total of price between 01/12/2014 and 31/12/2014.

Fuel_Price | Change_Date
------------------------
16.50      | 01-01-2014 
17.50      | 05-12-2014 
17.25      | 15-12-2014 
16.00      | 27-12-2014

Edit:

Total price = 16.50 * 5 (5-12-2014 - 1-12-2014) + 17.50 * 10 + 16 * 12 = 449,5

What do you mean by 'final total'? Can you provide the desired output given your data? — venerik, Dec 16 '14 at 15:21
Do you mean "If I buy fuel once every day in December, how much will I spend in total?" — Rhumborl, Dec 16 '14 at 15:25
For example I have fuel price as 16.50 until 05/12/2014, so from my start date which is 01/12/2014 until 05/12/2014 the total price for the days would be 16.5*5. Likewise I need to find the total between 01/12/2014 and 31/12/2014, hope I was able to explain. — Hamza Zahir, Dec 16 '14 at 15:25
You are asking for an **average** of the price for the whole month? Note that the initial price says `01-01` instead of `01-12` (I guess that's what you meant). — Andrew, Dec 16 '14 at 15:28

faby · Answer 1 · 2014-12-16T15:44:31.840

0

This is not tested but your query should be something similar to this

    SELECT SUM(
           [current].Fuel_Price *
           DATEDIFF ( DAY, [next].Change_Date, [current].Value ))
        FROM
           MyTable       AS [current]
        LEFT JOIN
           MyTable AS [next]
          ON [next].Change_Date= (SELECT MIN(Change_Date) FROM MyTable WHERE Change_Date> [current].Change_Date)
WHERE [current].Change_Date BETWEEN DATE1 AND DATE2

this query if I have understood will return the sum of the prices between 2 dates (calculated day by day)

edited Dec 16 '14 at 15:44

answered Dec 16 '14 at 15:39

faby

7,394
3
27
44

Thanks I will test it and let you know, I would need another query if not possible in this query to find the current price which will be basically the price of the nearest change prior to the start date, in this case the current price should be 16.50 because it was the price of the nearest date to the start date – Hamza Zahir Dec 16 '14 at 15:50

score 0 · Answer 2 · edited May 23 '17 at 11:50

I'm certain this isn't the most efficient way to do it, but it seems to do the trick. It uses 3 steps to get there:

Use a CTE to build a list of all dates in the range, using the idea from How to generate a range of dates in SQL Server.
Join the date list to your table, but only match dates in the past,. Use ROW_NUMBER() so that the latest change is first for each date.
Pick out only the latest change, where row_number() = 1 and sum them

DECLARE @start DATE, @end DATE;
SELECT @start = '2014-12-01', @end = '2014-12-31';

;WITH dates AS 
(
  SELECT TOP (DATEDIFF(DAY, @start, @end) + 1) 
    theDate = dateadd(day, ROW_NUMBER() OVER (ORDER BY [object_id]) - 1, @start)
  FROM sys.all_objects
),
allPrices as (
    select d.theDate, fp.Fuel_Price, ROW_NUMBER() OVER (partition by theDate ORDER BY fp.Change_Date desc) rn
    from dates d
    inner join dbo.FuelPrices fp on d.theDate > fp.Change_Date
)

select sum(Fuel_Price) from allPrices where rn = 1

This gave me 528.50.

You can do also select * from allPrices where rn = 1 at the end instead if you want to check the daily price.

Calculate the total price of changes between different dates in a SQL Server result set

2 Answers2