Solved

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

Posted on 2013-05-24
1
252 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 48

Accepted Solution

by:
PortletPaul earned 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

777 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