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
Query to show Problems, its associated incidents and change_ (MSSQL)
Tested in Build MSSQL (14306) SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency", "statdef"."STATUSNAME" AS "Problem Status", ...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 ...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 >= ...Query to display additional field associations with templates (MSSQL)
Tested in build MSSQL (14306) Use case The reports shows in which templates the created additional fields are associated Query select sd.name "Service Catalog Name", rt.templatename "Template name", fc.field_name "Fields" from requesttemplate_list rt ...Change Account for requester and in requests (Not applicable for builds above 9416 due to behavior change)
If the intent is to move a request from one account to another, you will have to first move the requester to the correct account, and then you will be able to change the account in the requests. 1. Go to Admin - > Account details - > Requesters. Go ...