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 [viewReportMonitorCounterLog]
                 where counter = 'Free megabytes' and [Value] > 1
group by  [ComputerName], [Counter Instance], Value, [Time(UTC)]
KenWidmaierAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
WITH cteCounter AS
(
   SELECT CouputerName, Counter, [Counter Instance], Value, ]Time(UTC)],
      ROW_NUMBER() OVER (PARTITION BY ComputerName, Counter ORDER BY Value) AS RowNumber
   FROM viewReportMonitorCounterLog
)
SELECT *
FROM cteCounter
WHERE RowNumber = 1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
I think you are trying to ask for "how to remove the time portion of datetime data"

There are these methods to do that:

cast([datetime_column] as date)
convert(date,[datetime_coulmn])
dateadd(day, datediff(day,0,[datetime_column]) ,0 ) --<< for any version of sql server

e.g.
SELECT TOP 1000
      [ComputerName] AS 'Computername'
    , [Counter Instance]
    , CAST([Time(UTC)] AS date) AS [Time(UTC)]
    , MIN([Value]) AS 'Value'
FROM [viewReportMonitorCounterLog]
WHERE counter = 'Free megabytes'
      AND [Value] > 1
GROUP BY
      [ComputerName]
    , [Counter Instance]
    , CAST([Time(UTC)] AS date)

Open in new window

Also note that you should not group by [value] if you are looking for MIN([value])

the group by clause needs all columns EXCEPT those using aggregate functions such as MIN/MAX/SUM/AVG/COUNT
Vitor MontalvãoMSSQL Senior EngineerCommented:
KenWidmaier, do you still need help with this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.