Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
| vendors:cisco:uc:icm:sql:unusedcalltypes [2019/05/07 15:33] – gerardorourke | vendors:cisco:uc:icm:sql:unusedcalltypes [2019/05/07 16:12] (current) – gerardorourke | ||
|---|---|---|---|
| Line 3: | Line 3: | ||
| ==== Calltypes configured but NOT in the Historical Interval Table ==== | ==== Calltypes configured but NOT in the Historical Interval Table ==== | ||
| <code sql> | <code sql> | ||
| + | SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON | ||
| + | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED | ||
| + | |||
| ;WITH Unused_CallTypes AS | ;WITH Unused_CallTypes AS | ||
| ( | ( | ||
| SELECT | SELECT | ||
| - | CT.[CallTypeID] | + | CT.[CallTypeID] |
| , | , | ||
| , | , | ||
| Line 22: | Line 25: | ||
| ==== Active Script Calltypes ==== | ==== Active Script Calltypes ==== | ||
| <code sql> | <code sql> | ||
| + | SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON | ||
| + | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED | ||
| + | |||
| ;WITH ActiveScriptCallTypes AS | ;WITH ActiveScriptCallTypes AS | ||
| ( | ( | ||
| Line 46: | Line 52: | ||
| - | ==== Results | + | ===== CallTypes Not in Active Scripts OR Dialed Number |
| + | |||
| + | Note - Remove Comments " | ||
| <code sql> | <code sql> | ||
| + | SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON | ||
| + | SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED | ||
| + | |||
| ;WITH Unused_CallTypes AS | ;WITH Unused_CallTypes AS | ||
| ( | ( | ||
| SELECT | SELECT | ||
| - | CT.[CallTypeID] | + | CT.[CallTypeID] |
| , | , | ||
| , | , | ||
| Line 58: | Line 69: | ||
| , | , | ||
| FROM [aib_awdb].[dbo].[Call_Type] CT | FROM [aib_awdb].[dbo].[Call_Type] CT | ||
| - | LEFT JOIN Call_Type_Interval CTI ON CTI.CallTypeID = CT.CallTypeID | + | |
| - | WHERE CTI.CallTypeID IS NULL | + | WHERE CT.Deleted = ' |
| ) | ) | ||
| Line 83: | Line 94: | ||
| Select | Select | ||
| - | UCT.[CallTypeID] | + | UCT.[CallTypeID] |
| , | , | ||
| , | , | ||
| Line 90: | Line 101: | ||
| , | , | ||
| , | , | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | , | ||
| FROM Unused_CallTypes UCT | FROM Unused_CallTypes UCT | ||
| LEFT JOIN ActiveScriptCallTypes ACT ON ACT.ForeignKey = UCT.CallTypeID | LEFT JOIN ActiveScriptCallTypes ACT ON ACT.ForeignKey = UCT.CallTypeID | ||
| - | WHERE ACT.ForeignKey is Null | + | LEFT JOIN Dialed_Number_Map DNM ON DNM.CallTypeID = UCT.CallTypeID |
| + | LEFT JOIN Dialed_Number DN ON DN.DialedNumberID = DNM.DialedNumberID | ||
| + | LEFT JOIN Routing_Client RC ON RC.RoutingClientID = DN.RoutingClientID | ||
| + | |||
| + | WHERE | ||
| + | ACT.ForeignKey | ||
| + | AND DNM.DialedNumberID | ||
| ORDER BY UCT.[EnterpriseName] | ORDER BY UCT.[EnterpriseName] | ||
| </ | </ | ||