out of three tables in my database, I am trying to determine 1) the total number of calls made per user, 2) the total number of demos set per user and 3) the total number of those demos which were turned into sales, per user. My table layout can be found at: http://188.8.131.52/temp/tables.png
Please note: The join may not be completely correct as there are several columns in each table that relate to another table.. I will describe more below. :)
The [Appusers].Users table (u) holds all of the telemarketers. The [AppCalls].PhoneCalls table has a FK from the users table which allows us to keep track of all of the phone calls made by any given telemarketer. When the phone call record has a FK_Status_ID of 20, then the call was 'Completed'.
When an appointment is set, it saves the record in the demos (d) table. When the demo_status_id = 180, the demo was sold... The total number of records in that table related to the current user gives us the total number of demos set, per telemarketer (user).
I have been fooling wiht this query for entirely too long - it gives me CRAZY numbers! For example, there are only 800 phone calls and 73 demos in the database... This crazy query returns more than 12,000 phone calls and upwards of 5,000 demos! UGH!
Can you see the problem I am having? I have commented out parts of and mangled my query to the point that I am completely lost...
Please help! Thank you in advance, experts. :)
SELECT u.NameFirst + ' ' + u.NameLast as Telemarketer,
SUM(CASE pc.FK_Status_ID WHEN '20' THEN 1 ELSE 0 END) as [Completed Calls],
SUM(CASE d.Demo_Status_ID WHEN '180' THEN 1 ELSE 0 END) as [Demos Sold]
-- [Demos Set] should be the total number of records related to the user
FROM AppUsers.Users AS u INNER JOIN
AppCalls.PhoneCalls AS pc ON u.User_Auto_ID = pc.FK_AssignedToUser_ID INNER JOIN
AppDemos.Demos AS d ON u.User_Auto_ID = d.FK_SetBy_UserID
WHERE u.FK_App_ID = 6 AND u.FK_Office_ID = @OfficeID
GROUP BY u.NameFirst, u.NameLast
That query should return a result set like this:
Telemarketer Completed Calls Demos Sold Demos Set
Mary Jones 401 48 80
Katie Janes 540 74 104