Solved

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

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now