Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2014
    Posts
    1

    Unanswered: Trying to output the avg stats over a weekly period for 6 months

    Hello, I am working with SQL 2008

    I have the following code which works and outputs the desired results, however I am attempting to figure out a way to get this output by week starting from January 5th to July 6th without having to manually change the date for each week and running again. Ideally I would like to be able to save the results to Excel separated by server by week.

    Code:
    SELECT AVG(cast(StatValue as float))as 'CPU Utilization', StatName, ServerName as 'Server Name', from dbo.ServerStats s
    WHERE s.StatName IN ('MEMPTAGE-TOTAL',
    'CPUPEAKP-TOTAL',
    'DISKPERC-C',
    'DISKPERC-F',
    'DISKPERC-G',
    'DISKPERC-H',
    'DISKPERC-I',
    'DISKPERC-L') and
    s.ServerName IN ('SE441600',
    'SE441601',
    'SE441602',
    'SE441603',
    'SE441604',
    'SE441605',
    'SE441606',
    'SE441607',
    'SE441608',
    'SE441610',
    'SE441611',
    'SE441612',
    'SE441613',
    'SE441614',
    'SE441615',
    'SE441617',
    'SE441618',
    'SE441619',
    'SE441620',
    'SE441621',
    'SE441622',
    'SE441623',
    'SE441624',
    'SE441625',
    'SE441626',
    'SE441627',
    'SE441628',
    'SE441629',
    'SE441630',
    'SE441631',
    'SE441632',
    'SE441633',
    'SE441634',
    'SE441635',
    'SE441636',
    'SE441637',
    'SE441638',
    'SE441639',
    'SE441640')
     and
    s.BCDate between '2014-06-29 00:00:01.000' AND '2014-07-05 23:59:59.999' 
    GROUP BY s.StatName, s.ServerName ORDER BY s.ServerName ASC, s.StatName
    Warning: I am by no means familiar with using SQL. It took me a week just get the above code.

    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This formula will give you the week for any given day
    Code:
    select	dateadd(week, datediff(week, 0, GETDATE()), 0)
    Note, you might need to adjust it depending on when you want your weeks to start...

    You can then group by this value to average your stats.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •