Returning A Floating Point Average

Jan 20, 2008

Hi SQL people,
I have rating system on pages in my website, each page being rated one to five by users. At the back end, an ASP.NET page displays the average rating and number of ratings for each page. As the rating is stored as an integer, the SQL statement returns an integer average. I would like to get a floating point average. Currently the statement used to return the average looks like this:

select AVG(Rating) as AverageRating from Ratings where [RatingPage] = @RatingPage

Is there a simple way to modify this to return a floating point average without iterating through the records, or converting the Rating field to a float?

Thanks for helping!

View 3 Replies


ADVERTISEMENT

Floating Point Type??

Jan 21, 2008

I need to write a floating point value to a column in a SQL server database. I have the following line ofcode, that formats the float value before passing it off to the stored procedure.
Please look at the line of code below. Does the 8 after SqlDbType.Foat mean that this will be a floatingpoint with 8 signigicant digits past the decimal point?
mySqlCommand.Parameters.Add("@Price", SqlDbType.Float, 8).Value = float.Parse(InitialPrice);

View 3 Replies View Related

Floating Point Calculations...

Nov 9, 2001

Hello all,

I can't see any reason for this error, not having a high level understanding of maths I thought I'd post it and hope someone could share some light on it.

I yesterday got called by a client who said that a payment for Ł15 + VAT was being passed to their payment gateway as 17.62 when it should be 17.63. The VAT calculation is performed in a SQL Server 2000 stored procedure. In the end I tracked it down and it wasn't a propblem with my calculation.

The price was coming out as 17.63 fine. The stored procedure then had to return this price in pence (17.63 * 100 = 17.63). When I put in a print statement with this calculation it was correct but when I output the variable that the result was assigned to it was coming out as 1762.

The variable that the result was being put into was of real datatype.

I then wrote a udf to test this. Here is the function:

CREATE FUNCTION dbo.POUNDS_TO_PENCE
(
@POUNDVALUE real
)
RETURNS INTEGER
AS
BEGIN

RETURN @POUNDVALUE * 100

END

As you can see nothing very special.

If you run this runction and pass in 17.63 it will return 1762!!!

The bit I don't get is if I change the @POUNDSVALUE intput variable to type float it returns the correct amount.

I've also found that the same problem occurs when passing in Ł30 + VAT (35.25) + 1pence. So, 35.26 comes out as 3525 instead of 3526. This is the case if you keep doubling the number (and adding a few pence here and there).

Does anyone know why this is or is it a bug in the processor?

The SQL books online say the following about the float and real data types:

--------------------------------------------------------
float and real (T-SQL)
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

Syntax
float[(n)]
Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.


n is Precision Storage size
1-24 7 digits 4 bytes
25-53 15 digits 8 bytes


The Microsoft® SQL Server™ float[(n)] data type conforms to the SQL-92 standard for all values of n from 1 to 53. The synonym for double precision is float(53).

real
Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).


--------------------------------------------------------

Apart from the fact that it says 'Approximate number data types' I can't see any difference between the data type apart from the ranges.

Anyone any ideas?
Thanks
Tom Holder

View 2 Replies View Related

Floating Point Truncation

Mar 24, 2004

How can I truncate a floating point number to required number of decimal points
Eg:
100.642364074 to 100.64 and 67.643929847 to 67.645

Thanks.

View 2 Replies View Related

Floating Point Exception

Oct 8, 2007

Hi.

Intermittently, the following error has been displayed when a SQL job on our box (SQL Server 2000 Service Pack 4 running on Windows 2000) runs.

'A floating point exception occurred in the user process. Current transaction is canceled.' The error number given is 3628 though I've also seen a 4xxx number (not at machine at the moment so cant be precise for the latter).

The intermittent problem over the last two days has become more regular and now does not appear to be related solely to the SQL job. For example, when connecting to the box using my local copy of SQL 2005 Management Studio and I attempt to browse the database objects I get this problem.

To the best of my knowledge, this server has not had any software installed on it and/or nothing has changed on it recently.

It has now got to the point that this particular job no longer runs. The job in question, in case this is significant, is a vanilla stored proc that returns data using OPEN ROWSET to communicate with an Access database (this connectivity has been verified to be okay, in that other similar jobs all run just fine)

If anybody can shed some light and/or point me in the right direction I would be extremely grateful.

Cheers

CG

View 4 Replies View Related

Should Floating Point Calculations Be Doing In Sql Or C#?

Jul 20, 2005

Hi,I would like to know, if I need to do some floting point operations(mainly multiplication and division) on each roll of a table, should Iread the data out from the DB and do the calculation with a programminglanguage, say C#, or should I just use sql to do it on the sql server.An obvious advantage of doing it in the sql server is that you dontneed to transfer the data between the sql server and he applicationserver.But I am not sure if there are any other factors that will overridethis advantage: like the performance of doing lots of floting pointoperations in sql server.How is the performance of doing floting point operations in sql servercomparing to C# or other languages?Also are there any other factors that should be considered for thiscase andare there any other advantages to do this is sql server or in c#?ThanksBenny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Floating Point Calculation

Jul 20, 2005

I'm trying to perform a calculation on a field in SQL Server thatshould return a value with a decimal point. My problem is that thevalue returned is truncated without the decimal point. Is there asetting that needs to be turned on in SQL server to allow this?for exampleSelect 20/3should return 6.6666667but instead I get 6

View 1 Replies View Related

Choosing Floating Point DataTypes In Sql?

Apr 28, 2008

Hi all
I'm a newbie in SQL server and please excuseme for this silly question, Could anyone tell me when i should use which of the following types:

Decimal
Float
Real

I've mixed up !!! all of them can have floating point BUT what's the difference? some advise please!

Thanks in advance.
Kind Regards.

View 6 Replies View Related

Query: Getting AVG() Of An Integer Column As A Floating Point

Feb 12, 2004

I have the following query:

SELECT AVG(respondent_question.answer)

Now, the 'answer' column is an integer. However, I want the query to return a floating point number with one decimal place (i.e., 5.4, 2.3)

Since the column is an integer, the query returns only an integer by rounding to the nearest integer. How can I do this? Thanks!

View 1 Replies View Related

Floating Point Exception In SQL Server 2000

Oct 26, 2006

Hi,

I got below error in the SQL Server Production Server and i checked in the microsoft site it needs to install SQL Server service pack 4 to resolve the
problem.

"A floating point exception occurred in the user process. Current transaction is canceled"

I need help that i want to reproduce this below problem in the SQL Server environment and tried several ways but no luck.

Please advise me how to reproduce the problem.

Would be appreciate your help.

Regards
Sathish

View 4 Replies View Related

Floating Point Error - Order By Mystery

Oct 27, 2006

I'm having a problem that I think is due to corrupt data. Depending on
the column I use in my order by clause two problems are occuring.

1. No results are returned and I get this error:
A floating point exception occured in the user process.

2. Results are returned but there are a different number of rows depending on which columns I use in my Order By clause.

Examples
SELECT * FROM SymbolStats
ORDER BY calc_date, symbol

Returns - 12207 rows but only includes one of the 25 dates in the table.

----------

SELECT * from SymbolStats
ORDER BY current_hv

Returns - 0 rows.

----------

SELECT * from SymbolStats
ORDER BY average_hv

Returns - floating point error

With more conditions in the WHERE clause the number of results returned varies greatly.

The
fact that different numbers of rows can be returned from the same query
only differing in how they are ordered seems like a bug.

Does this sound like corrupt data? If so, what are the best methods for fixing it?

Thanks,
patrick

View 1 Replies View Related

.......A Floating Point Exception Occurred In The User Process.........

Sep 21, 2005

I'm getting this error:

System.Data.SqlClient.SqlException: A floating point exception occurred in the
user process. Current transaction is canceled. at
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping
tableMapping) at ...etc etc

    when updating one particular table, but not when updating another using nearly the same code.

I've searched and searched on the inet, but with no joy.

Microsoft have several different manifestations of it, under various
different circumstances, but the solutions all seem to involve
installing SP4, which by the looks of it is a massive procedure that
you have to go to university to be competent to do.

In any case, according to Enterprise Manager, I already have SP4
included. On the other hand, the one file of mine I checked the date of
against MS's hotfix file list was 2002 not 2003, so who knows?

My Service Manager is v 8.00.760, and it's the Development Edition (ie it's all running on the one machine).

According to the info on sqlDataAdaptor.update,  the
sqlError that gets returned  as part of the sqlException contains
fields Class, which gives the seriousness of the error, and State,
which identifies the exact error.  I get Class=16 (user-fixable)
and State=1, which we have to go to SQL Books Online to discover the
meaning of.  SQL BO probably sounded like a good idea to W.Gates,
but it took me half an hour to get anywhere near finding out what that
'1' meant.  When I did, it was (guess): 'A floating point
exception occurred in the user process. Current transaction is
canceled.' !!!

Can anyone please tell me what exactly might be causing the error?

Graham Rounce

View 11 Replies View Related

Floating Point - Precision Of Arithmetic Calculations On Server

Dec 4, 2013

I am trying to understand why SQL Server gives me significantly lower precision than many of the other sources that have tried when using the POWER function. My environment is 2008 R2 SP2 (10.50.4000.0 X64, Standard edition) on Windows 2008 X64 SP1

DECLARE @x FLOAT(53) = 1.0004;
DECLARE @y FLOAT(53) = 1.0/12.0;
SELECT POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05
GO
DECLARE @x FLOAT(24) = 1.0004;
DECLARE @y FLOAT(24) = 1.0/12.0;

[URL] ....

Answer: 3.33272237835747E-05

I also tried using Windows Calculator.Answer:3.3327223783495255846580902358195e-5

And an online "high-precision" calculator from [URL] ...

Answer: 3.332722378349525584658E-5

The best SQL results compare only to the fourth digit with other results. Everything else agrees to the 10th or 12th digit.

Is the precision of arithmetic calculations on SQL Server that bad, or am I doing something wrong here? Is there another more precise alternative?

I did the following. The results speak for themselves

DECLARE @x FLOAT(53) = 1.0004;
DECLARE @y FLOAT(53) = 1.0/12.0;
DECLARE @z FLOAT(53) = POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05
SELECT POWER((1+@z),12); -- 1.00039999839968

-- using results from other sources (c#, windows calc, casio.com)
SELECT POWER((1+3.33272237835747E-05),12) -- 1.0004
SELECT POWER((1+3.3327223783495255846580902358195e-5),12)-- 1.0004
SELECT POWER((1+3.332722378349525584658E-5),12) -- 1.0004

It is not important what I am trying to do, but in case it will work, I am trying to calculate the monthly return for a 90-day T-bill given the compounded annual return. x is the annualized return.

View 7 Replies View Related

A Floating Point Exception Occurred In The User Process

Aug 20, 2007

sql server 2000 sp4 build 2184runs stats with maintenance plansp_msforeachtablestored procedure updating full stats for each table and the resultsare identicalA floating point exception occurred in the user process. Currenttransaction is canceled.all pointers are to move to latest service packAny pointers on how to avoid this errorThanks for your timeMassa

View 6 Replies View Related

A Floating Point Exception Occurred In The User Process - Msg 3628

Jun 13, 2006

I am trying to run "select * from <table> where <columnname> = 0.0" inquery analyzer<columnname> is a floatI get the following error:Server: Msg 3628, Level 16, State 1, Line 1A floating point exception occurred in the user process. Currenttransaction is canceled.I get the same error when I use the float <columnname> through anapplicationI have tried upgrading to SP4, without any luck - still get the errormessage.Any help would indeed be appreciated.Thanks -AH

View 1 Replies View Related

URGENT - - A Floating Point Exception Occured In The User Process

Jul 20, 2005

We get the following error message."a floating point exception occured in the user process. currenttransaction is cancelled".this message comes when trying to excute a stored procedure. Thisexception is unpredictable.OS : Windows 2000 (SP3)Version: SQL server 2000 (SP3).

View 1 Replies View Related

A Floating Point Exception Occurred In The User Process. Current Transaction Is Canceled.

Jul 23, 2005

Hi,I'm running SQL Server Version 8.00.194 on Windows 2000.I am am running this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere ReutersSymbol = 'IBM.N'and TheoVolImplied > 0.0TheoVolImplied is of type float, precision 15, length 8.When I run this query I get this error:Server: Msg 3628, Level 16, State 1, Line 1A floating point exception occurred in the user process. Currenttransaction is canceled.If I run this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere TheoVolImplied > 0.0It works fine with no problems.If I run this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere ReutersSymbol = 'IBM.N'It works fine with no problems.Anyone have any ideas about what might be wrong?

View 2 Replies View Related

Returning Average Of Multiple Rows In A Table Join

Jul 23, 2005

Hi,I'm am looking for a little help. I need to create a SQL view whichjoins a few tables, and I need to return an average for a particularcolumn where a second duplicate ID exists...Heres an example of how the results could be returned...ID | Name | Order No. | Value---+------+-----------+---------5 | test | 1234 | 35 | test2| 1234 | 45 | test3| 1234 | 35 | void | 1235 | 55 | void2| 1235 | 65 | void3| 1235 | 55 | void4| 1235 | 7ID is my main join which joins the tablesName is a unique nameOrder No is the same for the different names, I only need to return onerow with this order no, and the first name (the rest are irrelevant)Value is the field which I wish to return as an average of all 3, 4 orhowever many rows is returned and share the same order no. This iswhere I get totally lost as I am pretty new to SQL. Can anyone provideany help on how I would go about limiting this query to the uniqueorder no's and returning the average of the value field, and I can takeit from there with my own tables.Thanks for your helpstr8

View 3 Replies View Related

Analysis :: Calculation Of average Using DAX AVERAGE And AVERAGEX

Jun 21, 2015

Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:

Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)

The results were, respectively: 701,68; 2120,60...; -669,441; and  finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:

test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...

So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.

I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.

View 2 Replies View Related

Is Point In Time Recovery To A Point Before The Last Full Database Backup Possible?

Mar 26, 2008

Hello all,

First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.

Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.

Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.

To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.

I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.

My questions are as follows:

a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?

b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).

I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.

Ryan

View 4 Replies View Related

Need An Average By Year Of An Average By Month

Feb 15, 2008

I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?

Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '

SELECT name, country_cd from weather_station where platformId=&&platformId;

SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');

with a result of:

NAME_________________CO
-------------------- --
OFFUTT AFB___________US

MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60

View 4 Replies View Related

How To Convert A Floating Value To Nvarchar Value

Sep 6, 2006

for example:



SELECT CAST(CAST(getdate() AS datetime) AS float)

how can i convert the return select value to nvarchar????

View 2 Replies View Related

SQL Server Over Point-to-Point T1

Nov 10, 2006

I'm running SQL Server 2005 on a Server 2003 machine serving both our home network as well as a remote site through a point-to-point T1. While file transfer speeds are up to par, the remote site's interaction with SQL Server (Point of sale system) is very slow. After testing I am certain that it has nothing to do with the actual physical machine in place neither is it an issue with the program itself since speeds are as they are supposed to be over the home network lan. It seems that there might be a packet size issue or something of the sort. Has anyone dealt with this before or have any thoughts?

Thanks,
Peter

View 7 Replies View Related

A SqlDataReader Is Returning An Int, When It Should Be Returning A Tinyint

Sep 25, 2007

I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables.  The SELECT looks like this:
 SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89
 The TreatmentStatus column is a tinyint.  When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2.  But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.
Why?

View 5 Replies View Related

Stored Procedure Returning 2 Result Sets - How Do I Stop The Procedure From Returning The First?

Jan 10, 2007

I hvae a stored procedure that has this at the end of it:
BEGIN
      EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
 
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?

View 2 Replies View Related

Average

Sep 26, 2007

Hello,I have two tables:[Posts] > PostId, ...[Ratings] > RatingId, PostId, RatingI want to select all posts and add a new column named AverageRating.This new column is the average of all ratings associated to that Post.If a post was not rated then its AverageRating would be NULL.How can I do this?Thanks,Miguel

View 2 Replies View Related

Average Help

Apr 18, 2006

if I have a bunch of times, and want an average of them, how do I do that?

Thanks.

For more details, see this thread:

http://www.dbforums.com/showthread.php?t=1215633

View 8 Replies View Related

Average?

Jun 19, 2007

I am getting the number of transactions for two different months based on dates entered from the user. I need to display the number of transactions along with the average sale. I have figured out how to get the dates and sum the transactions but I can't seem to add in the average....

(@prevMonthStart datetime, @prevMonthEnd datetime, @thisMonthStart datetime, @thisMonthEnd datetime)

as

select sum(case when orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end ) as PrevMonthCount,

sum(case when orders.orderdate between @thisMonthStart and @thisMonthEnd then 1 else 0 end ) as ThisMonthCount

(how do I add in the average for both months? The column is orders.ordertotal)

View 3 Replies View Related

Getting Average?

Jan 6, 2008

Using MS SQL 2005 server how can I get the average cost price of item 'shirt' from this table please?

code item description cost retail
---- ---- ----------- ---- ------
191 shirt shirt - white 2.30 9.99
170 pants pants - beige 6.34 15.00
196 shirt shirt - red 2.64 9.99
199 shirt shirt - blue 2.61 9.99

View 2 Replies View Related

How To Get Average Of Average.

Jan 19, 2008



Hi !

I am having this problem. I have a grouping someting like this.

Group1
Resolved Resolution Time Average Resolution Time
30 2904.46 96.82

54 1442.03 26.70

0 0.00 0.00
0 0.00 0.00





------------------------------
Avg : 30.88

Now to calculate the average inside the grouping I have used this


iif(Sum(Fields!ResolutionTime.Value) = 0 ,"0.00",(Sum(Fields!ResolutionTime.Value)/(iif(Count(Fields!ResolvedDate.Value)=0,1,Count(Fields!ResolvedDate.Value)))))

I am at a loss how to calculate the group footer (Result = 30.88 ) which is equal to (96.82 + 26.70 + 0 + 0 ) / 4 . This is the result from the existing Crystal report. I am trying to convert this report to reporting services. Now I cannot nest aggregate functions and also the row number can be dynamic I am confused how to resolve it.

Any ideas will be greatly appreciated.

Thanks in advance.

--Abbi.

View 9 Replies View Related

Average

May 21, 2008

Hi All,

I was hoping someone could help me with pluging moving average into my report.


This is the template I am using for moving average:
//
/*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";


This is what I have before I started to add the moving average. The report works but only displays the cummulative complexity rank.

WITH

MEMBER [Measures].[Date Key] AS

[Date].[Date].CurrentMember.UniqueName

SELECT

{

[Measures].[Date Key],

[Measures].[ComplexityRank]

} ON COLUMNS,

(

[Work Item].[System_State].[System_State],

(StrToMember(@StartDateParam):StrToMember(@EndDateParam))

)

ON ROWS

FROM [Team System]

WHERE

(

STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"),

STRTOSET(@IterationParam),

STRTOSET(@AreaParam),

STRTOSET(@WorkItemTypeParam)

)



What I want to do is also add the moving average for this over the parameter of Iterations. (IterationParam). This is what I have so far with the moving average in my report: I know I must have a lot of errors but I can't get past this first error. I highlighted the line with error. The error said: Query(7, 15) Parser: The syntax for '.' is incorrect. (msmgdsrv)


WITH

MEMBER

[Measures].[Date Key] AS

[Date].[Date].CurrentMember.UniqueName,

[Measures].[Moving Average] as

AVG({[Date].[Date].currentmember.lag(@IterationParam):

[Date].[Date].currentmember},[Measures].[ComplexityRank])



SELECT

{

{measures.[moving average],measures.[Internet Total Product Cost]} on 0,

[Date].[Date]. Members

[Measures].[Date Key],

[Measures].[ComplexityRank]

} ON COLUMNS,

(

[Work Item].[System_State].[System_State],

(StrToMember(@StartDateParam):StrToMember(@EndDateParam))

)

ON ROWS

FROM [Team System]

WHERE

(

STRTOMEMBER("[Team Project].[Team Project].["+@Project+"]"),

STRTOSET(@IterationParam),

STRTOSET(@AreaParam),

STRTOSET(@WorkItemTypeParam)




Does anyone have any suggestions?

Thanks,
Nici

View 11 Replies View Related

Getting An Average From A Table

Jan 5, 2007

I have two tables, table a holds all the votes by users of each element in table b. I was wondering, how do I get the average of all those votes in table a that relate to that each instance of a element in table b. For example table b has two elements created by a certain user that has been voted 5 times each by users with scores like 2, 5, 4, 2 , 2 for both of them. I just need to get the average of those numbers that pertain to those elements in table b. Thanks for any help.  

View 4 Replies View Related

Moving Average

Oct 25, 2000

Can any one be able to help me to write a querry on Moving Average

Example

Product Volume
Fish

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved