Unused CallTypes

;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

;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

;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
 )
 
,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
 
FROM Unused_CallTypes UCT
LEFT JOIN ActiveScriptCallTypes ACT ON ACT.ForeignKey = UCT.CallTypeID
WHERE ACT.ForeignKey IS NULL
 
ORDER BY UCT.[EnterpriseName]