Problem Calculating Moving Average
Jul 20, 2005
Firstly, sorry for the long post, but I've included a fair bit of
sample data.
Im doing a comparision of 10yr Bond prices and CPI adjustments, with
an 18 week moving average of the CPI. I'm using a nested sub-query to
calculate the moving average, but I'm having difficulty selecting
exactly 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 Bond10
SELECT '1994-01-14', 6.57 UNION
SELECT '1994-01-21', 6.53 UNION
SELECT '1994-01-28', 6.44 UNION
SELECT '1994-02-04', 6.51 UNION
SELECT '1994-02-11', 6.54 UNION
SELECT '1994-02-18', 6.89 UNION
SELECT '1994-02-25', 7.18 UNION
SELECT '1994-03-04', 7.43 UNION
SELECT '1994-03-11', 7.43 UNION
SELECT '1994-03-18', 7.44 UNION
SELECT '1994-03-25', 7.66 UNION
SELECT '1994-04-01', 7.96 UNION
SELECT '1994-04-08', 8.07 UNION
SELECT '1994-04-15', 8.24 UNION
SELECT '1994-04-22', 8.23 UNION
SELECT '1994-04-29', 8.45 UNION
SELECT '1994-05-06', 8.82 UNION
SELECT '1994-05-13', 8.86 UNION
SELECT '1994-05-20', 8.44 UNION
SELECT '1994-05-27', 8.75 UNION
SELECT '1994-06-03', 8.79 UNION
SELECT '1994-06-10', 8.77 UNION
SELECT '1994-06-17', 9.24 UNION
SELECT '1994-06-24', 9.63 UNION
SELECT '1994-07-01', 9.66 UNION
SELECT '1994-07-08', 9.59 UNION
SELECT '1994-07-15', 9.41 UNION
SELECT '1994-07-22', 9.56 UNION
SELECT '1994-07-29', 9.58 UNION
SELECT '1994-08-05', 9.31
CREATE TABLE AvgCPI (
Closes [datetime] NOT NULL ,
AvgCPI [smallmoney] NOT NULL ,
)
INSERT INTO AvgCPI
SELECT '1994-01-14', 2.04 UNION
SELECT '1994-01-21', 2.04 UNION
SELECT '1994-01-28', 2.04 UNION
SELECT '1994-02-04', 2.04 UNION
SELECT '1994-02-11', 2.04 UNION
SELECT '1994-02-18', 2.04 UNION
SELECT '1994-02-25', 2.04 UNION
SELECT '1994-03-04', 1.51 UNION
SELECT '1994-03-11', 1.51 UNION
SELECT '1994-03-18', 1.51 UNION
SELECT '1994-03-25', 1.51 UNION
SELECT '1994-04-01', 1.51 UNION
SELECT '1994-04-08', 1.51 UNION
SELECT '1994-04-15', 1.51 UNION
SELECT '1994-04-22', 1.51 UNION
SELECT '1994-04-29', 1.51 UNION
SELECT '1994-05-06', 1.51 UNION
SELECT '1994-05-13', 1.51 UNION
SELECT '1994-05-20', 1.51 UNION
SELECT '1994-05-27', 1.51 UNION
SELECT '1994-06-03', 1.80 UNION
SELECT '1994-06-10', 1.80 UNION
SELECT '1994-06-17', 1.80 UNION
SELECT '1994-06-24', 1.80 UNION
SELECT '1994-07-01', 1.80 UNION
SELECT '1994-07-08', 1.80 UNION
SELECT '1994-07-15', 1.80 UNION
SELECT '1994-07-22', 1.80 UNION
SELECT '1994-07-29', 1.80 UNION
SELECT '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 Counter
FROM (
SELECT Closes, Prices FROM Bond10
) A1
LEFT JOIN (
SELECT Closes, AvgCPI FROM AvgCPI
) B1 ON A1.Closes = B1.Closes
LEFT JOIN (
SELECT Closes, AvgCPI FROM AvgCPI
) C1 ON C1.Closes >= A1.Closes AND DATEADD(Week,-18,C1.Closes) <
A1.Closes
GROUP BY A1.Closes, A1.Prices, B1.AvgCPI, C1.AvgCPI
-- HAVING COUNT(C1.Closes) = 18
ORDER BY A1.Closes
DROP TABLE Bond10
DROP TABLE AvgCPI
Expected Results
Closes Bon10 AvCPI MovAvg
========== ==== ==== ====
14-Jan-94 6.57 2.04
14-Jan-94 6.57 2.04
21-Jan-94 6.53 2.04
21-Jan-94 6.53 2.04
28-Jan-94 6.44 2.04
28-Jan-94 6.44 2.04
4-Feb-94 6.51 2.04
4-Feb-94 6.51 2.04
4-Feb-94 6.51 2.04
11-Feb-94 6.54 2.04
11-Feb-94 6.54 2.04
11-Feb-94 6.54 2.04
18-Feb-94 6.89 2.04
18-Feb-94 6.89 2.04
18-Feb-94 6.89 2.04
25-Feb-94 7.18 2.04
25-Feb-94 7.18 2.04
25-Feb-94 7.18 2.04 2.04
4-Mar-94 7.43 1.51 2.01
4-Mar-94 7.43 1.51 1.98
11-Mar-94 7.43 1.51 1.95
11-Mar-94 7.43 1.51 1.92
18-Mar-94 7.44 1.51 1.89
18-Mar-94 7.44 1.51 1.86
25-Mar-94 7.66 1.51 1.83
25-Mar-94 7.66 1.51 1.80
1-Apr-94 7.96 1.51 1.78
1-Apr-94 7.96 1.51 1.75
8-Apr-94 8.07 1.51 1.72
8-Apr-94 8.07 1.51 1.69
15-Apr-94 8.24 1.51 1.66
15-Apr-94 8.24 1.51 1.63
22-Apr-94 8.23 1.51 1.60
22-Apr-94 8.23 1.51 1.57
29-Apr-94 8.45 1.51 1.54
29-Apr-94 8.45 1.51 1.51
6-May-94 8.82 1.51 1.51
6-May-94 8.82 1.51 1.51
13-May-94 8.86 1.51 1.51
13-May-94 8.86 1.51 1.51
20-May-94 8.44 1.51 1.51
20-May-94 8.44 1.51 1.51
27-May-94 8.75 1.51 1.51
27-May-94 8.75 1.51 1.51
3-Jun-94 8.79 1.8 1.53
10-Jun-94 8.77 1.8 1.54
17-Jun-94 9.24 1.8 1.56
24-Jun-94 9.63 1.8 1.57
1-Jul-94 9.66 1.8 1.59
8-Jul-94 9.59 1.8 1.61
15-Jul-94 9.41 1.8 1.62
22-Jul-94 9.56 1.8 1.64
29-Jul-94 9.58 1.8 1.66
5-Aug-94 9.31 1.8 1.67
Thanks,
Stephen
View 6 Replies
ADVERTISEMENT
Nov 10, 2015
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 Replies
View Related
May 2, 2007
Im trying to get the average Fuel Consumption for A Manufacturer that produces two or more cars, so far ive only been able to find all manufacturers Average Fuel consumption.
Heres what I have so far
Select aManufacturer.MName, avg(FuelCons)
From aCar
Join aBuilts On aBuilts.CName = aCar.CName
Join aManufacturer On aBuilts.MName = aManufacturer.MName
Group by aManufacturer.MName
This produces nearly all I want only I need to be able to get only the Manufacturers who produce two or more Cars, ive tried implementing a few Count statements but nothings working, any ideas?
View 4 Replies
View Related
Feb 12, 2013
I want to calculate average of grades of each student and get the highest one with SQL command.
I have 2 tables:
Students:
*StudentId
*StudentName
___________
Grades:
*StudentId
*Grade
___________
I need to calculate average of each student and then get the highest.
My try:
Code:
SELECT Students.StudentId,Students.StudentName,AVG(Grades.Grade) AS avg_grade FROM Students s JOIN Grades g ON Grades.StudentId =Students.StudentId
GROUP BY Students.StudentId, Students.StudentName
ORDER BY avg_grade
LIMIT 1 FROM Students;
I encounter problem with this code, maybe it's Completely wrong...
View 5 Replies
View Related
Jul 4, 2014
How to calculate Average sal foe below scenario.
I am having tables with 12 columns as jan,feb,.......dec.
Now I want to calculate average salary for each record, but condition is that if any month salary is zero then that column also exclude from average calculation.
For example : if jan and feb column values are zero then i want to calculate (mar+apr+...+dec)/10.
View 5 Replies
View Related
Dec 3, 2013
I am calculating the average number of patients per day as like this
COUNT(DISTINCT PATIENTNAME) * 1.0/NullIf(COUNT(DISTINCT COALESCE(ARRIVEDATE,DEPARTDATE)),0) AS [AvgNo.ofpatients PerDay]
but i am getting results as like this 5.111111111111 , 8.000000000000,1.000000000000
we don't want to get that many digits after point we want only two digits like this 5.11,8.00 or 8, 1.00 or 1.
How can i do this?
View 2 Replies
View Related
Jul 5, 2007
Hi all,
I have a problem which needs to be sorted out immediate in Analysis service Cube. My requirement is as follows
The following data explains the average value of each employee in corresponding level.
Level1 - > E1 – (25hrs /25days) =1 hrs/day
Level2 ---------- >E2 – (125hrs /25days) = 5 hrs/day
Level3 ------------------------ >E4 – (150hrs /25days) = 6hrs/day
Level4 --------------------------------------- > E6 – (100hrs /25days) = 4hrs/day
Level4 --------------------------------------- > E7 – (75hrs/25days) = 3hrs/day
Level4 --------------------------------------- > E8 – (175hrs/25days) = 7hrs/day
Level3 ------------------------ >E5 – (75hrs/25days) = 3hrs/day
Level2 ---------- >E3 – (100hrs /25days) = 4hrs/day
Eg:
I have productivity records of each day and each employee. I need to calculate avg of each last level employee productivity by monthly. Again Last level employee productivity avg must be added up with their immediate head. But, when I define a Measure Item as avg in the cube, it sums all the values of lost level employees & head and divides with number of records (normal avg).
My requirement is calculating each head avg by sum of each last level employee avgs / no of employees. If head having value, he too will be added. Again Head’s Avg will be added up immediate head.
The following calculation gives average value at each level.
Average of Level 3(E4) = > (4+3+7+6)/4 = 5 hrs/day< = (E6+E7+E8+E4)/4
Average of Level 2(E2) = > (5+3+5)/3= 4.333 < =avg(Level3(E4))+avg(Level3(E5)))/2
Average of Level 1(E1) = > (4.333+4+1)/3 = 3.111
< = avg(Level3(E2))+avg(Level3(E3)))/2
Formula for average of level :
: (Sum of Children value + Head Value of Corresponding children) / (No.of Children +1)
I want to calculate average of each employee as well as average of each level in cube (SQL Server Analysis Services).
Thanks in advance
Thiru
View 1 Replies
View Related
Mar 19, 2008
Dear all,
I havea table name HISTORY_MEASURE which is a collection of different measure value issue from different mesuring device.
Values inthis table is represented as follow :
Id Name Value
==============================
1 Diameter1 0.45
2 Diameter2 1.23
3 Temperature2 123
4 Temperature2 200
5 Diameter1 0.65
Out of this table what I need to do is calculate the average value for each same [Name]. As you can see from the sample set above, the Diameter1 has 2 entries value hich gets store at different time of course.
So I I take the example of Diameter1 I need to calculate and display in a field the average results.
The result would be
Name Average
=====================
Diameter1 .....
Diameter2 ....
Temperature2 ....
Temperature1 ....
How can I perform this ?
Or could it be better to get a view of the table above which gets display as follow :
Diameter1 Temperature2 Diameter2
0.45 123 1.23
0.65 200 0
Thanks fro your help
regards
serge
View 7 Replies
View Related
Jun 9, 2015
I am having trouble with calculating AVG in a matrix. I have done the subtotal but I would like to know how to calculate the Average. I would also like AVA below the Total.
Link : [URL] ....
I have also added an image oh how the matrix looks like.
View 5 Replies
View Related
Mar 29, 2008
I am trying to create a SSRS report that needs to show average for both parent and child rows. In the example below i need to show an average of ErrorRecAge column for Company, then for cic_Group and one grand total/avg. How can this be done from single query (as below) and using SSRS built in functions/codes? I am also curious to know if this can be done from TSQL directly (ex. something similar to running sum/total). I appreciate the help.
Select
CustomerCorrection.Id As ParentId,
cc_company As cc_company2,
ccError.Id As ErrorId,
COALESCE(cic_Group,'Other Errors') As cic_Group,
COALESCE(cic_Code,'Unknown') As ErrorCode,COALESCE(cic_Description,'Unknown') As ErrorDescription,
DateDiff(hh,cc_entrydt,getdate())/24.00 AS ErrorRecAge
From
CustomerCorrection Inner Join CCError On
CustomerCorrection.Id = CCError.CustomerCorrectionId
Left Outer Join CustomerImportControl On
cic_code = ce_errno
Where cc_company = 'IWA' And CustomerCorrection.Id In (129,1004,3228)
Order By CustomerCorrection.Id
That Returns
129 IWA 992 Other Errors Unknown Unknown 399.500000
129 IWA 1089 Other Errors Unknown Unknown 399.500000
129 IWA 1760 Other Errors Unknown Unknown 399.500000
1004 IWA 952 Other Errors Unknown Unknown 365.333333
1004 IWA 1853 Other Errors Unknown Unknown 365.333333
3228 IWA 10 Other Errors Unknown Unknown 329.375000
Here is my temp workaround:
Select a.*,b.*
From
(
Select
cc_Company, Count(Distinct CustomerCorrection.Id) as RecCount, Avg(DateDiff(hh,cc_entrydt,getdate()))/24.00 As RecAge
From
CustomerCorrection
Group By cc_Company
) As a
Inner Join
(
Select
cc_company As cc_company2,
COALESCE(cic_Group,'Other Errors') As cic_Group,
Count(Distinct ccError.Id) As ErrorRecCount,
AVG(DateDiff(hh,cc_entrydt,getdate()))/24.00 As ErrorRecAge
From
CustomerCorrection Inner Join CCError On
CustomerCorrection.Id = CCError.CustomerCorrectionId
Left Outer Join CustomerImportControl On
cic_code = ce_errno
Group By cc_company, COALESCE(cic_Group,'Other Errors')
) as b
On a.cc_company = b.cc_company2
Order By cc_Company,cic_group
View 2 Replies
View Related
Aug 18, 2014
I need developing a query to get the average count by the following:
Day - use daily info for the last ??? days
Weekly - average
- Add all days and divide by 7
- As of Saturday midnight
Monthly - average
- Add all days and divide by days in the month
- As of last save on last day of month
Quarter - average
- Add all days and divide by number of days in the quarter
- As of last day of quarter
Year - average
I don't have requirements for year as of yet.
How can I get the avery count per these timeframes?
View 7 Replies
View Related
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
May 22, 2006
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
View 2 Replies
View Related
May 13, 2008
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
View 2 Replies
View Related
Apr 27, 2015
How you would calculate the average read/write latency experienced by a SQL Server instance during a specific time window in order to monitor this for multiple instances. From this MSDN blog, I know that you have to take multiple samples and do some calculations to get the correct latency.
[URL] ...
However, the SQLServer:Resource Pool Stats object tracks these numbers per resource pool and we want to get one number for the whole server. Since there can be a different base value for each resource pool, you can't simply sum the numerator values together. Here's some sample data from a server that illustrates the problem.
object_name counter_name instance_name cntr_value cntr_type
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) default 307318919 1073874176
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base default 25546724 1073939712
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) internal 2045730 1073874176
SQLServer:Resource Pool Stats Avg Disk Read IO (ms) Base internal 208270 1073939712
I'm thinking I would need to do some sort of weighted average, but I'm not sure if that will result in the correct value. Here's the formula I am thinking about using currently before doing the calculation over time
((default * default[base]) + (internal * internal[base]))/(default[base] + internal[base])
Then to do the calculation over time, I'd use the changes in the calculated numerator and denominator to get the average.
Does this sound like to correct way to get this value? Is there a good way to verify?
View 2 Replies
View Related
Jun 25, 2012
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.
View 11 Replies
View Related
Apr 17, 2008
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!
View 3 Replies
View Related
Jun 22, 2015
I have got this matrix and I am trying to calculate the average amount of working days in a month. At the moment, I have divided the total number of jobs by 21 for every month which is a hard coded value. However, I am not sure how to retrieve this value dynamically. Is there any formula that can find out the working days?
View 7 Replies
View Related
Jul 30, 2007
ID 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?
View 20 Replies
View Related
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
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
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
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
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
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
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
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
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
Sep 21, 2007
Hello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?
View 9 Replies
View Related
May 8, 2012
Select * from personnel
where salary > ALL (SELECT AVG (salary) from personnel group by bolno)
And how i find max average salary?
View 8 Replies
View Related
Apr 9, 2008
Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average".
I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below.
Shifts | Mon_day | Mon_Night | Tue_Day.....
Average | 5.2 | 10.2 | 15.2........
The codes that i tried..
declare @Weekday tinyint, @hour int
select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())
if (@Weekday= 1 and (@hour>= 7 AND @hour<19))
begin
Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average'
SELECT AVG(Sat_Night)
FROM Weekly
end
if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7))
begin
Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average'
SELECT AVG(Sun_Day)
FROM Weekly
end
View 7 Replies
View Related
May 28, 2008
Problem
fldyear fldascending flddecending
2007 20 23
2006 21 14
2007 41 12
2007 12 5
how do i find the average of fld ascending and descending where year=2007
Your help will be appreciated
View 6 Replies
View Related
Jul 12, 2013
How do you get the average number in sql?
View 11 Replies
View Related
