Home SQL Server

Query Help

alex_kokinalex_kokin Member IT Monkey ✭

Might anyone here know how I can show the full path to classifications in a SQL Query? Here is a query that I use.


Select

  wi.TierQueue

  ,wi.Title

  ,wi.Classification

  ,wi.System_WorkItemAssignedToUser_DisplayName AS 'AssignedTo'

  ,wi.Source_Id AS 'Ticket_ID'

  ,wi.Priority

  ,wi.Status

  ,wi.ResolutionCategory

  ,wi.ResolutionDescription    

  ,wi.Urgency

  ,wi.Impact

  ,CAST(wi.FirstAssignedDate AS DateTime) AS 'Assigned_Date'

  ,CAST(wi.ClosedDate AS DateTime) AS 'Closed_Date'

  ,CAST(wi.ResolvedDate AS Date) AS 'Resolved_Date'

  ,DateDiff(Day, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Days_To_Close'

  ,DateDiff(Hour, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Hours_To_Close'

  ,DateDiff(Minute, wi.FirstAssignedDate, wi.ResolvedDate) AS 'Minute_To_Close'

  ,wi.System_WorkItemCreatedByUser_DisplayName AS 'CreatedBy'

  ,wi.System_WorkItemAffectedUser_DisplayName AS 'AffectedUser'

,CAST(w.Created AS Date) AS 'Created'

,TRY_CAST(w.Created AS DATE) AS 'CreatedDate'

,YEAR(w.Created) AS 'CreatedYear'

,Month(w.Created) AS 'CreatedMonth'

,Day(w.Created) AS 'CreatedDay'


,DatePart(hh,w.Created) AS 'CreatedHour'

,CASE WHEN DatePart(dw,w.Created) = 1 THEN 'SUNDAY'

WHEN DatePart(dw,w.Created) = 2 THEN 'MONDAY'

WHEN DatePart(dw,w.Created) = 3 THEN 'TUESDAY'

WHEN DatePart(dw,w.Created) = 4 THEN 'WEDNESDAY'

WHEN DatePart(dw,w.Created) = 5 THEN 'THURSDAY'

WHEN DatePart(dw,w.Created) = 6 THEN 'FRIDAY'

WHEN DatePart(dw,w.Created) = 7 THEN 'SATURDAY'

END AS 'DayOfWeek'


FROM 

ServiceManagement_New.cachert.csnCached_MT_System_WorkItem_Incident AS wi

LEFT JOIN ServiceManagement_New.dbo.WorkItem AS w ON w.WorkItemID = wi.Source_Id

WHERE 1 = 1

AND wi.TierQueue like '%Product Support%'


  AND w.created >= '2021-01-01'

--AND @createdFilter

Answers

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    When you say the "full path" do you mean the entire parent/child tree to said Classification?

  • alex_kokinalex_kokin Member IT Monkey ✭

    Yes please

  • Adam_DzyackyAdam_Dzyacky Product Owner Contributor Monkey ✭✭✭✭✭

    You'd have to adapt this from SCSM, but the same principle would apply:

    WITH EnumDisplayName AS
    (
         	SELECT DISTINCT Enum.EnumTypeId
    					   ,Enum.[ParentEnumTypeId]
    					   ,LT.LTValue AS 'DisplayName'
    		FROM [dbo].[EnumType] Enum (nolock)
    		JOIN [dbo].[LocalizedText] LT (nolock)
    		ON LT.LanguageCode = 'ENU'
    		AND LT.LTStringType = 1 -- 1 is localized DisplayName for the element, 2 is Description
    		AND Enum.EnumTypeId = LT.LTStringId
    )
    ,FullEnumDisplayName AS
    (
    	SELECT Enum.[EnumTypeId]
    		  ,cast(Enum.[DisplayName] as nvarchar(max)) AS 'DisplayName'
    		  ,Enum.[ParentEnumTypeId]
    	FROM EnumDisplayName Enum
    	WHERE Enum.[ParentEnumTypeId] IS NULL
    	UNION ALL
    	SELECT Enum.[EnumTypeId]
    	      ,cast(FullEnum.[DisplayName] + '\' + Enum.[DisplayName] as nvarchar(max)) AS 'DisplayName'
    		  ,Enum.[ParentEnumTypeId]
    	FROM EnumDisplayName Enum
    
    	JOIN FullEnumDisplayName FullEnum 
    	ON Enum.[ParentEnumTypeId] = FullEnum.[EnumTypeId]
    )
    
    SELECT FullEnum.[EnumTypeId], FullEnum.[DisplayName]
    FROM FullEnumDisplayName FullEnum
    

    Alternatively, in the SCSM DW you could create a View that you could always call

    The View:

    CREATE VIEW dbo.IncidentClassificationHierarchy AS
    WITH _Items as
    (
    SELECT IncidentClassificationId Id, ParentId, d.DisplayName, Ordinal, 1 AS Level, cast(':' + cast(IncidentClassificationId as varchar) + ':' as varchar (100)) AS Struc
    , cast('\' + cast(d.DisplayName as varchar) + '\' as varchar (100)) AS FullName
    FROM dbo.IncidentClassification AS t
    INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = 'ENU' AND d.ElementName = t.Id
    WHERE ParentId = 1
    UNION ALL
    SELECT IncidentClassificationId Id, t.ParentId, d.DisplayName AS SupportGroup, (i.Level+1)*1000 + t.Ordinal, i.Level+1, cast(i.Struc + cast(IncidentClassificationId as varchar)+ ':' as varchar(100)) AS Struc
    , cast(i.FullName + cast(d.DisplayName as varchar) + '\' as varchar(100)) AS FullName
    FROM dbo.IncidentClassification AS t
    INNER JOIN dbo.DisplayStringDimvw AS d ON d.LanguageCode = 'ENU' AND d.ElementName = t.Id
    JOIN _Items i on i.Id = t.ParentId
    )
    SELECT ISNULL(Parent.Id,Child.Id) ParentId, ISNULL(Parent.DisplayName,Child.DisplayName) ParentClassification, Child.ID ChildId, Child.DisplayName Classification, Child.Ordinal
    , SUBSTRING(Child.FullName,2,LEN(Child.FullName)-2) FullName
    FROM _Items Child
    LEFT OUTER JOIN _Items AS Parent ON Parent.ID = Child.ParentId
    --ORDER BY Child.FullName
    
    

    The call/join to that View:

    select top 10 ir.Id, ir.Title, c.FullName as Classification
      from dbo.IncidentDimvw ir
        left outer join dbo.IncidentClassificationHierarchy c
          on c.ChildId = ir.Classification_IncidentClassificationId
    


    But it is worth asking, is there a particular reason you need the full path?

    sources:

  • alex_kokinalex_kokin Member IT Monkey ✭

    Adam_Dzyacky this is for more granular reporting

Sign In or Register to comment.