Search code examples
sqlsql-serverwindow-functions

Query to return the lowest SUM of values over X consecutive days

I'm not even sure how to word this one!...

I have a table with two columns, Price (double) and StartDate (Date). I need to be able to query the table and return X number of rows, lets say 3 for this example - I need to pull back the 3 rows that have consecutive dates e.g. 7th, 8th, 9th of May 2019 which have the lowest sum'd price values from a date range.

I'm thinking a function which takes startDateRange, endDateRange, duration.

It'll return a number of rows (duration) between startDateRange and endDateRange and those three rows when sum'd up would be the cheapest (lowest) sum of any number of rows within that date range for consecutive dates.

So as an example, if I wanted the cheapest 3 dates from between 1st May 2019 and 14th May 2019, the highlighted 3 rows would be returned;

enter image description here

I think possibly LEAD() and LAG() might be a starting point, but I'm not really a SQL person, so not sure if there's a better way around this.

I've developed some c# on my business layer to do this currently, but over large datasets its a bit sluggish - it would be nice to get a list of records straight from my data layer.

Any ideas would be greatly appreciated!

Thanks in advance.

Solution

  • You can calculate averages over 3 days with a window function. Then use top 1 to pick the set of 3 rows with the lowest average:

    select  top 1 StartDt
,       AvgPrice
from    (
        select  StartDt
        ,       avg(Price) over (order by StartDt rows between 2 preceding 
                                 and current row) AvgPrice
        ,       count(*) over (order by StartDt rows between 2 preceding
                               and current row) RowCnt
        from    prices
        ) sets_of_3_days
where   RowCnt = 3  -- ignore first two rows
order by
        AvgPrice desc