SQL - Getting average value across date range

Question

I have a SQL table formatted like the following:

8/1/2012   0.111
8/2/2012   0.222
8/5/2012   0.333
.
.
.

I want to run a query that will get the average value in the second column over a specified date range. For dates that do not exist (8/3/2012 and 8/4/2012 in the above table), I want to use the last known value (0.222) for the missing date. How can I go about doing this?

Answer 1

Edit: Sorry I think I miss understand the question the first time I think this actually works (Tested)

Things I did:

1. Created a temp table to test (included so you can test)
2. Created a Recursive Common Table Expression that creates a Table named Dates (so i can have all the dates from a given date to another)
3. The query has a subquery that returns a table with all the dates in the range and for every date the max date in the table with the values that is before the given date

4. This table is joined with the table with the values and then the Average is calculated

   CREATE TABLE #Table1 (date_column  DateTime, Value Decimal(5,4))
   INSERT INTO #Table1 VALUES ('20120804', 0.1234)
   INSERT INTO #Table1 VALUES ('20120808', 0.2222)
   INSERT INTO #Table1 VALUES ('20120809', 0.9876)
   INSERT INTO #Table1 VALUES ('20120812', 0.0505);
   
   WITH Dates(date_column , row )
   AS (SELECT Cast('20120804' as datetime) date_column, 0 as row
   UNION ALL
   SELECT DateAdd(Day, 1, date_column), row + 1
   FROM Dates
   WHERE date_column < '20120830'
   ) 
   SELECT AVG(Value) FROM (
   SELECT Dates.date_column, (SELECT  MAX(date_column)
   FROM #Table1 WHERE #Table1.date_column <= Dates.date_column) maxDateWithValue 
   FROM Dates
   ) AllDatesWithLastDateWithValue
   LEFT JOIN #Table1 ON AllDatesWithLastDateWithValue.maxDateWithValue = #Table1.date_column 
   WHERE AllDatesWithLastDateWithValue.date_column >= '20120804'
   AND AllDatesWithLastDateWithValue.date_column <= '20120815'
   

Hope this helps... Nice question...