sql - Get Sum of weighted average over Interval -

first of all, i'm using microsoft access 2002-2003 , using query in vba. means if possible need whole sql string can insert custom interval boundaries vba.

i have table layout:

   date   | value   1.1.2010      1   1.1.2012      2   1.1.2015      3 

i need sql query gets me weighted average of values in variable interval.

the idea values apply 1 date next, value 1 example applies 1.1.2010 31.12.2011 , on.
variable interval can (and of time) cut these, if interval 1.7.2010 1.7.2012 need consider 1.7.2010 - 31.12.2011 , 1.1.2012 - 1.7.2012
if interval starts before first date, value of first date used.

two examples:
1.1.2010 - 31.12.2012 -> (2*1 + 2) / 3 = 1.33 (two years of 1 , 1 of 2)
1.1.2008 - 1.7.2012 -> (4*1 + 0.5 * 2) / 4.5 = 1.11 (four years of 1 , half of 1 of 2)

my biggest problems getting these multiple intervals (especially custom interval) , getting value default first 1 if interval starts before first date.

my current query:

select sum(t2.value * datediff("d",t2.date,t1.date)) s1,         sum(datediff("d",t2.date,t1.date)) s2, s1 / s2 s3 table t1,      table t2 t2.date < t1.date   , not exists (select t3.date, t4.date                   table t3,                        table t4                   t1.date = t3.date , t4.date <> t2.date                     , t4.date > t2.date , t4.date < t1.date                     , t2.date < t1.date e); 

this gives me this:

s1                  |      s2      |    s3 730 * 1  + 1096 * 2    730 + 1096      s1/s2 

s1 weighted sum of intervals between 3 dates (2010-2012, 2012-2015)
, s2 sum of 2 intervals (the 5 years)
, s3 weighted average.

the query gets intervals doubling table, getting dates smaller , removing ones have date in interval not exists

now need "add" interval bounds equation...

the query have works table.
need interval: current output:

s1      |     s2    |   s3   2922         1826       1.6 

desired output example interval of 1.1.2008 - 31.12.2016:

   s1        |    s2    |    s3      ~5841       3287      5841/3287`   

so intervals 1.1.2008 - 31.12.2011 1, 1.1.2012 - 31.12.2014 2 , 1.1.2015 - 31.12.2016

i've done thorough rewrite of query, added parameters, , used parameters instead of lowest date t2, , highest date t1.

note i've avoided words date, table , value, since these sql keywords, , not used table or column names.

it's bit go through @ once, if have specific questions, i'll answer them.

note if want execute query through vba, have set parameters first.

parameters startinterval datetime, endinterval datetime; select sum(t2.weight *(iif(t1.thedate null, endinterval, t1.thedate) - iif(t2.thedate = (select min(thedate) thetable), startinterval, t2.thedate))) s1,         sum(iif(t1.thedate null, endinterval, t1.thedate)  - iif(t2.thedate = (select min(thedate) thetable), startinterval, t2.thedate)) s2, s1 / s2 s3 thetable t1 right join       thetable t2 on t2.thedate < t1.thedate t1.thedate = (select min(t3.thedate) thetable t3 t3.thedate > t2.thedate) or t1.thedate null