I'm working on some relevant computations based on a large data-index. There are 4 numbers (for instance sum(SPReturn*SPReturn) = RVSP etc) I need to calculate and I'm able to do so for either all the observations (approx 400.000) as a total sum, returning 1 value for each of the for relevant numbers; or, I'm able to compute the numbers for each separate observation.
However, what I'm trying to do is this:
The observations are ordered so that there are approx 60 observations pr. day, though the amount varies slightly. What I need is to calculate the 4 numbers for each day, so that I sum over the approx 60 observations, which leaves me for instance a RVSP number, then do the same for the next day etc - so I get a series with the 4 numbers, one observation for each day.
The code I've used so far to get 1x of each of the 4 numbers:
select *, covSPTY/sqrt(RVSP*RVTY) as CorSPTY from
sum(SPreturn*SPreturn) as RVSP,
sum(TYreturn*TYreturn) as RVTY,
sum(SPreturn*TYreturn) as CovSPTY,
Where datediff(hour, Prevobstime, Obstime) < 8
The 4 numbers I need for each day is RVSP, RVTY, CovSPTY and CorSPTY.
I figure what I need is some sort of grouping code that recognizes when the day splits, and is able to do so even though the amount of observations each day varies.