Query to show an additional column action pending by in change request

Query to show an additional column action pending by in change request

Database: MSSQL

SELECT "chdt"."CHANGEID" AS "Change ID", "chdt"."TITLE" AS "Title", LONGTODATE(chdt.CREATEDTIME) AS "Created Time", "ownaaa"."FIRST_NAME" AS "ChangeOwner", STUFF((SELECT ', '+ au.first_name + char(10)   FROM  changeroleusermapping crum 
left join aaauser au  on au.user_id=crum.userid 
where chdt.changeid=crum.changeid and crum.roleid=7 FOR XML PATH ('')), 1, 1, '') 'Implementer', STUFF((SELECT ', '+ au.first_name + char(10)   FROM  changeroleusermapping crum 
left join aaauser au  on au.user_id=crum.userid 
where chdt.changeid=crum.changeid and crum.roleid=8 FOR XML PATH ('')), 1, 1, '') 'Reviewer', "stageDef"."DISPLAYNAME" AS "Stage", CASE WHEN stageDef.DISPLAYNAME='Implementation' THEN STUFF((SELECT ', '+ au.first_name + char(10)   FROM  changeroleusermapping crum 
left join aaauser au  on au.user_id=crum.userid 
where chdt.changeid=crum.changeid and crum.roleid=7 FOR XML PATH ('')), 1, 1, '') WHEN stageDef.DISPLAYNAME='Review' THEN STUFF((SELECT ', '+ au.first_name + char(10)   FROM  changeroleusermapping crum 
left join aaauser au  on au.user_id=crum.userid 
where chdt.changeid=crum.changeid and crum.roleid=8 FOR XML PATH ('')), 1, 1, '') ELSE ownaaa.FIRST_NAME END AS "ACTION PENDINGBY", "statusDef"."STATUSDISPLAYNAME" AS "Status"  FROM "ChangeDetails" "chdt" LEFT JOIN "SDUser" "ownsd" ON "chdt"."TECHNICIANID"="ownsd"."USERID" LEFT JOIN "AaaUser" "ownaaa" ON "ownsd"."USERID"="ownaaa"."USER_ID" LEFT JOIN "QueueDefinition" "qd" ON "chdt"."GROUPID"="qd"."QUEUEID" LEFT JOIN "Change_StageDefinition" "stageDef" ON "chdt"."WFSTAGEID"="stageDef"."WFSTAGEID" LEFT JOIN "Change_StatusDefinition" "statusDef" ON "chdt"."WFSTATUSID"="statusDef"."WFSTATUSID" INNER JOIN "AccountSiteMapping" "asm" ON "chdt"."SITEID"="asm"."SITEID" INNER JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID" WHERE  ( ( ( ( "statusDef"."STATUSDISPLAYNAME" COLLATE SQL_Latin1_General_CP1_CS_AS != N'Canceled' ) AND ( "statusDef"."STATUSDISPLAYNAME" COLLATE SQL_Latin1_General_CP1_CS_AS != N'Completed' ) AND ( "statusDef"."STATUSDISPLAYNAME" COLLATE SQL_Latin1_General_CP1_CS_AS != N'Approval Pending' ) ) ) )

Query is applicable for latest builds too

                  New to ADSelfService Plus?

                    • Related Articles

                    • How to populate user names in a request additional field

                      This sample script is to populate both technician and requester names in a request additional field Use Case: Populate all user name in a request additional field. Execution Steps:  1.  Create a new 'Single Line' additional field and add them in the ...
                    • Request with ServiceCatalog Additional fields

                      Service catalog additional fields are category specific fields. These fields are stored in a dynamic table, so we need to join the specific template to get the resultant.  To make any changes to a query, refer to the KB article below. ...
                    • Change Submission and Planning stage details (PGSQL)

                      Tested in build PGSQL (14300) SELECT cdt.changeid "Change Request ID", orgaaa.FIRST_NAME AS "Change Requester", ownaaa.FIRST_NAME AS "Change Owner", cmDef.FIRST_NAME AS "Change Manager", manager.name "Line Manager", implementer.name "Change ...
                    • Pending Request for more than 10 days

                      This report provides a quick view of all Pending incidents which are not closed for more than 10 days. When the incidents start backlogging faster than they can be resolved, the tendency of long incidents pending time can be exposed in this report. ...
                    • Query to show Created, Pending and Completed Requests ( PGSQL & MSSQL )

                      Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT count(wo.workorderid) "Total Created Last Week", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Pending from Last Week", count(case when wo.completedtime >= ...