KenWidmaier
asked on
Sql Server 2008r2 select distinct date from datetime
I have a view that im trying to do a select statement from, the set looks something like this.
ComputerName Counter Counter Instance Value Time(UTC)
SPITERMINAL1 Free Megabytes C: 7272 2015-11-19 00:00:00.000
NAWSRAP033894 Free Megabytes D: 3723 2015-11-19 00:03:00.000
NAWSRAP033894 Free Megabytes C: 4298 2015-11-19 00:03:00.000
aasldsrv10 Free Megabytes X: -999 2015-11-19 00:03:00.000
aasldsrv10 Free Megabytes W: -999 2015-11-19 00:03:00.000
Solomon Free Megabytes F: 25491 2015-11-19 00:03:00.000
BSCVHOSTDC Free Megabytes E: 40796 2015-11-19 00:03:00.000
BSCVHOSTDC Free Megabytes C: 21859 2015-11-19 00:03:00.000
usccgm-dev Free Megabytes C: 22279 2015-11-19 00:03:00.000
USCCSCANSAFE Free Megabytes C: 2490 2015-11-19 00:00:00.000
What i want is the minimum value for each counter instance on computername for each day. I am new to SQL any help is greatly appriciated. My current script looks like this:
select top 1000 [ComputerName] As 'Computername'
,[Counter Instance]
,min([Value]) As 'Value'
,(SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, [Time(UTC)]))) AS [Time(UTC)]
from [viewReportMonitorCounterL og]
where counter = 'Free megabytes' and [Value] > 1
group by [ComputerName], [Counter Instance], Value, [Time(UTC)]
ComputerName Counter Counter Instance Value Time(UTC)
SPITERMINAL1 Free Megabytes C: 7272 2015-11-19 00:00:00.000
NAWSRAP033894 Free Megabytes D: 3723 2015-11-19 00:03:00.000
NAWSRAP033894 Free Megabytes C: 4298 2015-11-19 00:03:00.000
aasldsrv10 Free Megabytes X: -999 2015-11-19 00:03:00.000
aasldsrv10 Free Megabytes W: -999 2015-11-19 00:03:00.000
Solomon Free Megabytes F: 25491 2015-11-19 00:03:00.000
BSCVHOSTDC Free Megabytes E: 40796 2015-11-19 00:03:00.000
BSCVHOSTDC Free Megabytes C: 21859 2015-11-19 00:03:00.000
usccgm-dev Free Megabytes C: 22279 2015-11-19 00:03:00.000
USCCSCANSAFE Free Megabytes C: 2490 2015-11-19 00:00:00.000
What i want is the minimum value for each counter instance on computername for each day. I am new to SQL any help is greatly appriciated. My current script looks like this:
select top 1000 [ComputerName] As 'Computername'
,[Counter Instance]
,min([Value]) As 'Value'
,(SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, [Time(UTC)]))) AS [Time(UTC)]
from [viewReportMonitorCounterL
where counter = 'Free megabytes' and [Value] > 1
group by [ComputerName], [Counter Instance], Value, [Time(UTC)]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
KenWidmaier, do you still need help with this question?