troubleshooting Question

Need help modifying query involving time

Avatar of YZlat
YZlatFlag for United States of America asked on
Microsoft SQL ServerSQL
3 Comments1 Solution205 ViewsLast Modified:
I have a view that returns following data:

EventTime - DateTime
NameID - Integer
Percentage - Float
Duration - Integer

and a table

Names
______

NameID - Integer
Name - Varchar

I need to build a query that retrieves data from that view where event date is from today at midnight to today's current time.


SELECT CONVERT(CHAR(8),View1.[EventTime],8) AS EventTime,
Names.Name,
AVG(View1.Percentage) AS AVERAGE_PERCENTAGE,
AVG(View1.Duration) AS AVERAGE_DURATION
FROM Names INNER JOIN View1 ON (Names.NameID = View1.NameID)
WHERE ([View1.EventTime] BETWEEN convert(datetime,convert(varchar(8),getdate(),112)+' 00:00:00.000') AND GETDATE()) AND  
AND (Names.Name 'test%')
GROUP BY View1.[EventTime], Names.Name
ORDER BY EventTime ASC, 2 ASC

Now I need to modify my query to display Average Percentage for every hour (regardless of Name).

For instance

00:00:00 - 60.0% (data from 00:00 to 00:59)

01:00:00 - 88.0% (data from 01:00 to 01:59)

02:00:00 - 67.0%

What's the simplest way to modify my query? Response time is important.

Thank you
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 3 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros