Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2009
    Posts
    50

    Unanswered: Need assistance w/ Query

    I have a query that returns all the enteries in the database for a date range for each user that creates an entry per day.

    Code:
    SELECT RecvdBy, CallID, CustType, RecvdDate, Category 
    FROM CallLog 
    WHERE CustType = 'Customer' and NOT ( CateGory = 'Implementation Change' OR CateGory = 'NewInstall' OR CateGory = 'NOC' OR CateGory = 'PendingInstall') 
    AND (RecvdDate >= '2010-03-01' AND RecvdDate <= '2010-03-31') 
    ORDER BY RecvdDate, RecvdBy
    What I need is a count of the RecvdBy and the RecvdBy and RecvdDate fields. Something like this:

    Code:
    SELECT RecvdBy, COUNT(RecvdBY) as Total, RecvdDate 
    FROM CallLog 
    WHERE CustType = 'Customer' and NOT ( CateGory = 'Implementation Change' OR CateGory = 'NewInstall' OR CateGory = 'NOC' OR CateGory = 'PendingInstall') 
    AND (RecvdDate >= '2010-03-01' AND RecvdDate <= '2010-03-31') 
    GROUP BY RecvdBy 
    ORDER BY RecvdDate, RecvdBy
    But I get an error when I use this:

    ERROR [42000] [Microsoft][SQL Native Client][SQL Server]Column 'CallLog.CallID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Not sure how to get what I need.

  2. #2
    Join Date
    Dec 2009
    Posts
    50
    ok, I got it to work by adding the RecvdDate to the GROUP BY clause.
    Here is the output:
    afarnsworth 25 2010-03-01
    blovelady 3 2010-03-01
    dhyde 14 2010-03-01
    dkaliszczak 5 2010-03-01
    fkent 13 2010-03-01
    jskates 19 2010-03-01
    jwarren 16 2010-03-01
    kbarnum 19 2010-03-01
    kgouch 5 2010-03-01
    mareyn2 13 2010-03-01
    mfrick 1 2010-03-01
    miacca 16 2010-03-01
    rcortez 31 2010-03-01
    rferraioli 9 2010-03-01
    rmatheny 4 2010-03-01
    rschroeder 5 2010-03-01
    sahmed 19 2010-03-01
    tradnovich 12 2010-03-01
    tsarns 12 2010-03-01
    afarnsworth 15 2010-03-02

    This repeats for every day for the entire month or date range.
    I would like to know how to get a total for each individual RecvdBy(User) field for the date range instead of row for each day.
    Something along these lines:

    SELECT DISTINCT(RecvdBy) as User, COUNT(RecvdBy) as Total
    FROM CallLog
    """"""""

    Not sure if I've included enough data for anyone to assist, but I could use the help.
    Thanks in advance.
    Jim

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT RecvdBy
         , COUNT(*) as Total
      FROM CallLog 
     WHERE CustType = 'Customer' 
       and NOT CateGory IN ( 'Implementation Change' 
                           , 'NewInstall' 
                           , 'NOC' 
                           , 'PendingInstall' ) 
       AND RecvdDate >= '2010-03-01' 
       AND RecvdDate  < '2010-04-01' 
    GROUP 
        BY RecvdBy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2009
    Posts
    50
    Thanks for the assistance, it works perfect. One last question, how can I get the average instead of the count?
    Could I do:

    SELECT RecvdBy
    , AVG(COUNT(*)) as Total
    FROM CallLog

    ???
    Last edited by jbedson; 04-02-10 at 03:12.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jbedson View Post
    how can I get the average instead of the count?
    Code:
    SELECT AVG(Total) AS Average
      FROM ( SELECT RecvdBy
                  , COUNT(*) as Total
               FROM CallLog 
              WHERE CustType = 'Customer' 
                AND NOT CateGory IN ( 'Implementation Change' 
                                    , 'NewInstall' 
                                    , 'NOC' 
                                    , 'PendingInstall' ) 
                AND RecvdDate >= '2010-03-01' 
                AND RecvdDate  < '2010-04-01' 
             GROUP 
                 BY RecvdBy ) AS d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2009
    Posts
    50
    I hate to be a bother and I am very gratefull for all the assistance you've given me so far. It has been very helpfull. I know just enough SQL to get my self in trouble. But, I need the average for each unigue name in the recvdBy field for the month. when I run the query, I get the overall average and not the average for each name. I appologise if I wasn't specific enough with my explination.

    Thank you again.
    jim

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jbedson View Post
    But, I need the average for each unigue name in the recvdBy field for the month.
    the average of what?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2008
    Posts
    277
    [earlier post deleted]

    Oh wait, I see. r937's query will give you the total numbers of calls per user for the specified time period. Then I assume you want the daily average over that specified time period? Just alter his query by dividing the COUNT by number of days in the time period:
    Code:
    SELECT RecvdBy
         , COUNT(*)/(date '2010-04-01' - date '2010-03-01') as Average
      FROM CallLog 
     WHERE CustType = 'Customer' 
       and NOT CateGory IN ( 'Implementation Change' 
                           , 'NewInstall' 
                           , 'NOC' 
                           , 'PendingInstall' ) 
       AND RecvdDate >= '2010-03-01' 
       AND RecvdDate  < '2010-04-01' 
    GROUP 
        BY RecvdBy
    Last edited by futurity; 04-02-10 at 12:55.

  9. #9
    Join Date
    Dec 2009
    Posts
    50
    Some times I forget that no one else can here the conversation I'm having in my head. Sorry.
    Here is the full explination:

    This is from a Ticketing system. Calls come in to the call center and a new ticket is opened for the call. The RecvdBy field holds the name of the Tech that opened the ticket. As it is now, a report is run to show all the tickets opened by each tech and that number is entered into a spread sheet. At the end of the month, an average is generated that represents the AVG number of tickets opened per day. Since all that is needed for tracking purposes is the AVG at the end of the month, all we need to do is run the query once and enter the AVG tickets opened per day into the tracking software.

    Futurity: Your sugestion to devide the count by the number of days in the month is getting me closer to the result I need, however, each Tech will work a max of 5 days a week. If there are 4 weeks in the month, then the total should only be devided by 20, not 31 in this case. Also, because some months start in the middle of the week, the 20 could also scew the results.

    Is there a way to devide the COUNT(*) by the number of times each name shows up in the table for the time period? Also, also, some Techs work 4 days a week instead of 5, so their total enteries would only be 16.

    I hope this is more clear and to the point of what I'm looking for. Again, I apologize for not being more clear in the begining as to what my needed result was.

    Thanks again for your time
    Jim

  10. #10
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by jbedson View Post
    Is there a way to devide the COUNT(*) by the number of times each name shows up in the table for the time period? Also, also, some Techs work 4 days a week instead of 5, so their total enteries would only be 16.
    How you determine this number is highly dependent on how you're storing the techs work schedules. However, if you can guarantee that a tech will open at least one ticket a work day, then the following should work:

    Code:
    SELECT RecvdBy
         , COUNT(*)/COUNT(DISTINCT RecvdDate) as Average
      FROM CallLog 
     WHERE CustType = 'Customer' 
       and NOT CateGory IN ( 'Implementation Change' 
                           , 'NewInstall' 
                           , 'NOC' 
                           , 'PendingInstall' ) 
       AND RecvdDate >= '2010-03-01' 
       AND RecvdDate  < '2010-04-01' 
    GROUP 
        BY RecvdBy
    However, as soon as a tech goes a day without opening a ticket, this will give you wrong numbers.

  11. #11
    Join Date
    Dec 2009
    Posts
    50
    Thanks to everyone for the assistance. It is most appreciated.

    Jim

Posting Permissions

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