Solved

What is wrong with my UNION?

Posted on 2013-05-16
4
310 Views
Last Modified: 2013-05-16
This code
SELECT
    , e.cfFull_Name
    , OP.Total_Count tc
    , COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
FROM Snival..SFA_Opportunity o
INNER JOIN (
            SELECT
                  opp.Account_Manager_Id
                , count(*) as total_count
            FROM Snival..SFA_Opportunity opp
            WHERE opp.cfWon_Date >= @STARTDATE
            AND   opp.cfWon_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 Snival..Employee e             ON o.Account_Manager_Id = e.Employee_Id  
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc      ON e.cfCompany_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
INNER JOIN Snival..Company	co			  ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric	  ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri	  ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Email em	              ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND EM.Assigned_To = E.Employee_Id AND EM.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
    AND e.Login_id = isnull(@Employee, e.Login_id)
    AND o.cfWon_Date >= @STARTDATE
    AND o.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
    AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0,  o.cfWon_Date ), 0) )
    AND ri.Pq_Meeting_Completed_Flag = 1
    AND ISNULL(o.cfGreen_Completion, 0) = 0
    AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
    AND ISNULL(o.WARRANTY, 'No') = 'No'
GROUP BY
     , e.cfFull_Name
    , OP.Total_Count

Open in new window


Returns this:

cfFullName                       tc                  rintd

Name_1                             15                     6
Name_2                             21                     11
Name_3                             17                     18
Name_4                             30                     1
Name_5                             19                     45
Name_6                             32                     9

Which works as it should.

However,  when I perform a Union, no duplicates, a problem develops, here is the UNION:

SELECT
      e.cfFull_Name
    , COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
    , OP.Total_Count tc
FROM Snival..SFA_Opportunity o
INNER JOIN (
            SELECT
                  opp.Account_Manager_Id
                , count(*) as total_count
            FROM Snival..SFA_Opportunity opp
            WHERE opp.cfWon_Date >= @STARTDATE
            AND   opp.cfWon_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 Snival..Employee e             ON o.Account_Manager_Id = e.Employee_Id  
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc      ON e.cfCompany_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
INNER JOIN Snival..Company	co	  ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric	  ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri	  ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Email em	          ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND EM.Assigned_To = E.Employee_Id AND EM.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
    AND e.Login_id = isnull(@Employee, e.Login_id)
    AND o.cfWon_Date >= @STARTDATE
    AND o.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
    AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0,  o.cfWon_Date ), 0) )
    AND ri.Pq_Meeting_Completed_Flag = 1
    AND ISNULL(o.cfGreen_Completion, 0) = 0
    AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
    AND ISNULL(o.WARRANTY, 'No') = 'No'
GROUP BY
    E.cfFull_Name
    , OP.Total_Count

 
 UNION
 
 
 SELECT  
     e.cfFull_Name
    , COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
    , OP.Total_Count tc
FROM Snival..SFA_Opportunity o
INNER JOIN (
            SELECT
                  opp.Account_Manager_Id
                , count(*) as total_count
            FROM Snival..SFA_Opportunity opp
            WHERE opp.cfWon_Date >= @STARTDATE
            AND   opp.cfWon_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 Snival..Employee e             ON o.Account_Manager_Id = e.Employee_Id  
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc      ON e.cfCompany_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
INNER JOIN Snival..Company	co        ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric	  ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri	  ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Call_ em	          ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND em.Assigned_To = E.Employee_Id AND em.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
    AND e.Login_id = isnull(@Employee, e.Login_id)
    AND o.cfWon_Date >= @STARTDATE
    AND o.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
    AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0,  o.cfWon_Date ), 0) )
    AND ri.Pq_Meeting_Completed_Flag = 1
    AND ISNULL(o.cfGreen_Completion, 0) = 0
    AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
    AND ISNULL(o.WARRANTY, 'No') = 'No'
    GROUP BY
    E.cfFull_Name
    , OP.total_count

Open in new window


Returns this:
cfFullName                       tc                  rintd

Name_1                             15                     6
Name_2                             21                     11
Name_3                             17                     18
Name_3                             17                     1
Name_4                             30                     1
Name_5                             19                     45
Name_5                             19                      1
Name_6                             32                     9

Note the duplicate names at Name_3 & Name_5 & duplicate tc and just a one rintd.
The only change made between the two queries is line 24 on the first query and Line 65 on the second query. My expectations would be no duplicate FullName, no duplicate tc, and the rintd count column would be added, like on Name_5 45 +1 would be 46.

What went wrong?
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
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39172715
union will not do the sum, instead you have to write a sum on the whole result set as below

;with CTE
AS
(
SELECT
      e.cfFull_Name
    , COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
    , OP.Total_Count tc
FROM Snival..SFA_Opportunity o
INNER JOIN (
            SELECT
                  opp.Account_Manager_Id
                , count(*) as total_count
            FROM Snival..SFA_Opportunity opp
            WHERE opp.cfWon_Date >= @STARTDATE
            AND   opp.cfWon_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 Snival..Employee e             ON o.Account_Manager_Id = e.Employee_Id  
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc      ON e.cfCompany_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
INNER JOIN Snival..Company	co	  ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric	  ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri	  ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Email em	          ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND EM.Assigned_To = E.Employee_Id AND EM.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
    AND e.Login_id = isnull(@Employee, e.Login_id)
    AND o.cfWon_Date >= @STARTDATE
    AND o.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
    AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0,  o.cfWon_Date ), 0) )
    AND ri.Pq_Meeting_Completed_Flag = 1
    AND ISNULL(o.cfGreen_Completion, 0) = 0
    AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
    AND ISNULL(o.WARRANTY, 'No') = 'No'
GROUP BY
    E.cfFull_Name
    , OP.Total_Count

 
 UNION
 
 
 SELECT  
     e.cfFull_Name
    , COUNT(DISTINCT ri.Pq_Meeting_Date) rintd
    , OP.Total_Count tc
FROM Snival..SFA_Opportunity o
INNER JOIN (
            SELECT
                  opp.Account_Manager_Id
                , count(*) as total_count
            FROM Snival..SFA_Opportunity opp
            WHERE opp.cfWon_Date >= @STARTDATE
            AND   opp.cfWon_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 Snival..Employee e             ON o.Account_Manager_Id = e.Employee_Id  
INNER JOIN Snival..ctLine_of_Business lob ON e.cfLine_of_Business_Id = lob.ctLine_of_Business_Id
INNER JOIN Snival..ctCompany_Code cc      ON e.cfCompany_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
INNER JOIN Snival..Company	co        ON o.Company_Id = co.Company_Id
INNER JOIN Snival..Pq_Int_Company ric	  ON co.Company_Id = ric.Company_Id
INNER JOIN Snival..Pq_Meeting_ri	  ON ric.Pq_Meetings_Id = ri.Pq_Meetings_Id
INNER JOIN Snival..Call_ em	          ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND em.Assigned_To = E.Employee_Id AND em.Assigned_To = O.Account_Manager_Id
WHERE o.STATUS = 4 -- Won
    AND e.Login_id = isnull(@Employee, e.Login_id)
    AND o.cfWon_Date >= @STARTDATE
    AND o.cfWon_Date <=  @ENDDATE -- first day of period to be excluded
    AND ri.Pq_Meeting_Date >= Dateadd(dd, -30, dateadd(dd, datediff(dd,0,  o.cfWon_Date ), 0) )
    AND ri.Pq_Meeting_Completed_Flag = 1
    AND ISNULL(o.cfGreen_Completion, 0) = 0
    AND ISNULL(o.NO_QUOTE___T_AND_M, '0') IN ('No', '0')
    AND ISNULL(o.WARRANTY, 'No') = 'No'
    GROUP BY
    E.cfFull_Name
    , OP.total_count
)
select cfFull_Name,tc,sum(rintd) as rintd
from CTE
group by cfFull_Name,tc

Open in new window

0
 

Author Comment

by:rhservan
ID: 39172810
Thanks Neo - Tested & verified.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39173508
Be careful, you could be getting the wrong results.

Please try exactly the same query but make this one change to:

UNION ALL

============
also:

Returns this:
cfFullName                       tc                  rintd

Name_1                             15                     6
Name_2                             21                     11
Name_3                             17                     18 --<< from Query 1
Name_3                             17                     1   -- << from Query 2

Name_4                             30                     1
Name_5                             19                     45
Name_5                             19                      1
Name_6                             32                     9

UNION does make rows unique - but the WHOLE row is evaluated, so those 2 entries for Name_3 are because 18 is different to 1.

But what would happen if both query1 and query2 gave 18 in that third column?
One of those rows would disappear

& if one of those rows disappears the sum would be 18
should the sum be 36?


Please try
UNION ALL

you are at risk of producing the wrong results - just double check
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39173523
and, what is achieved by this singular difference?

INNER JOIN Snival..Email em	          ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND EM.Assigned_To = E.Employee_Id AND EM.Assigned_To = O.Account_Manager_Id
INNER JOIN Snival..Call_ em	          ON ri.Pq_Meetings_Id = em.Pq_Meetings_Id AND em.Assigned_To = E.Employee_Id AND em.Assigned_To = O.Account_Manager_Id

Open in new window


in fact it appears that neither of those tables are referenced in any other place than those joins (not in the where clause, not in the selection clause)

looks to me like you could remove those tables altogether and the results would be the same.

sorry but I suspect your query needs some re-thinking
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

732 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