Solved

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

Posted on 2013-05-13
3
263 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
3 Comments
 
LVL 39

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 48

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

932 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

10 Experts available now in Live!

Get 1:1 Help Now