Time Weighted Average

[Guest]

I have continious data measuring values in irregular itterations across a few
years worth of dates. I have varying number of values per day and want to
weight these values based on the time difference between them. I want to
group them by day to do this so that I obtain a weighted daily average.

I have a unique number for each value which is a measurement of time. This
value is called Datenumber and each full number represents a day. The
decimal places denote the hours, mins, seconds, and milliseconds for each
reading.

Any help would be appreciated!! THanks in advance

 

[Dale Fye]

Give us a small sample of what your data looks like, and what the output
should look like for that data set. That will make is significantly easier
for us to figure out exactly what you are trying to do.

Dale

 

[Michel Walsh]

Hi,


With data like:


TimeStamp, Reading ' fields
101010 122
101099 115
102033 118
102143 116


where TimeStamp is the number of millisec since "some" time origin,
assuming you want to weight the value 115 (at t=101099) by
(102033-101010)/2, then, something like:



SELECT LAST(a.Reading) As value, (Nz(c.TimeStamp,
a.TimeStamp)-Nz(b.TimeStamp, a.TimeStamp))/2 As weight
FROM ((((myTable As a LEFT JOIN myTable As b ON a.TimeStamp > b.TimeStamp)
LEFT JOIN myTable As b1 ON a.TimeStamp >b1.TimeStamp)
LEFT JOIN myTable As c ON a.TimeStamp < c.TimeStamp)
LEFT JOIN myTable As c1 ON a.TimeStamp < c1.TimeStamp)
GROUP BY a.TimeStamp, b.TimeStamp, c.TimeStamp
HAVING (c.TimeStamp = MIN(c1.TimeStamp) OR c.TimeStamp IS NULL)
AND (b.TimeStamp = MAX(b1.TimeStamp) OR b.TimeStamp IS NULL)



should return the list of values and their weight to be applied to them.
That is because in the SELECT clause, due to the FROM and the HAVING clause,
b.TimeStamp stands for the greatest TimeStamp value preceding a.TimeStamp
while c.TimeStamp stands for the lowest value succeeding a.TimeStamp.



Hoping it may help,
Vanderghast, Access MVP