Home SQL Server

SQL Query to get a Manual Activity's (MA's) parent Change Request (CR)

Brett_EtzelBrett_Etzel Customer IT Monkey ✭

Trying to figure out how to get a Change Request's (CR's) child Activities (RA, MA, PA, SA, etc.).

Answers

  • Justin_WorkmanJustin_Workman Cireson Support Super IT Monkey ✭✭✭✭✭

    @Brett_Etzel - This should work against the ServiceManagement database:

    SELECT * FROM WorkItem

    WHERE ParentWorkItemId = '<WorkItem ID of CR here>'

  • Brett_EtzelBrett_Etzel Customer IT Monkey ✭

    Thanks Justin!

    I was hoping to figure out how to do with in the "ServiceManager" DB due to the update lag on the "ServiceManagement" DB.

    I pieced some ideas together to come up with the query below. It's far from pretty, but it seems to be working.

    Now I can't figure out how to get the reviewers of an RA using the ServiceManager DB. It's always something. Any ideas on that?


    -- CRs and their RAs and MAs.

    DECLARE @strGroup varchar(60)

    SET @strGroup = 'IT Services';

    DECLARE @strState varchar(60)

    SET @strState = 'In Progress';

    SELECT * FROM

    (

    SELECT DISTINCT

    cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS 'CR',

    cr.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS 'CR_Title',

    cr.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 AS 'CR_Create_Date',

    cr_ass_usr.DisplayName as 'CR_Assigned_User',

    cr_status.DisplayName AS 'CR_Status',

    CR_SupportGroup.DisplayName AS 'CR_Support_Group',

    ma.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS 'Activity',

    ma_ass_usr.DisplayName AS 'Activity_Assigned_User',

    ma.SequenceId_644EB7B4_745F_8E69_5695_833D7AFD28A0 AS 'Activity_Order',

    ma_stat.DisplayName AS 'Activity_Status'

    FROM MTV_System$WorkItem$ChangeRequest AS cr

    --CR Asigned User

    INNER JOIN Relationship AS cr_ass_rel on cr_ass_rel.SourceEntityId = cr.BaseManagedEntityId and cr_ass_rel.RelationshipTypeId = '15e577a3-6bf9-6713-4eac-ba5a5b7c4722'

    INNER JOIN MT_System$Domain$User AS cr_ass_usr on cr_ass_usr.BaseManagedEntityId = cr_ass_rel.TargetEntityId

    --CR Status

    INNER JOIN DisplayStringView AS cr_status on cr_status.LTStringId = cr.Status_72C1BC70_443C_C96F_A624_A94F1C857138 and cr_status.LanguageCode = 'enu'

    --CR Tier

    LEFT OUTER JOIN DisplayStringView AS CR_SupportGroup ON CR.SupportGroup_4A79F4D2_652F_0653_5604_98E1B732ECDD = CR_SupportGroup.LTStringId AND CR_SupportGroup.LanguageCode = 'ENU' 

    --Related MA 

    INNER JOIN Relationship AS cr_ma_rel on cr_ma_rel.SourceEntityId = cr.BaseManagedEntityId and cr_ma_rel.RelationshipTypeId = '2da498be-0485-b2b2-d520-6ebd1698e61b' 

    INNER JOIN MTV_System$WorkItem$Activity$ManualActivity AS ma on cr_ma_rel.TargetEntityId = ma.BaseManagedEntityId

    --Related MA Status

    INNER JOIN DisplayStringView AS ma_stat on ma_stat.LTStringId = ma.Status_8895EC8D_2CBF_0D9D_E8EC_524DEFA00014 and ma_stat.LanguageCode = 'enu'

    --Related Asigned User

    INNER JOIN Relationship AS ma_ass_rel on ma_ass_rel.SourceEntityId = ma.BaseManagedEntityId and ma_ass_rel.RelationshipTypeId = '15e577a3-6bf9-6713-4eac-ba5a5b7c4722'

    INNER JOIN MT_System$Domain$User AS ma_ass_usr on ma_ass_usr.BaseManagedEntityId = ma_ass_rel.TargetEntityId

    --CR is 'In Progress' and Support Group match.

    WHERE cr_status.DisplayName = @strState AND CR_SupportGroup.DisplayName = @strGroup

    UNION ALL

    SELECT DISTINCT

    cr.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS 'CR',

    cr.Title_9691DD10_7211_C835_E3E7_6B38AF8B8104 AS 'CR_Title',

    cr.CreatedDate_6258638D_B885_AB3C_E316_D00782B8F688 AS 'CR_Create_Date',

    cr_ass_usr.DisplayName as 'CR_Assigned_User',

    cr_status.DisplayName AS 'CR_Status',

    cr_SupportGroup.DisplayName AS 'CR_Support_Group',

    ra.Id_9A505725_E2F2_447F_271B_9B9F4F0D190C AS 'Activity',

    -- setting to null until I figure out how to get the reviewers

    null AS 'Activity_Assigned_User', 

    ra.SequenceId_644EB7B4_745F_8E69_5695_833D7AFD28A0 AS 'Activity_Order',

    ra_stat.DisplayName AS 'Activity_Status'

    FROM MTV_System$WorkItem$ChangeRequest AS cr

    --CR Asigned User

    INNER JOIN Relationship AS cr_ass_rel on cr_ass_rel.SourceEntityId = cr.BaseManagedEntityId and cr_ass_rel.RelationshipTypeId = '15e577a3-6bf9-6713-4eac-ba5a5b7c4722'

    INNER JOIN MT_System$Domain$User AS cr_ass_usr on cr_ass_usr.BaseManagedEntityId = cr_ass_rel.TargetEntityId

    --CR Status

    INNER JOIN DisplayStringView AS cr_status on cr_status.LTStringId = cr.Status_72C1BC70_443C_C96F_A624_A94F1C857138 and cr_status.LanguageCode = 'enu'

    --CR Tier

    LEFT OUTER JOIN DisplayStringView AS cr_SupportGroup ON cr.SupportGroup_4A79F4D2_652F_0653_5604_98E1B732ECDD = cr_SupportGroup.LTStringId AND cr_SupportGroup.LanguageCode = 'ENU' 

    --Related RA 

    INNER JOIN Relationship AS cr_ra_rel on cr_ra_rel.SourceEntityId = cr.BaseManagedEntityId and cr_ra_rel.RelationshipTypeId = '2da498be-0485-b2b2-d520-6ebd1698e61b' 

    INNER JOIN MTV_System$WorkItem$Activity$ReviewActivity AS ra on cr_ra_rel.TargetEntityId = ra.BaseManagedEntityId

    --Related RA Status

    INNER JOIN DisplayStringView AS ra_stat on ra_stat.LTStringId = ra.Status_8895EC8D_2CBF_0D9D_E8EC_524DEFA00014 and ra_stat.LanguageCode = 'enu'

    --Related RA 'reviewer'

    -- need to figure this out

    --setting to 'null' for now

    --CR is 'In Progress' and Support Group match.

    WHERE cr_status.DisplayName = @strState AND CR_SupportGroup.DisplayName = @strGroup

    ) AS ma_ra_union

    ORDER BY CR_Create_Date, CR, Activity_Order

Sign In or Register to comment.