Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

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 needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

618 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