Home SQL Server

HOW TO - SCSM DataWarehouse - Report FULL NAME of IR CLASSIFICATION

Conner_WoodConner_Wood Customer Advanced IT Monkey ✭✭✭
So recently I've been tasked with creating another report in SQL.  One of the requirements was to display the FULL NAME of the IR Classification Enumeration.  This means having to do SQL recursion on the SCSM DataWarehouse.

Below is the SQL required to generate "IR_CLASSIFICATION_FULLENUM" which contains the FULL PATH/FULL NAME of the Incident Classification Enumeration.

USE [DWDataMart];

WITH IR_CLASSIFICATION_ENUM AS
(
     SELECT Enum.[IncidentClassificationId] AS 'EnumDimKey'
           ,DS.[DisplayName] AS 'EnumDisplayName'
           ,Enum.[ParentId] as 'EnumParentDimKey'
     FROM [dbo].[IncidentClassificationvw] Enum (nolock)

     JOIN [dbo].[DisplayStringDimvw] DS (nolock)
     ON DS.[BaseManagedEntityId] = Enum.[EnumTypeId]
     AND DS.[LanguageCode] = 'ENU'
)
,IR_CLASSIFICATION_FULLENUM AS
(
     SELECT Enum.[EnumDimKey]
           ,cast(Enum.[EnumDisplayName] as nvarchar(max)) AS 'EnumDisplayName'
           ,Enum.[EnumParentDimKey]
     FROM IR_CLASSIFICATION_ENUM Enum
     WHERE Enum.[EnumParentDimKey] IS NULL
     UNION ALL
     SELECT Enum.[EnumDimKey]
           ,cast(FullEnum.[EnumDisplayName] + '\' + Enum.[EnumDisplayName] as nvarchar(max)) AS 'EnumDisplayName'
           ,Enum.[EnumParentDimKey]
     FROM IR_CLASSIFICATION_ENUM Enum
     JOIN IR_CLASSIFICATION_FULLENUM FullEnum on Enum.[EnumParentDimKey] = FullEnum.[EnumDimKey]
)


SELECT TOP 1000 IR.[DisplayName]
      ,IR.[Classification_IncidentClassificationId]
      ,IR_CLASSIFICATION.[EnumDisplayName] AS 'Full Classification'
FROM [dbo].[IncidentDimvw] IR (nolock)

LEFT JOIN IR_CLASSIFICATION_FULLENUM IR_CLASSIFICATION 
ON IR_CLASSIFICATION.[EnumDimKey] = IR.[Classification_IncidentClassificationId]


Comments

Sign In or Register to comment.