Query Help

alex_kokin

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

Adam_Dzyacky

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

alex_kokin

Yes please

Adam_Dzyacky

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:

• https://social.technet.microsoft.com/Forums/en-US/c059a528-6a49-4526-b7fc-237e2afd6b08/sr-parent
• https://social.technet.microsoft.com/Forums/ie/en-US/2f3ab2f7-0fec-4ac7-85e0-8d6b28d0932a/how-to-display-full-classification-category-in-scsm-reports?forum=dwreportingdashboards

alex_kokin

Adam_Dzyacky this is for more granular reporting