Change Management Category
I am creating a custom SQL report for our Change Control meetings. I have all of the Change Request information in SQL with the exception of the Category (Emergency, Normal, etc). I have worked through a number of the database tables to join the Category to the Change Work Item, but unsuccessfully. The closest I have been is to list each of the Categories for any particular Change Request (see SQL query below).
Has anyone successfully found the relationship between the Change Work Item and the Category?
Select WorkItemId, DSCat.DisplayString
From WorkItem as WI
--Join Workitem to Category
LEFT OUTER JOIN ServiceManager.dbo.ManagedTypeProperty as MTP on WI.ClassId = MTP.ManagedTypeId and ManagedTypePropertyName = 'Category'
LEFT OUTER JOIN Enumeration as Enum on MTP.EnumTypeId = Enum.ParentEnumerationID
LEFT OUTER JOIN DisplayString as DSCat on Enum.EnumerationID = DSCat.ElementID
Where WorkItemId Like 'CR%'