I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.
View 3 RepliesCan any one be able to help me to write a querry on Moving Average
Example
Product Volume
Fish
Can anyone tell me how to calculate a moving average for the below table?
--------------------
MONS /vol /counter(the date)
jan/8 / 2006-01-01
feb/9 / etc.
........
oct/5 / etc.
nov/6 /
dec/7 /
--------------------- I am using the below code, but it is having no effect......
SELECT i1.MONS, i1.COUNTER,
( SELECT AVG( i2.VOL )
FROM #RES1 AS i2
WHERE i2.MONS = i1.MONS And
DATEDIFF( month , i2.COUNTER , i1.COUNTER ) Between 0 And 12
) AS MovingAverageOver12Months
FROM #RES1 AS i1
ORDER BY i1.COUNTER
thanks in advance, dirwin
Hi All,
I am trying to create a report that shows moving average. Well I found this template:
//
/*Returns the average value of a member over a specified time interval.*/
CREATE MEMBER CURRENTCUBE.[MEASURES].[Moving Average]
AS Avg
(
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Lag(<<Periods to Lag>>) :
[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember,
[Measures].[<<Target Measure>>]
)
// This calculation returns the average value of a member over the specified time interval.
,
FORMAT_STRING = "Standard";
Only I do not have a specified interal or a constant number that I can place into
.Lag(<<Periods to Lag>>)
The report I have has a parameter that allows you to select a specific team project from a list. Although I have not figured out how to get the iteration paramter to update to only show the specified team project iterations, I want the graph to only show the iterations for that team project and the moving average (in the graph). The problem is that I can't just plug in the number 6 because different projects we have, have different numbers of iterations, and the user can pick any project from the list in the parameter.
Does anyone have suggestions on this?
Thanks!
-Nici
I am attempting to find a moving average for gas production per gas well over a 3 month period. I also would like to only keep the records in which the average of the previous 3 months is less than or equal to 90. My table roughly looks as such:
Code:
OperatorID RRCID Year Month Gas Production
88626113200813
88626113200829
88626113200835
88626113200845
88626113200855
88626113200885
88626113200895
886261132008104
886261132008116
886261132008126
88626113200916
8862611420081752
8862611420082697
8862611420083673
8862611420084706
8862611420085701
8862611420088730
8862611420089716
88626114200810717
88626114200811680
I would like to find the average of the last 3 months, or last 3 records.
Hello Guys,
Im quite new to SSRS and SQL in general and i've found this site to be a goldmine, and I found a thread on here relating to moving averages but I don't think it meets my needs.
I have a simple bar chart in SSRS with a Category Field (Fields!WeekYear.Value) which has the week number and year value on the axis, and a Data Field (Fields!Amount.Value) which is just a quantity for the given week.
Now what I need is a Moving Average of the amount with each new week generated (Simple and 3-point and/or 5-point), I've tried using some code and even functions but nothing is working for me so I was wondering if anyone here can help me out?
PS I've been tinkering with the RunningValue function and my DataSet is called "Cube" so I assumed that the scope should be "Cube" aswell but it only returns an error, am I right or am I being retarded?
As I say I am very new so please be gentle with me :)
Thank You in advance.
Man im a n00b!
Firstly, sorry for the long post, but I've included a fair bit ofsample data.Im doing a comparision of 10yr Bond prices and CPI adjustments, withan 18 week moving average of the CPI. I'm using a nested sub-query tocalculate the moving average, but I'm having difficulty selectingexactly 18 data points (ie When I include the 'HAVING COUNT(C1.Closes)= 18' line, I get no results).Can anyone help?-- Some sample data:CREATE TABLE Bond10 (Closes [datetime] NOT NULL ,Prices [smallmoney] NOT NULL ,)INSERT INTO Bond10SELECT '1994-01-14', 6.57 UNIONSELECT '1994-01-21', 6.53 UNIONSELECT '1994-01-28', 6.44 UNIONSELECT '1994-02-04', 6.51 UNIONSELECT '1994-02-11', 6.54 UNIONSELECT '1994-02-18', 6.89 UNIONSELECT '1994-02-25', 7.18 UNIONSELECT '1994-03-04', 7.43 UNIONSELECT '1994-03-11', 7.43 UNIONSELECT '1994-03-18', 7.44 UNIONSELECT '1994-03-25', 7.66 UNIONSELECT '1994-04-01', 7.96 UNIONSELECT '1994-04-08', 8.07 UNIONSELECT '1994-04-15', 8.24 UNIONSELECT '1994-04-22', 8.23 UNIONSELECT '1994-04-29', 8.45 UNIONSELECT '1994-05-06', 8.82 UNIONSELECT '1994-05-13', 8.86 UNIONSELECT '1994-05-20', 8.44 UNIONSELECT '1994-05-27', 8.75 UNIONSELECT '1994-06-03', 8.79 UNIONSELECT '1994-06-10', 8.77 UNIONSELECT '1994-06-17', 9.24 UNIONSELECT '1994-06-24', 9.63 UNIONSELECT '1994-07-01', 9.66 UNIONSELECT '1994-07-08', 9.59 UNIONSELECT '1994-07-15', 9.41 UNIONSELECT '1994-07-22', 9.56 UNIONSELECT '1994-07-29', 9.58 UNIONSELECT '1994-08-05', 9.31CREATE TABLE AvgCPI (Closes [datetime] NOT NULL ,AvgCPI [smallmoney] NOT NULL ,)INSERT INTO AvgCPISELECT '1994-01-14', 2.04 UNIONSELECT '1994-01-21', 2.04 UNIONSELECT '1994-01-28', 2.04 UNIONSELECT '1994-02-04', 2.04 UNIONSELECT '1994-02-11', 2.04 UNIONSELECT '1994-02-18', 2.04 UNIONSELECT '1994-02-25', 2.04 UNIONSELECT '1994-03-04', 1.51 UNIONSELECT '1994-03-11', 1.51 UNIONSELECT '1994-03-18', 1.51 UNIONSELECT '1994-03-25', 1.51 UNIONSELECT '1994-04-01', 1.51 UNIONSELECT '1994-04-08', 1.51 UNIONSELECT '1994-04-15', 1.51 UNIONSELECT '1994-04-22', 1.51 UNIONSELECT '1994-04-29', 1.51 UNIONSELECT '1994-05-06', 1.51 UNIONSELECT '1994-05-13', 1.51 UNIONSELECT '1994-05-20', 1.51 UNIONSELECT '1994-05-27', 1.51 UNIONSELECT '1994-06-03', 1.80 UNIONSELECT '1994-06-10', 1.80 UNIONSELECT '1994-06-17', 1.80 UNIONSELECT '1994-06-24', 1.80 UNIONSELECT '1994-07-01', 1.80 UNIONSELECT '1994-07-08', 1.80 UNIONSELECT '1994-07-15', 1.80 UNIONSELECT '1994-07-22', 1.80 UNIONSELECT '1994-07-29', 1.80 UNIONSELECT '1994-08-05', 1.80-- My query so far:SELECT A1.Closes, A1.Prices, B1.AvgCPI, SUM(C1.AvgCPI) AS MovSumCPI,AVG(C1.AvgCPI) AS MovAvgCPI, COUNT(C1.AvgCPI) AS CounterFROM (SELECT Closes, Prices FROM Bond10) A1LEFT JOIN (SELECT Closes, AvgCPI FROM AvgCPI) B1 ON A1.Closes = B1.ClosesLEFT JOIN (SELECT Closes, AvgCPI FROM AvgCPI) C1 ON C1.Closes >= A1.Closes AND DATEADD(Week,-18,C1.Closes) <A1.ClosesGROUP BY A1.Closes, A1.Prices, B1.AvgCPI, C1.AvgCPI-- HAVING COUNT(C1.Closes) = 18ORDER BY A1.ClosesDROP TABLE Bond10DROP TABLE AvgCPIExpected ResultsCloses Bon10 AvCPI MovAvg========== ==== ==== ====14-Jan-94 6.57 2.0414-Jan-94 6.57 2.0421-Jan-94 6.53 2.0421-Jan-94 6.53 2.0428-Jan-94 6.44 2.0428-Jan-94 6.44 2.044-Feb-94 6.51 2.044-Feb-94 6.51 2.044-Feb-94 6.51 2.0411-Feb-94 6.54 2.0411-Feb-94 6.54 2.0411-Feb-94 6.54 2.0418-Feb-94 6.89 2.0418-Feb-94 6.89 2.0418-Feb-94 6.89 2.0425-Feb-94 7.18 2.0425-Feb-94 7.18 2.0425-Feb-94 7.18 2.04 2.044-Mar-94 7.43 1.51 2.014-Mar-94 7.43 1.51 1.9811-Mar-94 7.43 1.51 1.9511-Mar-94 7.43 1.51 1.9218-Mar-94 7.44 1.51 1.8918-Mar-94 7.44 1.51 1.8625-Mar-94 7.66 1.51 1.8325-Mar-94 7.66 1.51 1.801-Apr-94 7.96 1.51 1.781-Apr-94 7.96 1.51 1.758-Apr-94 8.07 1.51 1.728-Apr-94 8.07 1.51 1.6915-Apr-94 8.24 1.51 1.6615-Apr-94 8.24 1.51 1.6322-Apr-94 8.23 1.51 1.6022-Apr-94 8.23 1.51 1.5729-Apr-94 8.45 1.51 1.5429-Apr-94 8.45 1.51 1.516-May-94 8.82 1.51 1.516-May-94 8.82 1.51 1.5113-May-94 8.86 1.51 1.5113-May-94 8.86 1.51 1.5120-May-94 8.44 1.51 1.5120-May-94 8.44 1.51 1.5127-May-94 8.75 1.51 1.5127-May-94 8.75 1.51 1.513-Jun-94 8.79 1.8 1.5310-Jun-94 8.77 1.8 1.5417-Jun-94 9.24 1.8 1.5624-Jun-94 9.63 1.8 1.571-Jul-94 9.66 1.8 1.598-Jul-94 9.59 1.8 1.6115-Jul-94 9.41 1.8 1.6222-Jul-94 9.56 1.8 1.6429-Jul-94 9.58 1.8 1.665-Aug-94 9.31 1.8 1.67Thanks,Stephen
View 6 Replies View RelatedID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE)
1 01/01/2007 PURCHASE 10 1000
2 01/01/2007 PURCHAES 5 1100
3 01/01/2007 SALES -5 *TobeCalculated
4 02/01/2007 Purchase 20 9000
5 02/01/2007 SALES -10 *TobeCalculated
5 02/01/2007 purchase 50 8000
6 03/01/2007 Sales -10 *TobeCalculate
7 01/01/2007 Purchase 20 12000
I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)
In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.
When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.
The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.
Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.
Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
Any way to create a measure that filters the second column to mimic the behavior of only filtering for slicer for the Open Pool Date values as per the image below. Ultimately, I need to create a measure that only includes accounts that were opened 6 months prior to the month row context.
Trying to get the values in the second column to only include aging accounts 6 months prior instead of 12 months prior.
Trailing6Month Conversion:=CALCULATE([TOTAL LTD Converted Amount] ,Filter(Settlement700,Settlement700[OpenDatePool]>=RELATED(DimDate[Trailing6MonthsEnd])))
Trailing6Month LTD Conversion Amount:=CALCULATE([Trailing6Month Conversion],DATESINPERIOD(DimDate[FullDate],LASTDATE(DimDate[FullDate]),-6,Month))
I am using SQL 2012 SE. I am trying to move .mdf and .ndf files after a database is detached. Here is my code that is just to copy the mdf file. I am testing it against this file now and if it worked then I would do the move ldf file the same way.
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
DECLARE @cmd nvarchar(4000)
[Code] ...
The only message is I see while the query is executing is :Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
If I manually copy over the file it takes 30 seconds since the file is only 2GB and the script takes 45 minutes and still executing.
I have a documented API and an XSD for that API data.
I want to create an SQL DB from that XSD file and validate against it. There used to be tools and tutorials for this in Visual Studio 2010 but 2013 is proving to be difficult.
What is the best way to move from the XSD into an SQL DB? All manual coding?
I have this data returning from a query:
Procedure Intermediate New Old Total avg
Proc1 6 0 0 6 2.000000
Proc2 74 13 0 87 29.000000
Proc3 29 0 0 29 9.666666
Proc4 16 0 0 16 5.333333
And I want to dynamically calculate the average rather than divide the total by the number of columns every time, I want to divide only by the number of non zero values per row for intermediate, new and old. So row 1 average would just be itself = 6, row 2 would be 74+13+87/3 etc.
;with cte as
(
select
f.Procedure,
SUM(case when f.old_new = 'O' then f.Value else 0 end) "Intermediate",
SUM(case when f.old_new = 'Y' then f.Value else 0 end) "New",
SUM(case when f.old_new = 'N' then f.Value else 0 end) "Old"
[code]....
Hi,I am trying to add a staggered running total and average to a queryreturning quarterly CPI data. I need to add 4 quarterly data pointstogether to calculate a moving 12-month sum (YrCPI), and then tocomplicate things, calculate a moving average of the 12-month figure(AvgYrCPI).Given the sample data:CREATE TABLE [dbo].[QtrInflation] ([Qtr] [smalldatetime] NOT NULL ,[CPI] [decimal](8, 4) NOT NULL) ON [PRIMARY]GOINSERT INTO QtrInflation (Qtr, CPI)SELECT '1960-03-01', 0.7500 UNIONSELECT '1960-06-01', 1.4800 UNIONSELECT '1960-09-01', 1.4600 UNIONSELECT '1960-12-01', 0.7200 UNIONSELECT '1961-03-01', 0.7100 UNIONSELECT '1961-06-01', 0.7100 UNIONSELECT '1961-09-01',-0.7000 UNIONSELECT '1961-12-01', 0.0000 UNIONSELECT '1962-03-01', 0.0000 UNIONSELECT '1962-06-01', 0.0000 UNIONSELECT '1962-09-01', 0.0000 UNIONSELECT '1962-12-01', 0.0000 UNIONSELECT '1963-03-01', 0.0000 UNIONSELECT '1963-06-01', 0.0000 UNIONSELECT '1963-09-01', 0.7100 UNIONSELECT '1963-12-01', 0.0000 UNIONSELECT '1964-03-01', 0.7000 UNIONSELECT '1964-06-01', 0.7000 UNIONSELECT '1964-09-01', 1.3900 UNIONSELECT '1964-12-01', 0.6800 UNIONSELECT '1965-03-01', 0.6800 UNIONSELECT '1965-06-01', 1.3500 UNIONSELECT '1965-09-01', 0.6700 UNIONSELECT '1965-12-01', 1.3200I am trying to return the following results:Qtr CPI YrCPI AvgYrCPI-------- ----- ----- --------1-Jun-60 1.481-Sep-60 1.461-Dec-60 0.721-Mar-61 0.71 4.371-Jun-61 0.71 3.601-Sep-61 -0.70 1.441-Dec-61 0.00 0.72 2.531-Mar-62 0.00 0.01 1.441-Jun-62 0.00 -0.70 0.371-Sep-62 0.00 0.00 0.011-Dec-62 0.00 0.00 -0.171-Mar-63 0.00 0.00 -0.181-Jun-63 0.00 0.00 0.001-Sep-63 0.71 0.71 0.181-Dec-63 0.00 0.71 0.361-Mar-64 0.70 1.41 0.711-Jun-64 0.70 2.11 1.241-Sep-64 1.39 2.79 1.761-Dec-64 0.68 3.47 2.451-Mar-65 0.68 3.45 2.961-Jun-65 1.35 4.10 3.451-Sep-65 0.67 3.38 3.601-Dec-65 1.32 4.02 3.74Note, 4 data points are required to calculate a moving sum of CPI(YrCPI) and 4 calculate YrCPI figures are required calculate theannual average of YrCPI (AvgYrCPI), giving a staggered effect to thefirst 7 resultsThis sad effort is about as far as I've got:SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPIFROM QtrInflation IJOIN (SELECT TOP 4 Qtr, CPIFROM QtrInflation) SON S.Qtr <= I.QtrGROUP BY I.Qtr, I.CPIORDER BY I.Qtr ASCCan anyone suggest how do achieve this result without having to resortto cursors?Thanks,Stephen
View 5 Replies View Relateddoes anyone have a script that will read the sysdatabase table and transfer all datbase's to another server. example pseudo code:
start loop
get local sserver db name
create db on other server
transfer ALL objects to new database
end loop
I have inherited an SQL server that is running out of disk space. SQL 2000 was installed on a regretfully small C: partition, and I need to move as much as I can to the D: partition. I really don't want to uninstall and start over, and I am hoping there is a whitepaper or something that I can use to accomplish the same thing.
Any help is appreciated!
My company recently purchased a new ERP system which does not support 64 bit SQL Server. For this reason, I need to uninstall the 64 bit SQL server 2005 and install the 32 bit version. This leads to my question. I have an application currently using a database on the 64 bit version and would like to confirm that the DB will work correctly in the 32 bit version. Do I just have to detach the database, copy the files, then install SQL server 2005 32 bit and attach the DB?
View 4 Replies View RelatedI have been using MS Access for a long time. The last couple of months my sites have really picked up in business (good rankings) and we keep crashing the server (No sites on the server that use MS Access will run) We get the errorSystem.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider()at System.Data.OleDb.OleDbConnection.Open()at DreamweaverCtrls.DataSet.DoInit()Also I am finding out from some of my customers that their sites they can't use at times because of the error but the server hasn't crashed yet because others are up. Well I believe we are exceeding MS Access very badly because it crashes the server daily now and sometimes more then once a day. So I am going to start moving my MS Access database to SQL but have NO CLUE how where to start etc or use SQL correctly. If people can point me in the right direction that would be very helpful. Everything I find online is people needing help but no solutions that work for me. My new server is a windows server running ASP.Net 2.0 and to edit my SQL I have "ASP.Net Enterprise Manager" and Plesk for the server.1. A good book to follow and easy to follow for SQL (connections / how tos) or website2. Good examples or book on "ASP.Net Enterprise Manager"3. How to convert access databases into SQL easy (some have over 50,000 listings)4. how to convert Tab files into SQL easy.Thank you so much for any help. Also I am using Dreamweaver for most of the edditing and connections. I know its not your favorite but I don't have the time to write everything by hand and it works good for what I do on the websites.Thanks again,Rusty
View 2 Replies View RelatedHi
I want to transfer a database from one PC to another. (Both running SQL Server 2005 express)
I have copied the files (SQL Server Database Primary Data File and SQL Server Database Transaction Log File) from the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData folder of the source PC to the destination PC (Same folder).
I was under the impression that using Microsoft SQL Server 2005 - SQL Server Management Studio Express these files would be picked up and could be used, do I infact need to do something to attach the files/database so that they will appear in SQL Server management?
I have 2 databases on one server that I want to consolidate into one database. I'm just learning SQL Server 2005. What is the easiest way to move my 3 tables from one database to a new one on the same server? Do I have use SSIS to do it, or can a simple query be written? I'm new so please be a little detailed in your answer. Thanks in advance for any comments.
View 2 Replies View RelatedHi I'm very new to this but what I'm looking to do should'nt be too difficult although I've tried to no avail.
I'm looking to deploy a web site that uses SQL2000 to another server. Im trying to find out how to move the database to that server. Ideally I would like to be able to save it to CD for safety but its not like old access where you save the mdb file. I need to save the whole 'DTS' package, is that what its called? stored procedures, triggers the lot.
I tried the wizards but just got in a mess.
Thanks
I've created a web application with Visual Studio 2003. Uploading my application to the server is easy enough. I use the Copy Project utility from the Project selection on the menu bar.
I'm using a remote server-- and hosting account that supports ASP.NET and ms SQL. But now I want to copy my ms SQL database and can't find any way to copy my DB to the server.
What do the best way to to this?
PaulTucson, AZ
Can someone tell me how I can move the temp database. I know it get's recreated on startup so there must be somewhere that it's referencing.. Is it the model database it uses?
Thanks!
What is the best way to move the databases to a new hard drive connected to the system? We added a new larger hard drive and I would like to move just the databases to the new hard drive.
Thanks
I have an SQL database running on an old server and i am upgrading the server, but i am having some problems moving my SQL database over. Has any one got a work through they could mail me to give me a hand!!
Cheers
Mike
Is there a way that I can move DTS packages from test machine to production machine? Or do I have to recreate new packages in production.
Thanks,
Jim
Is there any easiest way to move a 100 Gig sql 7.0 database to sql2000?
View 1 Replies View RelatedHi, Everyone,
I need to copy a Database onto another Server, what is the best way to do it?
Thanks
I have serveral jobs set up on my current SQL Server, and I am not sure how to move the jobs to my new SQL server. What is the best way to do this?
Thanks,
John
I have a scenario where I need to refresh a database that is in 7.0 (converted from 6.5 database) from the original database. Is there an easy way to do this. I have tried creating a DTS package but the data never seems to make it accross.
View 1 Replies View RelatedHi,
Iam trying to move the database from one server to the other server.I know it can be done in the following 3 ways i.e
Copy .mdf and .ldf files to the other server and attach them.
or
restore the databses from the backups
or
use data transformation services.
Now let me know which is the fastest way of doing this.
and also what are advantages and disadvantages of above methods if possible.
Any suggestion would be appreciated.
Thanks.
Hi
I have a database(CEB) and my CEB.mdf is on D Drive and CEB.LDF is on
G DRIVE ...NOw I want to move the CEB.LDF on to the different drive ..
can any one suggest me the way and will I have any effect on the database.
It is kind of urgent.
Thanks
RAGHU
I want to move the physical location of the msdb database on a SQL server. However I cannot drop the database with the drop database statement, even setting it to dbo use only and single user mode. I get an error message saying the database is in use. Obviously I do not want to reinstall the server. The reason I want to move it is because it is located in the default dir on the C: drive which is running out of space.
Any ideas.....
I have a database approximately 30 GB in sixe which need to be moved from one SQL server to another. Does anyone know the most efficient way of doing this, other then backing up to tape?
View 1 Replies View Related