[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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

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
rhservan
Asked:
rhservan
1 Solution
 
lcohanDatabase AnalystCommented:
"...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
 
PortletPaulCommented:
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
 
awking00Commented:
Can you provide the sample data that should produce your desired results?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now