Need assistance w/ Query

jbedson · #1 (**permalink**) 04-01-10, 21:55

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.

jbedson · #2 (**permalink**) 04-01-10, 22:22

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

r937 · #3 (**permalink**) 04-01-10, 22:53

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

jbedson · #4 (**permalink**) 04-02-10, 02:04

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

???

r937 · #5 (**permalink**) 04-02-10, 03:49

Quote:

Originally Posted by jbedson

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

jbedson · #6 (**permalink**) 04-02-10, 04:44

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

r937 · #7 (**permalink**) 04-02-10, 06:44

Quote:

Originally Posted by jbedson

But, I need the average for each unigue name in the recvdBy field for the month.

the average of what?

futurity · #8 (**permalink**) 04-02-10, 11:35

[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

jbedson · #9 (**permalink**) 04-02-10, 22:49

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

futurity · #10 (**permalink**) 04-07-10, 11:26

Quote:

Originally Posted by jbedson

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.