Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: Calculating weekly averages...

    Hi. I want to be able to calculate weekly averages of our report data. Here is a sample from our EmplyeeTrends table:

    Code:
    EmployeeID   ReportDate   TotalCases
    2            1/1/2007     77
    2            1/2/2007     63
    3            1/1/2007     56
    All the report data is produced daily. However, my boss wants to be able to see weekly/monthly averages for each employee. Is this possible? Thanks so much for your help!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select datepart(wk,ReportDate) as week
         , avg(TotalCases)
      from daTable
    group
        by datepart(wk,ReportDate)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yeah, but that will group weeks from different years together. I'd suggest grouping by year as well, or using this:
    Code:
    select	dateadd(wk, datediff(wk, 0, ReportDate), 0) as week,
    	avg(TotalCases)
    from	daTable
    group by dateadd(wk, datediff(wk, 0, ReportDate), 0)
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's gorgeous, nice one blindman

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2005
    Posts
    165
    thanks so much blindman and r937! those solutions work perfectly!

    is it possible to find the average of the TotalCases by week as well so i can see the entire office's total cases? Does that make any sense? Thanks for your help!
    Last edited by bla4free; 11-09-07 at 13:16.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, it is possible, but not advisable. You should not take averages of subtotals, as it skews your results. Weeks with fewer entries will be weighted more heavily than weeks with more entries.
    It is best just to take an average across the entire office.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Jan 2005
    Posts
    165
    i'll do that then. thanks so much for the advice!

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by bla4free
    i'll do that then. thanks so much for the advice!
    wow, you don't see that very often around here....
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    they're a heartless bunch, users, but the occasional good one like bla4free is why we do this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Speak for yourself, Rudy...I do it simply for the chicks. Chicks dig database nerds.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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