CODEWITHSUNDEEP
sql
Clint Perry
Clint Perry

Reputation: 51

SQL - Getting average value across date range

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?

Upvotes: 5

Views: 2706

Answers (1)

saul672
saul672

Reputation: 897

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...

Upvotes: 3

Related Questions