rhservan
asked on
How can I get the correct count I need in my attached query?
The query below:
Returns this:
Account_Mgr rintd tc
Name1 2 1
Name2 17 4
Name3 3 5
Name4 23 7
Name5 26 7
Name6 18 9
Name7 5 11
Name8 21 13
Problem: The problem is I need the query to return Namex when there is a tc count and 0 rintd count. Right now it is only returning a Name if there is a rintd count. The rintd count is filtered on the Assigned_To (line 31). If the rintd is Assigned_To someone other than the Account_Mgr it will not list the tc count.
Something like this - em.Assigned_ID <> e.employee_ID then return a 0
Here is an example of the return I am looking for:
Account_Mgr rintd tc
Name1 2 1
Name2 17 4
Name3 3 5
Name4 23 7
Name5 26 7
Name6 18 9
Name7 5 11
Name8 21 13
Name9 0 15 <--------0 rintd count with tc count
Name10 0 4 <--------0 rintd count with tc count
SELECT distinct e.Full_Name As Account_Mgr
, count (distinct ri.Rn_Interactions_Id) rintd
, OP.Total_Count tc
FROM [Snival_TEST].[dbo].Opportunity o
JOIN (
SELECT
opp.Account_Manager_Id
, COUNT(*) as total_count
FROM Snival_TEST..Opportunity opp
INNER JOIN Snival_TEST..Employee e ON opp.Account_Manager_Id = e.Employee_Id
INNER JOIN Snival_TEST..Line_of_Business lob ON e.cfLine_of_Business_Id = lob.Line_of_Business_Id
INNER JOIN Snival_TEST..Company_Code cc ON e.cfCompany_Code_Id = cc.Company_Code_Id
INNER JOIN Snival_TEST..ctService_Center sc ON opp.cfService_Center_Id = sc.ctService_Center_Id
WHERE opp.Date >= '01/01/2013' --@STARTDATE
AND opp.Date < '03/31/2013' --@ENDDATE
AND ISNULL(opp.Completion, 0) = 0
AND ISNULL(opp.NO_QUOTE, '0') IN ('No', '0')
AND ISNULL(opp.WARRANTY, 'No') = 'No'
AND opp.Status =4
AND lob.Line_of_Business_Name = 'SID'
AND sc.Service_Center_Code IN ('rst', q5)
GROUP BY
opp.Account_Manager_Id
) as OP ON o.Account_Manager_Id = OP.Account_Manager_Id
INNER JOIN Snival_TEST.[dbo].Employee e ON o.Account_Manager_Id = e.Employee_Id
INNER JOIN Snival_TEST.[dbo].Line_of_Business lob ON e.cfLine_of_Business_Id = lob.Line_of_Business_Id
INNER JOIN Snival_TEST.[dbo].ctService_Center sc ON o.cfService_Center_Id = sc.ctService_Center_Id
INNER JOIN [Snival_TEST].[dbo].[Company] c ON o.Company_Id = c.Company_Id
INNER JOIN [Snival_TEST].[dbo].Rn_Int_Company ric ON ric.Company_id = c.Company_id
INNER JOIN [Snival_TEST].[dbo].Rn_Interactions ri ON ric.Rn_Interactions_Id = ri.Rn_Interactions_Id
INNER JOIN [Snival_TEST].[dbo].Email em ON em.Rn_Interactions_Id = ri.rn_interactions_id AND em.Assigned_To = e.employee_id
WHERE o.Date >= '01/01/2013' --@STARTDATE
AND o.Date <= '03/31/2013' --@ENDDATE
AND ( ri.Rn_Interaction_Date >= dateadd(day, -30 ,(o.Date) ))
AND ri.Rn_Interaction_Date < dateadd(day, 0 ,(o.Date) )
AND ISNULL(o.Completion, 0) = 0
AND ISNULL(o.NO_QUOTE, '0') IN ('No', '0')
AND ISNULL(o.WARRANTY, 'No') = 'No'
AND o.Status =4
AND e.Active=1
AND lob.Line_of_Business_Name = 'SID'
AND sc.Service_Center_Code IN ('rst', q5)
Group by e.Full_Name,OP.Total_Count
Returns this:
Account_Mgr rintd tc
Name1 2 1
Name2 17 4
Name3 3 5
Name4 23 7
Name5 26 7
Name6 18 9
Name7 5 11
Name8 21 13
Problem: The problem is I need the query to return Namex when there is a tc count and 0 rintd count. Right now it is only returning a Name if there is a rintd count. The rintd count is filtered on the Assigned_To (line 31). If the rintd is Assigned_To someone other than the Account_Mgr it will not list the tc count.
Something like this - em.Assigned_ID <> e.employee_ID then return a 0
Here is an example of the return I am looking for:
Account_Mgr rintd tc
Name1 2 1
Name2 17 4
Name3 3 5
Name4 23 7
Name5 26 7
Name6 18 9
Name7 5 11
Name8 21 13
Name9 0 15 <--------0 rintd count with tc count
Name10 0 4 <--------0 rintd count with tc count
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.