UCCE SQL Queries To Join Tables

[abhilash2001]

Hi Guys, I am trying to run a query that joins Route Call Detail and Termination Call Detail. For example; I need to pull pv1 and pv2 from Route call detail with a CallTypeID = 5043, at the same time, I need to pull AgentPeripheralNumber from Termination Call Detail for that same call, but it has a different call type ID. The RCD and TCD have one thing in common  (PV9) which is the (routercallkeyday+routercallkey). We also need to disregard any TCD records with empty AgentPeripheralNumber. Any ideas? 

[Omar Deen · Accepted solution]

There's more efficient ways of doing this, and if I had the time, I would put that together... but this query should suffice

select RCD.Variable1, RCD.Variable9, TCD.AgentPeripheralNumber
from Termination_Call_Detail TCD, Route_Call_Detail RCD
where TCD.RouterCallKeyDay = RCD.RouterCallKeyDay
and TCD.RouterCallKey = RCD.RouterCallKey
and RCD.CallTypeID = '5787'
and TCD.AgentPeripheralNumber IS NOT NULL
order by RCD.DateTime desc

[abhilash2001]

Thank you so much. It worked for my requirement.