Solved

In the subquery below how can I build it to filter on account manager.

Posted on 2013-05-13
3
281 Views
Last Modified: 2013-05-14
Current:
The main query below works well. It provides Counts by names as needed.
There has been a subquery added in the select statement which provides a single count value.  I don't use the Full_Name column.

NAME                    Q       TotalCount  
Name1                 6                       540
Name2                 16                     540
Name3                 3                       540
Name4                 14                     540
Name5                  1                      540

Problem:
The TotalCount column is just a total and does not breakout the individual values by name I would like the subquery count, TotalCount, to filter on names so each name has an individual count as in the main query.  Can this be done or is there a better way?

Desired Output:

NAME                     Q        TotalCount    
Name1                 6                       90
Name2                 16                     23
Name3                 3                       37
Name4                 14                     22
Name5                  1                      100
Name6                   -                      42
Name7                 10                     69
Name8                   -                      74
Name9                   2                     83

select 
e.Name_ as [Name],E.Full_Name,count(distinct o.Quote_Number)Q,
TotalCount=(SELECT Count(o.quote_Number) FROM SnivalTest_ED..Opportunity o)
from SnivalTest_ED..Opportunity o
	inner join SnivalTest_ED..Employee e
		on o.Account_Manager_Id = e.Employee_Id
	inner join SnivalTest_ED..ctLine_of_Business lob
		on e.Line_of_Business_Id = lob.ctLine_of_Business_Id
	inner join SnivalTest_ED..ctCompany_Code cc
		on e.Company_Code_Id = cc.ctCompany_Code_Id
	inner join @TempListCompanyCode tcc
		on cc.company_code_name = tcc.CompanyCode
	inner join @TempListLOB tlob
		on lob.line_of_business_name = tlob.lob
where o.Status = 4 -- Won
                and e.Name_ = isnull(@Employee, e.Name_)
                and o.Won_Date >= @STARTDATE
                and o.Won_Date <= @ENDDATE
                and ISNULL(o.Completion,0) = 0
                AND ISNULL(o.NO_QUOTE___T_AND_M,'0') IN ('No','0')
                AND ISNULL(o.WARRANTY,'No') = 'No'
group by e.Name_,E.Full_Name
order by e.Name_

Open in new window

0
Comment
Question by:rhservan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 39163029
"...Problem:
The TotalCount column is just a total and does not breakout the individual values by name ..."


You may want to build a new function that returns name beside total - maybe you need to pass in two parameters not just o.quote_Number then do the GROUP BY later in the statement.
...
TotalCount=(SELECT Count(o.quote_Number) FROM SnivalTest_ED..Opportunity o)
...

I also suggest look at and try to use CTE structures.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39163528
assuming the total_count is of all (i.e. not to be filtered via the where clause) then I'd suggest a slightly different approach by joining to a grouped summary like this:
SELECT
      e.Name_ AS [Name]
    , E.Full_Name
    , count(DISTINCT o.Quote_Number) Q
    , OP.TotalCount
FROM SnivalTest_ED..Opportunity o
INNER JOIN (
            SELECT
                  opp.Account_Manager_Id
                , count(*) as total_count
            FROM SnivalTest_ED..Opportunity opp
            GROUP BY
                  opp.Account_Manager_Id
            ) as OP                              ON o.Account_Manager_Id = OP.Account_Manager_Id
INNER JOIN SnivalTest_ED..Employee e             ON o.Account_Manager_Id = e.Employee_Id
INNER JOIN SnivalTest_ED..ctLine_of_Business lob ON e.Line_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN SnivalTest_ED..ctCompany_Code cc      ON e.Company_Code_Id = cc.ctCompany_Code_Id
INNER JOIN @TempListCompanyCode tcc              ON cc.company_code_name = tcc.CompanyCode
INNER JOIN @TempListLOB tlob                     ON lob.line_of_business_name = tlob.lob
WHERE o.STATUS = 4 -- Won
    AND e.Name_ = isnull(@Employee, e.Name_)
    AND o.Won_Date >= @STARTDATE
    AND o.Won_Date <= @ENDDATE
    AND ISNULL(o.Completion, 0) = 0
    AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
    AND ISNULL(o.WARRANTY, 'No') = 'No'
GROUP BY
      e.Name_
    , E.Full_Name
ORDER BY e.Name_

Open in new window

and, if filtering for this count  is required (e..g a date range) then a where clause may be included into that subquery.

I notice you are using >= with <= as your date range filter, this might mean you are using a construct like this:

    AND o.Won_Date >= '2012-01-01'
    AND o.Won_Date <= '2012-12-31' -- the last day of the period to be included

If this is how you are doing it now, it's actually easier in the vast majority of cases to to this instead:

    AND o.Won_Date >= '2012-01-01'
    AND o.Won_Date <   '2013-01-01' --the beginning of the period to be excluded

The overall result might look something like this:
declare @STARTDATE datetime, @ENDDATE datetime
set @STARTDATE = '2012-01-01'
set @ENDDATE   = '2013-01-01'

SELECT
      e.Name_ AS [Name]
    , E.Full_Name
    , count(DISTINCT o.Quote_Number) Q
    , OP.TotalCount
FROM SnivalTest_ED..Opportunity o
INNER JOIN (
            SELECT
                  opp.Account_Manager_Id
                , count(*) as total_count
            FROM SnivalTest_ED..Opportunity opp
/* nb: SOME date field - not sure which */            
            WHERE o.SOME_Date >= @STARTDATE
            AND   o.SOME_Date <  @ENDDATE -- first day of period to be excluded
            GROUP BY
                  opp.Account_Manager_Id
            ) as OP                              ON o.Account_Manager_Id = OP.Account_Manager_Id
INNER JOIN SnivalTest_ED..Employee e             ON o.Account_Manager_Id = e.Employee_Id
INNER JOIN SnivalTest_ED..ctLine_of_Business lob ON e.Line_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN SnivalTest_ED..ctCompany_Code cc      ON e.Company_Code_Id = cc.ctCompany_Code_Id
INNER JOIN @TempListCompanyCode tcc              ON cc.company_code_name = tcc.CompanyCode
INNER JOIN @TempListLOB tlob                     ON lob.line_of_business_name = tlob.lob
WHERE o.STATUS = 4 -- Won
    AND e.Name_ = isnull(@Employee, e.Name_)
    AND o.Won_Date >= @STARTDATE
    AND o.Won_Date <  @ENDDATE -- first day of period to be excluded
    AND ISNULL(o.Completion, 0) = 0
    AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
    AND ISNULL(o.WARRANTY, 'No') = 'No'
GROUP BY
      e.Name_
    , E.Full_Name
ORDER BY e.Name_

Open in new window

for reasons why I recommend the date boundaries as you see here please see: "Beware of Between"
0
 
LVL 32

Expert Comment

by:awking00
ID: 39165216
Can you provide the sample data that should produce your desired results?
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

724 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