sql server programming: Grouping performance monitor time data after pivoting help please -- sql server programming

Re: Grouping performance monitor time data after pivoting help please

togbabe
6/13/2006 9:48:46 PM

Mike, I have already pivotted the result set. That is not what I need
to know. In this post I am asking how to group the random time results
to fixed increments. Say every hour over two weeks or ever minute over
a day etc.

[quoted text, click to view]

Re: Grouping performance monitor time data after pivoting help please

Mike Hodgson
6/14/2006 12:00:00 AM

Oops, sorry (that'll teach me not to read the question properly). OK,
then you just need the grouping column to be stripped of the data you're
not interested in (hours, minutes, seconds, etc.). For example, if you
want hourly averages you can say (untested):

SELECT
DATEADD(hh,DATEDIFF(hh,0,CounterDateTime),0) AS HourlyInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY DATEADD(hh,DATEDIFF(hh,0,CounterDateTime),0)

If you wanted the intervals to be every minute just change the "hh" in
the datetime functions to "mi" (untested):

SELECT
DATEADD(mi,DATEDIFF(mi,0,CounterDateTime),0) AS MinuteInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY DATEADD(mi,DATEDIFF(mi,0,CounterDateTime),0)

If you use too small an interval eventually the DATEDIFF() function will
fail with an overflow (it'll try to return a number too big for an int)
so you might want to pick some arbitrary base date instead of 0, like
(untested):

SELECT

DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')
AS SecondInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY
DATEADD(ss,DATEDIFF(ss,'2006-01-01',CounterDateTime),'2006-01-01')

Hope this helps.

--
*mike hodgson*
http://sqlnerd.blogspot.com

[quoted text, click to view]

Re: Grouping performance monitor time data after pivoting help please

Hugo Kornelis
6/14/2006 12:00:00 AM

[quoted text, click to view]

(snip)
[quoted text, click to view]

Hi Mike,

And to get the 5-minute intervals "togbabe" originally requested, throw
some integer division into the equation:

SELECT
DATEADD(mi,
5 * ((DATEDIFF(mi,0,CounterDateTime) / 5),
0) AS FiveMinuteInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY DATEDIFF(mi,0,CounterDateTime) / 5

[quoted text, click to view]

Recommend format for dates is yyyymmdd, without the dashes. With the
dashes added, it is no longer guaranteed to be unambiguous under all
possible locale settings. (Not that it matters much when both date and
month are 1 anyway <g>).

--

Re: Grouping performance monitor time data after pivoting help please

Mike Hodgson
6/14/2006 12:00:00 AM

OK, so I still didn't read the original question
properly...<blush>...well, I'm a busy man. Thanks for the addition Hugo
(and thanks for the comment on my blog, I'll update it as soon as I get
a minute - it was written some time ago and I've learnt a few tricks
since then).

--
*mike hodgson*
http://sqlnerd.blogspot.com

[quoted text, click to view]

Re: Grouping performance monitor time data after pivoting help please

Mike Hodgson
6/14/2006 12:00:00 AM

How to pivot a resultset is a pretty commonly requested thing in T-SQL.
So I don't have to rehash it again (OK, I'm lazy...sometimes) I refer
you to Aaron Bertrand's article on the subject on aspfaq.com:
http://www.aspfaq.com/show.asp?id=2462

--
*mike hodgson*
http://sqlnerd.blogspot.com

[quoted text, click to view]

Re: Grouping performance monitor time data after pivoting help please

togbabe
6/14/2006 5:39:33 PM

Thanks mate. This is all good. I have implemented this and it is
working as a general mechanism for displaying my Performance Monitor
data. This is much better than using the performance monitor because
you can take readings for every minute over long periods of time and
change the granularity of the data over longer or shorter periods by
simply changing the 'datepart' argument. You can also archive
extracted data for 'long term trend analysis. Now, this is great and
thanks for that. One thing though, the date functions only allow
intervals of seconds, minutes, hours and days etc. Is there any way
anybody can think of of aggregating across time intervals of 10 minutes
or - say - 5 seconds etc. I believe that this can't be done with
the 'date functions.'
Thanks Mike for you solution.

[quoted text, click to view]

Re: Grouping performance monitor time data after pivoting help please

Mike Hodgson
6/15/2006 12:00:00 AM

Yep - Hugo answered that in his post in this thread:

<quote>

And to get the 5-minute intervals "togbabe" originally requested, throw
some integer division into the equation:

SELECT
DATEADD(mi,
5 * ((DATEDIFF(mi,0,CounterDateTime) / 5),
0) AS FiveMinuteInterval,
AVG(Server1) AS Server1Avg,
AVG(Server2) as Server2Avg
FROM MyTable
GROUP BY DATEDIFF(mi,0,CounterDateTime) / 5

</quote>

--
*mike hodgson*
http://sqlnerd.blogspot.com

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.