Geek asks and answers

SQL - getting average value in date range

I have an SQL table formatted as follows:

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

I want to run a query that gets the average in the second column over a specific date range. For dates that don't exist (8/3/2012 and 8/4/2012 in the table above), I want to use the last known value (0.222) for the missing date. How can i do this?

+5
source share
1 answer

Edit: Sorry, I think I missed understanding the question the first time I think it really works (tested)

What I've done:

  • Created a temporary table for testing (enabled so you can test)
  • , Dates ( )
  • , ,

  • ,

    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'

, ... ...

+3

More articles:


All Articles