?
Solved

What is wrong with my UNION?

Posted on 2013-05-16
4
Medium Priority
?
314 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 2000 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 49

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 49

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

762 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