?
Solved

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

Posted on 2013-05-13
3
Medium Priority
?
284 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 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 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