CODEWITHSUNDEEP
sqlsql-servert-sql
mlevit
mlevit

Reputation: 2736

Get average value by date (with missing dates)

I have the following table:

Interest Rate ID    Interest Rate   Interest Rate Start Date
20                  3.5             2009-12-02 00:00:00.000
21                  3.75            2010-03-03 00:00:00.000
22                  4               2010-04-07 00:00:00.000
23                  4.25            2010-05-05 00:00:00.000
24                  4.5             2010-11-03 00:00:00.000
25                  4.25            2011-11-02 00:00:00.000

As you can see the Interest Rate spans from one month into the next. I need to work out the average interest for an entire month.

So lets say we pick 2010-11. The interest rate for the 01/11/2010 to the 02/11/2010 was 4.25 as seen in row 23 but from 03/11/2010 onwards it was 4.5 percent. How can I write an SQL query (using TSQL) that uses the month and year to find the average interest rate?

Thanks

Upvotes: 2

Views: 485

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

This is a pain, so I hope there is not an off-by-one error:

select sum(((case when end_date > lastdt then lastdt else end_date end) -
            (case when start_date < firstdt then firstdt else start_date end)
           )* t.rate) /
       sum((case when end_date > lastdt then lastdt else end_date end) -
           (case when start_date < firstdt then firstdt else start_date end)
          )
from (select t.*,
             (select top 1 start_date - 1 from t t2 where t2.start_date > t.start_date order by t2.start_date
             ) as end_date
      from t
     ) t cross join
     (select cast('2011-11-01' as date) as firstdt, cast(2011-11-30) as lastdt
where firstdt <= t.end_date and lastdt >= t.start_date

The idea is to calculate the number of days that each interest rate is valid. The average is then the sum of the number of days times the rate divided by the number of days.

This is a bit complicated. The inner query puts in an end date to each record. The where clause just chooses the ones that overlap. And the select has the calculations for the weighted average.

Upvotes: 1

Related Questions