» Creating new column for rolling average in SQL
Creating new column for rolling average in SQL
|September 16, 2014
Posted by forumadmin
I am using SQL Server 2005.
Consider the following table with three columns:
rate: sqlfiddle.com/#!2/611682. I would like to create a new column that displays the moving average of
rate for each issueid at each date. I want to call this column
The result I am looking for is:
For issueid 1, the average on 3/31/2014 is the average of the rate values on 01/31/2014, 02/28/2014 and 3/31/2014. In other words, for each security and at each the date, the moving average is the average of the rate values for that month and the two previous months.
Here is what I have tried:
set mse_m = (
FROM my_table A
OUTER APPLY (SELECT *
WHERE i.issueid = A.issueid
AND i.[date] BETWEEN DATEADD(MONTH,-2,A.[date]) AND A.[date]) B
GROUP BY A.issueid, A.[date], A.rate)
from my_table i
However I get the following error:
Any help would be appreciated! Thanks!
More Related Questions
- Moving/Rolling Average in SQL I am using SQL Server 2005.
Consider the following table with three columns: issueid, date and rate: sqlfiddle.com/#!2/611682
The result I am looking for is:
For issueid 1, the […]
- select top 10 records for each category I want to return top 10 records from each section in the one query. Can anyone help how to do it. Section is one of the column in the table.
Database is sql server 2005. Top 10 by date […]
- How do I create unique constraint that also allows nulls in sql server Adding a column to table, and I want a unique constraint, so the column will insert GUID's, but to allow for existing data before column was added, I need to allow for NULL's in the […]
- SQL Server – pull X random records per state I have a table with records for each zip code in the united states. For the purposes of displaying on a map, I need to select X random records per state. How would I go about doing […]
- How to remove diagramming support objects from SQL Server? I need to remove diagramming support tables, stored procs, views, etc from SQL Servrer using TSQL script.
Is there such a script available?
SQL 2005 and 2008. Asked By - […]
- How can I do an UPDATE statement with JOIN in SQL? I need to update this table in MS SQL Server 2005 with data from its 'parent' table, see below:
- How to SELECT * INTO [temp table] FROM [stored procedure] How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?
Select all data from BusinessLine into tmpBusLine works fine.
- Using Table valued Function in join There is a Table valued function which gives multiple rows as output and there are 2 columns as output.
Now I want use this function in the query for all the employees in the employees […]
- SQL update query using joins I have to update a field with a value which is returned by a join of 3 tables.
,im.sku as iSku
,gm.SKU as GSKU
,mm.ManufacturerId as ManuId
- SQL Server Connection Unavailable in Visual Studio 2010 Express I'm hoping this is a easy/quick fix, but it is taking it's toll on me.
When I try to create a new Data Source in Visual Studio 2010 (Visual Basic) I do not have the option to select […]