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 ...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 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", ...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. ...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 >= ...