SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How can I get the correct count I need in query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rservant
Starting Member

USA
1 Posts

Posted - 05/25/2013 :  01:30:57  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

Romania
278 Posts

Posted - 05/25/2013 :  03:41:13  Show Profile  Reply with Quote
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
Go to Top of Page

stepson
Constraint Violating Yak Guru

Romania
278 Posts

Posted - 05/25/2013 :  08:38:36  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000