Solved

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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
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.

861 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

20 Experts available now in Live!

Get 1:1 Help Now