Differences

This shows you the differences between two versions of the page.

--- vendors:cisco:uc:icm:sql:script [2019/05/08 15:54] – gerardorourke
+++ vendors:cisco:uc:icm:sql:script [2021/05/17 15:42] (current) – [Calltypes] gerardorourke
@@ Line 86: / Line 86: @@
 | 87                  | Object Last                    |
+===== The Latest Version of the MasterScript which contain a Enterprise Skillgroup=====
 <code sql>
 SELECT
  MasterScriptName = MS.EnterpriseName
 ,ESGName = ESG.EnterpriseName
+,CurrentVersion = MS.CurrentVersion
+,ScriptVersion = S.Version
 ,SCR.[ScriptID]
 ,[LocalID]
@@ Line 101: / Line 103: @@
   LEFT JOIN Enterprise_Skill_Group ESG ON ESG.EnterpriseSkillGroupID = SCR.ForeignKey
-  WHERE SCR.TargetType = '9'
+  WHERE SCR.TargetType = '9' AND (CurrentVersion = S.Version)
   ORDER BY MS.EnterpriseName
 </code>
+==== All Scripts (including old scripts) which reference an Enterprise Skillgroup ====
+<code sql>
+SELECT
+ MasterScriptName = MS.EnterpriseName
+,ESGName = ESG.EnterpriseName
+  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 Enterprise_Skill_Group ESG ON ESG.EnterpriseSkillGroupID = SCR.ForeignKey
+  WHERE SCR.TargetType = '9'
+  GROUP BY MS.EnterpriseName, ESG.EnterpriseName
+  ORDER BY MS.EnterpriseName, ESG.EnterpriseName
+</code>
-Which Enterprise Skillgroups are NOT in any scripts (and can be easily deleted)
+====Which Enterprise Skillgroups are NOT in any scripts (and can be easily deleted)====
 <code sql>
 SELECT [EnterpriseSkillGroupID]
@@ Line 153: / Line 172: @@
   ORDER BY MS.EnterpriseName
 </code>
+==== Details Scripts which have a Goto Script and which script configured as the Goto Script ====
+<code sql>
+SELECT
+      OriginatingScript = MS1.EnterpriseName
+	  ,SCR.[ScriptID]
+	  ,S.MasterScriptID
+      ,SCR.[LocalID]
+      ,SCR.[TargetType]
+      ,SCR.[ForeignKey]
+	  ,GotoScript = MS2.EnterpriseName
+  FROM [vhi_awdb].[dbo].[Script_Cross_Reference] SCR
+  LEFT JOIN Script S ON S.ScriptID = SCR.ScriptID
+  LEFT JOIN Master_Script MS1 ON MS1.MasterScriptID = S.MasterScriptID
+  LEFT JOIN Master_Script MS2 on MS2.MasterScriptID = SCR.ForeignKey
+  WHERE SCR.TargetType = '20'
+  --AND ForeignKey = '5642'
+  ORDER BY MS2.EnterpriseName
+</code>