T-SQL (SS2K8) :: Get Top 10 Servers With Highest Average CPU?
Mar 9, 2015
I have a need to create a line graph report in SSRS 2008. The report should show the top 10 servers from a group of servers with the highest CPU utilization for the last day. The report is for Microsoft System Center Operations Manager 2012. I have a SQL query that will return the average CPU for all of the servers in the group, with the average for each hour (24 records per server).
How can I get the top 10 servers with the highest average CPU? I think I need to create an average of the averages, then select the top 10. Here is the SQL query I have so far:
Use OperationsManagerDW
GO
SELECT
vPerf.DateTime,
vPerf.SampleCount,
cast(vPerf.AverageValue as numeric(10,2)) as AverageCPU,
vPerformanceRuleInstance.InstanceName,
[code]....
View 2 Replies
ADVERTISEMENT
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 18, 2011
I have a requirement like, we are having two tables in our database.
Table names: student, marklist
Student table values:
id studname
------------------
1x
2y
3z
4a
5b
Marklist table values:
id maths physics English
---------------------------------
1506070
2706040
3508070
45010070
5906070
But my requirement is, I need to display the data "subject wise" highest marks for each student.
for example:
id name highestmark
---------------------------------
1 x English
View 9 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
Apr 7, 2015
I need to calculate the average number of days between customer orders.
My table has customer number and order number and the order date.
I would like to end with customer number last, order date and the average number of days between orders for each customer.
cust_idorder_numorder_date
HOLLGCAB 119482 02/27/2015
JILCO 119484 02/27/2015
KEY 119491 02/27/2015
TURNER 119496 02/27/2015
KEY 119499 02/27/2015
[Code] .....
View 9 Replies
View Related
Oct 7, 2015
I have a dataset as such:
Student TestTypeDate TestCnt
111-22-1111English2015-09-01 10:00:00 1
111-22-1111Math2015-09-02 11:00:00 2
111-22-1111Geo2015-09-03 12:00:00 3
222-11-2222English2015-09-01 10:00:00 1
333-22-1111English2015-09-01 10:00:00 1
[Code] ...
So some have just 1 test and some have multiple. I have a count for each. What I need to do is use that count and get an average time between each test per student.
View 9 Replies
View Related
Mar 30, 2015
how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory](
[username] [varchar](50) NULL,
[current_tenure] [int] NULL,
[move_in_date] [smalldatetime] NULL,
[Cnt_Lead_id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
[code]....
View 9 Replies
View Related
Aug 27, 2014
I am performing analysis of linked servers across 2000-2008R2 and need to find/build a list of linked servers that are truly active. For the sake of the post let's define 'active' have executed a distributed query in the last 5 days.
I have been scanning the DMVs without much success. Perhaps I must look more closely at MSDTC?
The end result would be to cleanup 300+ linked servers across 40+ SQL Servers.
View 4 Replies
View Related
Jun 17, 2015
Can it be possible to split the below string to NICK and SERVERS. Query which provides two columns, first column data should be NICK and other SERVERS.
_BF-TYPE:NICK_ _BF-SUBSYSTEM:SERVERS_
View 9 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
May 26, 2006
Hi! i have a column named number(int),ref_number(char),date(date),creator(ch ar)...what would be my query string, if posible, to get the highest value for clumn 'number'? by the way im using SQL Desktop Engine... any inputs will be greatly appreciated...thanks in advanace!
View 3 Replies
View Related
Jul 20, 2005
Hi all,Given:f1 f2-- --1 a2 b3 b4 b5 c6 c7 dHow to get the highest count for field f2, output as:f2 f2count-- -------b 3
View 1 Replies
View Related
Jan 23, 2008
I have four columns that include numbers like: A B C D
I want to update D with highest value of A, B or C.
For example A = 1, B = 2, C= 3 then D should be 3.
Can you help me?
View 1 Replies
View Related
Nov 21, 2007
I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.
We use rectangles to force page breaks to page 2 and to page 3.
When running the report on the report server, it shows and prints fine.
When running the report from the QA website internally, it shows and prints just fine.
When running the report from the production website from a machine internally, it shows and prints just fine.
When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.
I need help in determining where I even begin with trouble shooting this!
View 1 Replies
View Related
Feb 27, 2007
Can someone tell me what the highest value (number) is for an int data type in sql server 2005?
View 2 Replies
View Related
Jun 19, 2007
Hai frendz,
I am having a table named Employee(int EID, float Salary)...
Now I want to select the highest salary in the table and the query is-
"select top 1 EID, Salary from Employee ORDER BY Salary DESC"
Now I need to write a query which selects the second highest salary.
So how to achieve this?..
thanx
View 6 Replies
View Related
Sep 26, 2004
hello all,
i'm not new to SQL but i cant seem to get this right:
hope some one will:
how to find highest unique number of a certain column(val)for a specific name(name is in INPUT parameter)
i.e :
id | name | val
---------------
1 | name1 | 2.7
2 | name1 | 3.5
3 | name1 | 3.5
4 | name1 | 3.5
5 | name1 | 1.3
6 | name2 | 3.1
7 | name2 | 3.1
8 | name2 | 2.9
requested result:
if input param(name)=name1
result:
1 | name1 | 2.7
if input param(name)=name2
result:
8 | name2 | 2.9
hope some one can write the sql cmd for this
i'll be grateful !
thank you!
View 3 Replies
View Related
Dec 8, 2004
Got a table with salestatistics and I want to know what ItemNr sold most a certain day. Can anyone help me with the SQL?
View 1 Replies
View Related
Sep 21, 2004
Hello,
I have the following problem.
I have three Columns
name / version / package
I want to create a select query, that shows me all names and their actuall package. The actual Package is the record with the highest version number.
For Example my Database has the following entries :
name / version / package
name1 / 1 / package1
name2 / 1 / package1
name2 / 2 / package3
name3 / 1 / package2
The output should look like this :
name1 / 1 / package1
name2 / 2 / package3
name3 / 1 / package2
Has anyone a idea and can help me ?
Thanks in Advance
Mirco
View 4 Replies
View Related
Aug 26, 2004
Hi all,
Hopefully you can assist this pleb (me) with some (hopefully) basic scripting.
I have a table which has bucket loads of rows in it (funnily enough)
Field1 & Field2
Field1 is repeated numerous times (but also has differing values) through the table
Field2 is a numeric value which is assigned to the Field1
What I need to do is pull back all the unique Field1's but only those with the highest value in Field2
Example:
Field1 Field2
blah 100
blah 400
blah1 12
blah1 9
blah1 2
blah 350
I need the return to be basically:
Field1 Field2
blah 400
blah1 12
I am sure that this has to be straight forward - however my brain just isn't connected the right way at the moment....
Thanks in advance for any assistance on this.
Troy
View 2 Replies
View Related
Jan 12, 2012
I am attempting to create a report that will display all the yearly, and monthly gas production for a company but the issue I am encountering is finding amended records for a lease. Below is what the query produces:
Company Number Year Month Gas Production Lease Number
3125 20091 296 9105701
3125 20091 345 165207
3125 20091 565 2250593 <--
3125 2009 1 1161 2250593
I would like to omit the lower production gas production, and keep the higher production from the same lease.
View 3 Replies
View Related
Apr 12, 2006
I want to obtain the top 1 students' name in each grade,but this code
dosen't work:
select top 1 name
from student
group by grade
order by GPA desc
Can anyone tells me what to do?
View 6 Replies
View Related
Sep 25, 2006
I have a table stock, on the table I have company_name, stock_value, sector.
Now I want to get top five rows from this table depending on the five highest value of stock_value and for each sector. The query has to run on both oracle DB and Microsoft SQL Server with top priority MSSQL.
I will appreciate if you help me on this
jideofor
View 2 Replies
View Related
Mar 25, 2008
Hi,
I need help on this one. Let's say I have a table like this:
Table_ID Value SomeOtherTable_ID
1 2 1
2 4 1
3 1 2
4 5 3
5 3 2
6 0 1
How can I get only two rows of each SomeOtherTable_ID? The result that I want is this:
SomeOtherTable_ID Value
1 4
1 2
2 3
2 1
3 5
Thanks in advance
View 2 Replies
View Related
Mar 4, 2005
Hi there!
I've got a SPROC that generates a recordset of user vote tallies (they're calculated in a separated SPROC). The user submissions are grouped by a GUID value so as to remain unique for a user's submission (each user can have multiple submissions.
The problem is that the recordset returned displays ALL the users, and I'd like to only select the highest score for each user. So, if I have 500 submissions from 3 users (User1 and User2 submit once each and User3 submits 497 times), the total recordset will have 3 rows - being the highest score per user, discounting the others.
Here's my base query:
SELECT a.UserID,a.Name AS [Name],SUM(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID, a.Name,b.SubmissionGUID
ORDER BY [TotalPoints] DESC,[Name] ASC
...and I've been able to get the highest vote per user, discounting duplicate entries, by using this:
SELECT a.UserID,MAX(b.TotalTally) AS [TotalPoints]
FROM Users a
INNER JOIN Ballots b ON a.UserID = b.UserID
GROUP BY a.UserID
How can I write combine the two in a nested subquery to display only the top score per user?
View 1 Replies
View Related
Jul 9, 2015
I've got the following query
SELECT MAX(CountCategories.countOfCategory) AS maxCategory, CountCategories.phoneNumber, CountCategories.category
FROM
(SELECT Categories.phoneNumber, Categories.category, Count(Categories.category) AS CountOfcategory
FROM Categories
GROUP BY Categories.phoneNumber, Categories.category) AS CountCategories
GROUP BY CountCategories.phoneNumber, CountCategories.category;
As you can see I have a sub query that is counting categories for the same phoneNumber and category. I then create a Select query about this as I wanted to only return the highest result. However the above query is returning the same as the subquery.It isn't showing me the unique result for the highest count.
View 1 Replies
View Related
Sep 10, 2015
I have a query that I need to be able to pull the highest value for each row. Here is the query:
SELECT
V.VEND_ID
, V.AP_CHK_VEND_ID
, V.VEND_NOTES
, V.VEND_NAME AS VEND_NAME
, V.VEND_LONG_NAME
[Code] ....
So if someone makes a change to a vendor address, it creates a new row with the new address and a new "RowVersion" number is created. Right now this query will pull both addresses. I need it to pull the row with the highest rowversion number. The datatype for the RowVersion column is "int", and VEND_ID I believe is the unique identifier for the rowversion.
View 2 Replies
View Related
Apr 1, 2008
I haven't done any SQL in a while. Having trouble getting me head around this one.
I have a table Sales:
(Transaction_ID int, Customer_ID, Sales_DateTime Time, Sales_Amt Dollar)
I need a query that returns:
For each Customer_ID that has made a purchase
Show the highest dollar Sales_Amt they have ever made
In C++ I would probably do a recursive solution, is this doable? advisable? in T-SQL?
View 3 Replies
View Related
Apr 25, 2008
I have this query that returns the largest value in a row, but i need to know the column name that this value is in as well. any help in advance is appreciated
select clientID,
(select max(incomeValue)
from
(select earnings as incomeValue
union all
select unemployment
union all
select pensionRetirement
union all
select alimony
union all
select childSupport
union all
select dividendInterest
union all
select SS
union all
select SSI
union all
select SSDI
union all
select veteranBenefits
union all
select FIP
union all
select workStudy
union all
select other
union all
select otherHHWS) as income) as MaxIncomeValue
from tbl_income
View 12 Replies
View Related
May 8, 2007
I need to display the difference between the highest and lowest salaries. Label the column DIFFERENCE. like below:
From what I came up with is should look something like so:
<b>SQL> select * from 'salary' order by salary desc limit
select * from 'salary' order by salary desc limit 1,1 </b>
DIFFERENCE
------------------
4200
View 8 Replies
View Related
Jan 21, 2008
I have inherited a half-finished sql-server based project from a recently departed coworker. The critial point of this project is one app thread that reads barcodes, queries a single table in the database for the one record with that code as its primary key, and makes desisions based on that record. The faster that I can make that go, the better the process will run, up to a max rate as high as 20 queries per second if that were possible. I have a limited
general knowledge of sql, but very little of sql-server express.
My question is what is the best way with sql-server to maximize my single-table request rate?? On some other databases I could create an in-memory temp copy of the table with trigger events on the main table to keep the copy in sync, or I could do an initial select on the entire table to hopefully get the table into cache memory, or I could use some kind of ado-like table on the app side (but do I really gain much of anything doing this??)
With SQL server, what is my best approach to maximize my throughput under these conditions??
FYI..The c++ app uses direct odbc calls to a localhost database. Table theoretically could have 75000 ever-changing records in it. There are 5 or 6 other processes also hitting on this table, but at a far more lakadaisical (say once every 10 seconds level) rate.
View 3 Replies
View Related
Mar 25, 2008
I have a table like
TradeID ActionID
58096 3663
58096 3664
78901 2235
78901 2236
I want to select the only the TradeID with the highest ActionID
I tried using
select distinct tradeid,actionid
From
cct
Where ActionID = (SELECT MAX(ActionID)
FROM cct1
WHERE cct1.TradeID = cct.TradeID)
group by tradeid,actionid
but the result is not correct
please help
In god we trust,everything else we test.
View 4 Replies
View Related
