How can I get the correct count I need in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How can I get the correct count I need in query

Author

Topic

rservant
Starting Member

1 Post

Posted - 2013-05-25 : 01:30:57

The query below:

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

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-05-25 : 03:41:13

Hi

You have a Inner Join

INNER JOIN [Snival_TEST].[dbo].Rn_Interactions ri ON ric.Rn_Interactions_Id = ri.Rn_Interactions_Id

try to change to a LEFT JOIN
and use for

count (distinct ri.Rn_Interactions_Id) rintd

a COALESCE like this :

 COALESCE(count (distinct ri.Rn_Interactions_Id),0) as rintd

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-05-25 : 08:38:36

and another thing


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) )
.
.
.

I change to


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)) 
 OR ri.Rn_Interaction_Date IS NULL)
.
.
.

the main idee is to change some INNER JOIN to LEFT JOIN
and in the WHERE Condition keep in mind that ri.Rn_Interaction_Date could be Null

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources