UNION - Adding Where Statement

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
halifaxmanAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
CluskittCommented:
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
 
halifaxmanAuthor Commented:
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
lwadwellCommented:
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
 
halifaxmanAuthor Commented:
Thanks for the answer, unfortunately this takes about 20 minutes plus to execute
0
 
CluskittCommented:
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
 
lwadwellCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.