Change Management Category

Metro_ITS

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

Justin_Workman

What about this?

<div>SELECT cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS 'WorkItemId',&nbsp;</div><div>cr.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS 'Title',&nbsp;</div><div>catds.DisplayName AS 'Category'&nbsp;</div><div>FROM MTV_System$WorkItem$ChangeRequest cr

</div><div>JOIN DisplayStringView catds&nbsp;</div><div>ON catds.LTStringId = cr.Category_7B1892FE_108A_EC85_064D_7815C2DFC442</div><div>AND catds.LanguageCode = 'enu'</div>

Justin_Workman

What about this?

<div>SELECT cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS 'WorkItemId',&nbsp;</div><div>cr.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS 'Title',&nbsp;</div><div>catds.DisplayName AS 'Category'&nbsp;</div><div>FROM MTV_System$WorkItem$ChangeRequest cr

</div><div>JOIN DisplayStringView catds&nbsp;</div><div>ON catds.LTStringId = cr.Category_7B1892FE_108A_EC85_064D_7815C2DFC442</div><div>AND catds.LanguageCode = 'enu'</div>

Metro_ITS

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.