SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select to row to colum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nonsec
Starting Member

26 Posts

Posted - 10/02/2013 :  15:30:39  Show Profile  Reply with Quote
Hi everyone;
I have a column name called countername it has different values like;
% Free Space
Free Megabytes
Avg. Disk sec/Transfer

What i am trying to do place them next to each other in the query;
Here is my query;


SELECT  
dbo.vManagedEntity.Path,
dbo.vPerformanceRuleInstance.InstanceName,
Perf.vPerfHourly.AverageValue,
Perf.vPerfHourly.DateTime,
dbo.vManagedEntity.DisplayName,
dbo.vPerformanceRule.ObjectName,
dbo.vPerformanceRule.CounterName,   
dbo.vManagedEntity.Name   

FROM        
Perf.vPerfHourly INNER JOIN dbo.vPerformanceRuleInstance ON Perf.vPerfHourly.PerformanceRuleInstanceRowId = dbo.vPerformanceRuleInstance.PerformanceRuleInstanceRowId INNER JOIN dbo.vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = dbo.vManagedEntity.ManagedEntityRowId INNER JOIN dbo.vPerformanceRule ON dbo.vPerformanceRuleInstance.RuleRowId = dbo.vPerformanceRule.RuleRowId
WHERE     (dbo.vPerformanceRule.ObjectName = 'Logicaldisk') and CounterName like '%Free%' 


Here is the output;

Path	InstanceName	AverageValue	DateTime	DisplayName	ObjectName	CounterName	Name
MYServerName.abc.com	L:	94.51095581	00:00.0	Cluster Disk 4 L-Drive Logs	LogicalDisk	% Free Space	Cluster Disk 4 L-Drive Logs
MYServerName.abc.com	L:	94.51095581	00:00.0	Cluster Disk 4 L-Drive Logs	LogicalDisk	% Free Space	Cluster Disk 4 L-Drive Logs
MYServerName.abc.com	L:	157648	00:00.0	Cluster Disk 4 L-Drive Logs	LogicalDisk	Free Megabytes	Cluster Disk 4 L-Drive Logs
MYServerName.abc.com	L:	157648	00:00.0	Cluster Disk 4 L-Drive Logs	LogicalDisk	Free Megabytes	Cluster Disk 4 L-Drive Logs


What i need is something like this;

Path	          InstanceName	 Free Megabytes	 % Free Space
MYServerName.abc.com	   L:	     5000	          75


where Free megabytes and % Free Space is next to each other.

any input greatly appreciated.
Thank you.

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/02/2013 :  15:45:43  Show Profile  Reply with Quote
Something like this. However, the aggregate function may be sum or average (or something else) depending on the data you are working with. For cases where the SUM function is to be used, the calculation would be correct. But for average this would not give you the correct results unless all drives happened to have the same total space. So you will need to calculate the total space and available free space for each drive and then take the ratio of the sums to find the average free space.
SELECT
	[Path],
	[InstanceName],
	SUM(CASE WHEN CounterName = 'Free Megabytes' THEN AverageValue END) AS [Free Megabytes],
	AVG(CASE WHEN CounterName = '% Free Space' THEN AverageValue END) AS [%Free Space]
FROM
(
	SELECT  dbo.vManagedEntity.Path ,
			dbo.vPerformanceRuleInstance.InstanceName ,
			Perf.vPerfHourly.AverageValue ,
			Perf.vPerfHourly.DateTime ,
			dbo.vManagedEntity.DisplayName ,
			dbo.vPerformanceRule.ObjectName ,
			dbo.vPerformanceRule.CounterName ,
			dbo.vManagedEntity.Name
	FROM    Perf.vPerfHourly
			INNER JOIN dbo.vPerformanceRuleInstance ON Perf.vPerfHourly.PerformanceRuleInstanceRowId = dbo.vPerformanceRuleInstance.PerformanceRuleInstanceRowId
			INNER JOIN dbo.vManagedEntity ON Perf.vPerfHourly.ManagedEntityRowId = dbo.vManagedEntity.ManagedEntityRowId
			INNER JOIN dbo.vPerformanceRule ON dbo.vPerformanceRuleInstance.RuleRowId = dbo.vPerformanceRule.RuleRowId
	WHERE   ( dbo.vPerformanceRule.ObjectName = 'Logicaldisk' )
			AND CounterName LIKE '%Free%' 
)s
GROUP BY 
	[Path],
	[InstanceName]
Go to Top of Page

nonsec
Starting Member

26 Posts

Posted - 10/02/2013 :  15:59:55  Show Profile  Reply with Quote
Thank you James. I appreciate the query but i don`t want to use any aggregation just need one sample of the exiting value. The results are pretty much duplicate of every hours. Is there a way that i can select once distinct value for each InstanceName in that query without doing any aggregation?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/02/2013 :  16:13:40  Show Profile  Reply with Quote
If you change the aggregate function to MAX (or MIN) that should give you one of the values. It may not pick the values corresponding to the same time stamp if you do it that way, but if the values are duplicates, that shouldn't matter.
Go to Top of Page

nonsec
Starting Member

26 Posts

Posted - 10/03/2013 :  14:41:37  Show Profile  Reply with Quote
Thank you very much.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000