?
Solved

UNION - Adding Where Statement

Posted on 2012-09-11
7
Medium Priority
?
506 Views
Last Modified: 2012-09-14
Using SQL 2008

I am using the following query

SELECT  cd.DateTime ,
        cd.DigitsDialed ,
        rc.RouterCallKey ,
        rc.RouterCallKeyDay ,
        pe.FirstName ,
        pe.LastName
FROM    t_Route_Call_Detail rc
        LEFT OUTER JOIN t_Termination_Call_Detail cd ON rc.RouterCallKeyDay =    
         cd.RouterCallKeyDay AND rc.RouterCallKey = cd.RouterCallKey
        LEFT OUTER JOIN im_awdb.dbo.t_Agent ag ON cd.AgentSkillTargetID = ag.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Person pe ON ag.PersonID = pe.PersonID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team_Member tm ON ag.SkillTargetID = tm.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team at ON tm.AgentTeamID = at.AgentTeamID
WHERE   rc.DateTime >= @start
        AND rc.DateTime < @end
        AND ag.Description = 'MREC'

This does not give me all the information I require as the same RouterCallKey can have a different  ag.Description other than MREC i.e.

RouterCallKey      Desc
155            MREC
155            MREC
155            MREC
155            Cube

At the moment  I need to bring back all data reklating to MREC and also any other ag.description that has the same routercallkey as MREC


To do this I need to do something like below, which I cannot get to work.

SELECT
        a.DateTime ,
        a.DigitsDialed ,
        a.RouterCallKey ,
        a.RouterCallKeyDay ,
        a.FirstName ,
        a.LastName
FROM

(SELECT  cd.DateTime ,
        cd.DigitsDialed ,
        rc.RouterCallKey ,
        rc.RouterCallKeyDay ,
        pe.FirstName ,
        pe.LastName
FROM    t_Route_Call_Detail rc
        LEFT OUTER JOIN t_Termination_Call_Detail cd ON rc.RouterCallKeyDay = cd.RouterCallKeyDay AND rc.RouterCallKey = cd.RouterCallKey
        LEFT OUTER JOIN im_awdb.dbo.t_Agent ag ON cd.AgentSkillTargetID = ag.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Person pe ON ag.PersonID = pe.PersonID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team_Member tm ON ag.SkillTargetID = tm.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team at ON tm.AgentTeamID = at.AgentTeamID
WHERE   rc.DateTime >= @start
        AND rc.DateTime < @end
        AND ag.Description = 'MREC') a

UNION ALL

SELECT
        b.DateTime ,
        b.DigitsDialed ,
        b.RouterCallKey ,
        b.RouterCallKeyDay ,
        b.FirstName ,
        b.LastName
FROM

(SELECT  cd.DateTime ,
        cd.DigitsDialed ,
        rc.RouterCallKey ,
        rc.RouterCallKeyDay ,
        pe.FirstName ,
        pe.LastName
FROM    t_Route_Call_Detail rc
        LEFT OUTER JOIN t_Termination_Call_Detail cd ON rc.RouterCallKeyDay = cd.RouterCallKeyDay AND rc.RouterCallKey = cd.RouterCallKey
        LEFT OUTER JOIN im_awdb.dbo.t_Agent ag ON cd.AgentSkillTargetID = ag.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Person pe ON ag.PersonID = pe.PersonID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team_Member tm ON ag.SkillTargetID = tm.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team at ON tm.AgentTeamID = at.AgentTeamID
WHERE   rc.DateTime >= @start
        AND rc.DateTime < @end
        AND ag.Description <> 'MREC') b

WHERE a.RouterCallKey = b.RouterCallKey

Any help would be appreciated
0
Comment
Question by:halifaxman
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38386440
Insert the whole query into a subquery. That is, if you have:
SELECT * FROM TableA
UNION SELECT * FROM TableB

turn it into:
SELECT * FROM (SELECT * FROM TableA
UNION SELECT * FROM TableB) t
WHERE MyField=MyValue
0
 

Author Comment

by:halifaxman
ID: 38386485
Thanks for the answer

Not sure how this will work with my query

SELECT * FROM TableA a
where desc = MREC
UNION SELECT * FROM TableB a
where desc <> MRC

where a.routercallkey= b.routercallkey

If I put it into one subquery i.e.
select *
from
(SELECT * FROM TableA a
where desc = MREC
UNION SELECT * FROM TableB a
where desc <> MRC) t

How would I join the routercallcallkey in a to b i.e.

where a.routercallkey= b.routercallkey
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38386499
how about
;with cte as (
SELECT  cd.DateTime ,
        cd.DigitsDialed ,
        rc.RouterCallKey ,
        rc.RouterCallKeyDay ,
        pe.FirstName ,
        pe.LastName
FROM    t_Route_Call_Detail rc
        LEFT OUTER JOIN t_Termination_Call_Detail cd ON rc.RouterCallKeyDay = cd.RouterCallKeyDay AND rc.RouterCallKey = cd.RouterCallKey
        LEFT OUTER JOIN im_awdb.dbo.t_Agent ag ON cd.AgentSkillTargetID = ag.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Person pe ON ag.PersonID = pe.PersonID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team_Member tm ON ag.SkillTargetID = tm.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team at ON tm.AgentTeamID = at.AgentTeamID
WHERE   rc.DateTime >= @start
        AND rc.DateTime < @end
        AND ag.Description = 'MREC'
)
SELECT DateTime ,
       DigitsDialed ,
       RouterCallKey ,
       RouterCallKeyDay ,
       FirstName ,
       LastName
FROM cte
UNION ALL
SELECT  cd.DateTime ,
        cd.DigitsDialed ,
        rc.RouterCallKey ,
        rc.RouterCallKeyDay ,
        pe.FirstName ,
        pe.LastName
FROM    t_Route_Call_Detail rc
        LEFT OUTER JOIN t_Termination_Call_Detail cd ON rc.RouterCallKeyDay = cd.RouterCallKeyDay AND rc.RouterCallKey = cd.RouterCallKey
        LEFT OUTER JOIN im_awdb.dbo.t_Agent ag ON cd.AgentSkillTargetID = ag.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Person pe ON ag.PersonID = pe.PersonID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team_Member tm ON ag.SkillTargetID = tm.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team at ON tm.AgentTeamID = at.AgentTeamID
WHERE   rc.DateTime >= @start
        AND rc.DateTime < @end
        AND ag.Description <> 'MREC'
	AND rc.RouterCallKey IN (SELECT rc.RouterCallKey FROM cte)

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:halifaxman
ID: 38386578
Thanks for the answer, unfortunately this takes about 20 minutes plus to execute
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 38386595
As long as the columns have the same name, it's easy:

select *
from
(SELECT * FROM TableA a
where desc = MREC
UNION SELECT * FROM TableB a
where desc <> MRC) t
LEFT JOIN t_Termination_Call_Detail c
ON c.routercallkey=t.routercallkey

If they have different names, you can make them the same:

select *
from
(SELECT MyField1 col1, MyField2 col2, routercallkey routercallkey FROM TableA a
where desc = MREC
UNION SELECT * FROM TableB a
where desc <> MRC) t
LEFT JOIN t_Termination_Call_Detail c
ON c.routercallkey=t.routercallkey

This way, as long as the column number is the same (which is has to be for the union to work), if TableB has a different name for the field (or TableA), both get the same name.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38386633
How long does the upper SELECT take by itself?

First suggestion ... the ag.Description <> 'MREC' in the WHERE for both SELECTs make it an inner join in the end, so
LEFT OUTER JOIN t_Termination_Call_Detail cda and LEFT OUTER JOIN im_awdb.dbo.t_Agent ag
could both be INNER joins.  INNER joins are less expensive than OUTER joins.

Second suggestion ... instead of an IN, maybe an INNER JOIN e.g.
SELECT ....
FROM    t_Route_Call_Detail rc
        INNER JOIN (SELECT DISTINCT RouterCallKey FROM cte) v ON rc.RouterCallKey  = v.RouterCallKey
        LEFT OUTER JOIN t_Termination_Call_Detail cd ...
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38387355
Maybe this:


SELECT  cd.DateTime ,
        cd.DigitsDialed ,
        rc.RouterCallKey ,
        rc.RouterCallKeyDay ,
        pe.FirstName ,
        pe.LastName
FROM    t_Route_Call_Detail rc
        LEFT OUTER JOIN t_Termination_Call_Detail cd ON
            rc.RouterCallKeyDay = cd.RouterCallKeyDay AND
            rc.RouterCallKey = cd.RouterCallKey
        LEFT OUTER JOIN (
            SELECT SkillTargetID, PersonID
            FROM im_awdb.dbo.t_Agent
            WHERE
                Description = 'MREC'
        ) AS ag ON cd.AgentSkillTargetID = ag.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Person pe ON ag.PersonID = pe.PersonID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team_Member tm ON ag.SkillTargetID = tm.SkillTargetID
        LEFT OUTER JOIN im_awdb.dbo.t_Agent_Team at ON tm.AgentTeamID = at.AgentTeamID
WHERE   rc.DateTime >= @start
        AND rc.DateTime < @end
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

809 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