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
Solved

What is wrong with my UNION?

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Character matching different date formats for dates between 6 59
T-SQL: Do I need CLUSTERED here? 13 59
ServiceCenter IR Query Expressions 1 39
Complex Query - help please 5 52
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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 …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

766 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