Change Management Category

Metro_ITSMetro_ITS Customer IT Monkey ✭

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%'

Best Answer

Answers

  • Metro_ITSMetro_ITS Customer IT Monkey ✭

    Thank you Justin...that worked great!  I am now stuck on tying the CR Assignee to this report as well.  I am not able to find the correct tables to join together to get this binding.  Any thoughts?

    I have attached my current SQL statement if that helps.

Sign In or Register to comment.