Partial Sums - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Partial Sums

Author

Topic

MartinFalch
Starting Member

5 Posts

Posted - 2009-07-29 : 09:20:22

Hi there,

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
(
Select
sum(SPreturn*SPreturn) as RVSP,
sum(TYreturn*TYreturn) as RVTY,
sum(SPreturn*TYreturn) as CovSPTY,
From ReturnTotal
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.

Anyone has an idea? I'd be grateful for any help :)

Thanks!

Best regards,

Martin Falch

X002548
Not Just a Number

15586 Posts

Posted - 2009-07-29 : 09:24:51

got a column in ReturnTotal that is datetime you want to use? (or date in sql 2k8?)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 09:29:05

[code]
Select dateadd(day, datediff(day, 0, Obstime), 0) as ObsDate,
sum(SPreturn*SPreturn) as RVSP,
sum(TYreturn*TYreturn) as RVTY,
sum(SPreturn*TYreturn) as CovSPTY,
From ReturnTotal
Where datediff(hour, Prevobstime, Obstime) < 8
group by dateadd(day, datediff(day, 0, Obstime), 0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources