Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I get the correct count I need in my attached query?

Posted on 2013-05-24
1
Medium Priority
?
261 Views
Last Modified: 2013-05-28
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

Open in new window


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
0
Comment
Question by:rhservan
1 Comment
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39196081
Fundamentally the names with zero counts are being excluded due to INNER JOINS. That is when there are no records to count in this table [Snival_TEST].[dbo].Rn_Interactions ri because you are using an inner join to that table some names get excluded.

So you need (at least) one left join to that table, and to ensure that this continues to act as a left join the existing date range filter on that table needs to move from the where clause into the join conditions of that table.

One other point is that there simply is no point whatsoever in using 'select distinct' when you are aggregating with a  'group by', the 'group by' will produce unique rows anyway.

It may be necessary to have more than one left join, and I whilst I can't be entirely sure, I believe your query will end-up looking like this:
SELECT /* change , there is absolutely no point using distinct when also using group by */
     e.Full_Name AS Account_Mgr
   , OP.Total_Count tc
   , isnull(count(DISTINCT ri.Rn_Interactions_Id),0) rintd /* change */
FROM [Snival_TEST].[dbo].Opportunity o
INNER 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
LEFT JOIN  [Snival_TEST].[dbo].Rn_Int_Company ric   ON c.Company_id = ric.Company_id                                 /* change */
LEFT JOIN  [Snival_TEST].[dbo].Rn_Interactions ri   ON ric.Rn_Interactions_Id = ri.Rn_Interactions_Id                /* change */
                                                        AND (ri.Rn_Interaction_Date >= dateadd(day, - 30, (o.DATE))) /* change */
                                                        AND ri.Rn_Interaction_Date < dateadd(day, 0, (o.DATE))       /* change */
LEFT JOIN  [Snival_TEST].[dbo].Email em             ON ri.rn_interactions_id = em.Rn_Interactions_Id                 /* change */
                                                        AND e.employee_id = em.Assigned_To
WHERE o.DATE >= '01/01/2013' --@STARTDATE
    AND o.DATE <= '03/31/2013' --@ENDDATE
    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

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question