Solved

What is wrong with my UNION?

Posted on 2013-05-16
4
302 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
  • 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

744 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