Solved

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

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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