Unused CallTypes

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

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]