Working with Views, Aggregates

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

Working with Views, Aggregates

Author

Topic

dlorenc
Posting Yak Master

172 Posts

Posted - 2010-04-14 : 18:07:00

I would like to include a field 'RecentCapacity' for each monthly record that I am aggregating in a view.

RecentCapacity = the last three months average of the project 'Done'..

'Done' is ontime plus late projects counted for the month.

How would you produce a moving three month average for each monthly record?

SELECT TOP (100) PERCENT Month, SUM(Due) AS Due, SUM(Late) AS Late, SUM(Ontime) AS Ontime, ISNULL(SUM(Late), 0) + ISNULL(SUM(Ontime), 0)AS Done

FROM dbo.EPM_Milestone_Performance_Compare

GROUP BY Month
ORDER BY Month

for example:

Month Done RecentCapacity
1/1/2010 200 NULL
2/1/2010 100 NULL
3/1/2010 300 200 (600/3)
4/1/2010 100 166 (500/3)
etc....

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-15 : 07:07:17

See if this helps
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911

Madhivanan

Failing to plan is Planning to fail

dlorenc
Posting Yak Master

172 Posts

Posted - 2010-04-26 : 17:50:47

yes..helped quite a bit..thank you...

there is a follow-up on this solution at:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=143433

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources