Query just doesn't return the right data...

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://76.12.52.157/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

Open in new window

stankstankAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Malik1947Commented:
Instead of using the Sum function did you try using the COUNT function ?

0
stankstankAuthor Commented:
>> Instead of using the Sum function did you try using the COUNT function ?<<
Every time I used it, I get a count for all rows, not per user... Having said that, the problem isn't SUM instead of count(*), is it?
0
stankstankAuthor Commented:
Regardless of whether I use SUM or COUNT, it still does not return data properly...  Something is going on with a JOIN statement or something...  There are only 3 users, around a thousand phone calls and less than 100 demos in the database.  Yet, when I run select count(*) and the FROM statement in the query, I receive a count of over 225,000 records.  This is NOT right!  Please help - I can tweak the query later, I just need to make it work now...
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

dqmqCommented:
That requires a union query.  Give this a try:
Verify that this returns the demo detail you want:
 
SELECT  u.NameFirst + ' ' + u.NameLast as Telemarketer,
     0 as [Call],
     CASE d.Demo_Status_ID WHEN '180' THEN 1 ELSE 0 END as [Sold],
     1 as [Set]
FROM  AppUsers.Users AS u
--note: not sure about your column names in following line 
--INNER JOIN AppDemos.Demos AS d ON u.User_Auto_ID = d.FK_SetBy_UserID
INNER JOIN AppDemos.Demos AS d ON u.User_Auto_ID = d.Sales_Rep_ID
WHERE	u.FK_App_ID = 6 AND u.FK_Office_ID = @OfficeID
 
 
 
Now Confirm that this returns the call detail you want:
 
SELECT  u.NameFirst + ' ' + u.NameLast as Telemarketer,
   CASE pc.FK_Status_ID WHEN '20' THEN 1 ELSE 0 END as [Call],
   0 as [Sold],
   0 as [Set]
FROM  AppUsers.Users AS u
INNER JOIN AppCalls.PhoneCalls AS pc ON u.User_Auto_ID = pc.FK_AssignedToUser_ID 
WHERE	u.FK_App_ID = 6 AND u.FK_Office_ID = @OfficeID
 
Then combine them:
SELECT Telemarketer,
  sum([Call]) as [Completed Calls],
  sum([Sold]) as [Demos Sold],
  sum([Set]) as [Demos Set]
From
(
SELECT  u.NameFirst + ' ' + u.NameLast as Telemarketer,
        u.FK_App_ID,
        u.FK_Office_ID,
     0 as [Call],
     CASE d.Demo_Status_ID WHEN '180' THEN 1 ELSE 0 END as [Sold],
     1 as [Set]
FROM  AppUsers.Users AS u
--note: not sure about your column names in following line 
--INNER JOIN AppDemos.Demos AS d ON u.User_Auto_ID = d.FK_SetBy_UserID
INNER JOIN AppDemos.Demos AS d ON u.User_Auto_ID = d.Sales_Rep_ID
UNION ALL
SELECT  u.NameFirst + ' ' + u.NameLast as Telemarketer,
        u.FK_App_ID,
        u.FK_Office_ID,
   CASE pc.FK_Status_ID WHEN '20' THEN 1 ELSE 0 END as [Call],
   0 as [Sold],
   0 as [Set]
FROM  AppUsers.Users AS u
INNER JOIN AppCalls.PhoneCalls AS pc ON u.User_Auto_ID = pc.FK_AssignedToUser_ID
) V
WHERE v.FK_App_ID = 6 AND v.FK_Office_ID = @OfficeID
GROUP BY v.Telemarketer
 
 
 
 
 
 
 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqmqCommented:
>Something is going on with a JOIN statement or something

That's right.  You get 1 row for every user call  multiplied by 1 row for every user demo.  In other words, the join has a multiplicative effect.  You want it to have an additive effect--do the calculations separately and union them together. :>)
0
stankstankAuthor Commented:
dgmg - Thank you for your post..  I ran it and after changing a few values it seems to work.  I knew the numbers were not adding up properly, but i just couldn't tell what it was!  Can you give me a term to search for regarding this?  I have a limited understanding of the multiplication effect, but I knew something was going on.

I am not at home now, I will replly with the results as to whetehr or not the query is returning accurate results when I get home in a few hours... Having said that - they look MUCH better.  Instead of reporting close to 5,000 records, now they are down to the few hundred htey are suppose to be!

Thanks again, I will reply when I have tested it thoroughally later.

StankStank
0
dqmqCommented:
Not sure what the term might be. But, I'll try to explain by example.

Suppose you have a dance with 5 girls and 5 boys.   If you just count the boys at the ance you get 5.  If you just count the girls at the dance you get 5.  However, if you try to count potential couples, then you get 25 because each boy can dance with 5 different girls.

A join between the girl table (5 rows) and the boy table (5 rows) returns 25 rows.  So, if you group by boy and count the girls each boy will have 5 and the sum will be 25.  Same if you group by girls and sum the count of boys.

But, if you count boys and girls independently, you get 5 boys and 0 girls in one table.  you get 0 boys and 5 girls in the other.  When you sum over the union of those results you get 5 each.








   
0
stankstankAuthor Commented:
I would rather just dance with the girls. :)  Thank you for your help, I understand what you are saying.  I am still unsure when to use union as not all of my joins have this result...  I guess I understand why it does it, I just don't understand when..

Thanks again for your help - the query is working perfectly!

StankStank
0
stankstankAuthor Commented:
Great answer, expert help!  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.