Unused CallTypes

Calltypes configured but NOT in the Historical Interval Table

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
;WITH Unused_CallTypes AS
(
SELECT 
       CT.[CallTypeID]
      ,CT.[EnterpriseName]
      ,CT.[Description]
      ,CT.[Deleted]
      ,CT.[ChangeStamp]
      ,CT.[DateTimeStamp]
  FROM [aib_awdb].[dbo].[Call_Type] CT
  LEFT JOIN Call_Type_Interval CTI ON CTI.CallTypeID = CT.CallTypeID
  WHERE CTI.CallTypeID IS NULL
 )
 
SELECT * FROM Unused_CallTypes

Active Script Calltypes

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
;WITH ActiveScriptCallTypes AS
(
SELECT 
	 MasterScriptName = MS.EnterpriseName
	,CurrentVersion = MS.CurrentVersion
	,ScriptVersion = S.Version
	,CT.EnterpriseName
	,ScriptID = SCR.[ScriptID]
	,[LocalID] = SCR.LocalID
	,[TargetType] = SCR.TargetType
	,[ForeignKey] = SCR.ForeignKey	
 
  FROM [Ref_Script_Cross_Reference] SCR
  LEFT JOIN Ref_Script S ON S.ScriptID = SCR.ScriptID
  LEFT JOIN Ref_Master_Script MS ON MS.MasterScriptID = S.MasterScriptID
  LEFT JOIN Call_Type CT ON CT.CallTypeID = SCR.ForeignKey
 
  WHERE SCR.TargetType = '7' AND (CurrentVersion = S.Version)
  )
 
SELECT * FROM ActiveScriptCallTypes

Note - Remove Comments “–” in SQL below if you want to also check these CallTypes do not appear in the Calltype Historical Tables

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
;WITH Unused_CallTypes AS
(
SELECT 
       CT.[CallTypeID]
      ,CT.[EnterpriseName]
      ,CT.[Description]
      ,CT.[Deleted]
      ,CT.[ChangeStamp]
      ,CT.[DateTimeStamp]
  FROM [aib_awdb].[dbo].[Call_Type] CT
  --LEFT JOIN Call_Type_Interval CTI ON CTI.CallTypeID = CT.CallTypeID
  WHERE CT.Deleted = 'N' --AND CTI.CallTypeID IS NULL
 )
 
,ActiveScriptCallTypes AS
(
SELECT 
	 MasterScriptName = MS.EnterpriseName
	,CurrentVersion = MS.CurrentVersion
	,ScriptVersion = S.Version
	,CT.EnterpriseName
	,ScriptID = SCR.[ScriptID]
	,[LocalID] = SCR.LocalID
	,[TargetType] = SCR.TargetType
	,[ForeignKey] = SCR.ForeignKey	
 
  FROM [Ref_Script_Cross_Reference] SCR
  LEFT JOIN Ref_Script S ON S.ScriptID = SCR.ScriptID
  LEFT JOIN Ref_Master_Script MS ON MS.MasterScriptID = S.MasterScriptID
  LEFT JOIN Call_Type CT ON CT.CallTypeID = SCR.ForeignKey
 
  WHERE SCR.TargetType = '7' AND (CurrentVersion = S.Version)
  )
 
SELECT 
       UCT.[CallTypeID]
      ,UCT.[EnterpriseName]
      ,UCT.[Description]
      ,UCT.[Deleted]
      ,UCT.[ChangeStamp]
      ,UCT.[DateTimeStamp]
	  ,ACT.ForeignKey
	  ,DNM.DialedNumberID
	  ,DN.DialedNumberString
	  ,DN.RoutingClientID
	  ,RoutingClientName = RC.EnterpriseName
 
FROM Unused_CallTypes UCT
LEFT JOIN ActiveScriptCallTypes ACT ON ACT.ForeignKey = UCT.CallTypeID
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 IS NULL 
AND DNM.DialedNumberID IS NULL
 
ORDER BY UCT.[EnterpriseName]
This website uses cookies. By using the website, you agree with storing cookies on your computer. Also you acknowledge that you have read and understand our Privacy Policy. If you do not agree leave the website.More information about cookies