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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
Aged Yak Warrior

Romania
545 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
Aged Yak Warrior

Romania
545 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  
 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.05 seconds. Powered By: Snitz Forums 2000