Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. If you'd prefer to explore, try our test area to get started. And see here for current known issues.

Bronze

Calling SQL experts to help write a query

Trying to find some help from some sql experts.

I was asked to help write a query showing each Dialed_Number, the associated Call_Type, and Script(s) in an ICM configuration.

All the data I’m looking for seems to be in the following tables:

Dialed_Number (DialedNumberString, EnterpriseName, DialedNumberID)

Call_Type (CallTypeID, EnterpriseName)

Dialed_Number_Map (DialedNumberID, CallTypeID)

Call_Type_Map (CallTypeID, MasterScriptID)

Master_Script (MasterScriptID, EnterpriseName)

Thus far, my query looks like this:

SELECT     distinct Dialed_Number.DialedNumberString, Dialed_Number.EnterpriseName AS DN_Name, Call_Type.EnterpriseName AS Call_Type_Name,

                      Master_Script.EnterpriseName AS ScriptName

FROM         Call_Type_Map CROSS JOIN

                      Call_Type CROSS JOIN

                      Dialed_Number CROSS JOIN

                      Dialed_Number_Map CROSS JOIN

                      Master_Script

However, it’s returning every possible combination (it was 10x worse before adding the word ‘distinct’)

Ideally, I’d like the results to just show one row for each Dialed_Number (assumes 1:1 ratio of DN to CallType).

e.g.:

DN_Name           DialedNumberString      CallType_Name                ScriptName

Can anyone offer any guidance or suggestion?

Everyone's tags (3)
9 REPLIES
Green

Calling SQL experts to help write a query

I often do that at the end of the deployment to get a spreadsheet showing exactly that - DN, call type, scheduled script.

select DialedNumberString AS DN,  d.Description AS DN_Description, c.EnterpriseName AS CallType,

ms.EnterpriseName as ScriptName

from t_Dialed_Number_Map m, t_Call_Type c, t_Dialed_Number d, Call_Type_Map ctm, t_Master_Script ms

where ctm.Item = 0

and m.CallTypeID = c.CallTypeID

and c.CallTypeID = ctm.CallTypeID

and m.DialedNumberID = d.DialedNumberID

and ms.MasterScriptID = ctm.MasterScriptID

and d.RoutingClientID = 5000

order by DN

Regards,

Geoff

Bronze

Re: Calling SQL experts to help write a query

Thanks Geoff,

I'd put this out on a couple different channels, and while yours is pretty good (I did remove the RoutingClientID from the where clause), I got another version that works just as well, and also includes DNs without CallTypes, and those which may have a CallType, but no script.

SELECT dn.DialedNumberString, dn.EnterpriseName AS Dialed_Number_Name, ct.EnterpriseName AS Call_Type_Name, ms.EnterpriseName AS Script_Name

FROM Dialed_Number dn

LEFT OUTER JOIN Dialed_Number_Map dnm ON dn.DialedNumberID = dnm.DialedNumberID

LEFT OUTER JOIN Call_Type ct ON dnm.CallTypeID = ct.CallTypeID

LEFT OUTER JOIN Call_Type_Map ctm ON ct.CallTypeID = ctm.CallTypeID

LEFT OUTER JOIN Master_Script ms ON ctm.MasterScriptID = ms.MasterScriptID

I believe the "joins" are supposedly more efficient, too.

Thanks for your contribution!

Green

Calling SQL experts to help write a query

Steve.

DNs without call types - never have them. What's the point?

DN call types always have a scheduled script - otherwise, what's the point.

Agree that explicit JOINS are more efficient, although it's just the config. Can't really be an issue.

I do move the routing client ID around - CUCM, CVP, EIM etc. Sorry about that bit.

Regards,

Geoff

Bronze

Calling SQL experts to help write a query

I suppose the benefit *to me* is that it shows ALL the Dialed_Numbers.

For example in my lab, your query only returned ten rows.  The second query returned fifteen, and may help in situations where there's a misconfiguration.

Green

Calling SQL experts to help write a query

I suppose the benefit *to me* is that it shows ALL the Dialed_Numbers.

For example in my lab, your query only returned ten rows.  The second query returned fifteen, and may help in situations where there's a misconfiguration.

Steve,

I think I get it. You are looking for a misconfig where you have forgotten to associate the DN with a CT? Of course, I never make mistakes like that.

Regards,

Geoff

Calling SQL experts to help write a query

Votes for you guys as this thing is awesome!

david

Bronze

Calling SQL experts to help write a query

Thanks David,

I used the query above as a template to write a similar one, listing every Agent, what peripheral they're on, what Agent_Team they're a member of, and whether or not they're a Supervisor.

Enjoy.

Select a.EnterpriseName AS AgentName, at.EnterpriseName AS Agent_Team_Name, a.SupervisorAgent, p.PeripheralName AS CCSiteName

from Agent a

left outer join Agent_Team_Member atm on a.SkillTargetID = atm.SkillTargetID

left outer join Agent_Team at on atm.AgentTeamID = at.AgentTeamID

left outer join Peripheral p on at.PeripheralID = p.PeripheralID

New Member

Calling SQL experts to help write a query

HI All,

I am trying to run above queries; but i am not getting any results :-(

I am putting above queries in a new query editor windows on AW and run it; but no results.

Please help me how do i run above queries.  These queries look exceptionally useful.

Thanks & Regards,

Piyush

New Member

Calling SQL experts to help write a query

Hi Guys,

I got it. 5 star for all.

1695
Views
30
Helpful
9
Replies