Change Management Category
Metro_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
-
Options
Justin_Workman
Cireson Support Super IT Monkey ✭✭✭✭✭
What about this?<div>SELECT cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS 'WorkItemId', </div><div>cr.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS 'Title', </div><div>catds.DisplayName AS 'Category' </div><div>FROM MTV_System$WorkItem$ChangeRequest cr </div><div>JOIN DisplayStringView catds </div><div>ON catds.LTStringId = cr.Category_7B1892FE_108A_EC85_064D_7815C2DFC442</div><div>AND catds.LanguageCode = 'enu'</div>
5
Answers
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.