Dynamic proc for moving average

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

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic proc for moving average

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-01 : 10:47:55

Bonjour,

I want to create a stored procedure that can do averages on our invoice table. But I want it to be dynamic in that it can do either daily averages, weekly, bi-weekly, tri-weekly, monthly, bi-monthly, tri-monthly.
So I want to pass the time span as a parameter.
What would be the best approach. The user keeps changing the requirements (really? no way, user never does that) so I decided maybe I should wrap this in a sproc. Any input would be much appreciated.

Here is the table structure:
InvoiceID
Throughput Float
CommodityID INT
BranchID INT
Shipdate datetime

Merci buckets

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-01 : 10:53:59

Time span as a param sounds good. then you can just compute your average with a where clause that says

WHERE Shipdate BETWEEN @Stdt and @Endt

Or some such.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-01 : 11:12:54

how do you group by though for weekly, biweekly, triweekly, monthly etc?
Like following will return the daily
WITH CSumInvoices (
SELECT SUM(Throughput) AS SumThroughput,
BranchID,
CommodityID,
ShipDate
FROM Invoices <Where date span here>
GROUP BY BranchID, CommodityID, ShipDate
)
SELECT CommodityID
,BranchID
,AVG(SumThroughput) AS DefinitionValue
FROM CSumInvoices
GROUP BY BranchID, CommodityID
ORDER BY branchID, CommodityID

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources