Query_ Changes approved by(approvals tab) and request ID associated to it.

Query_ Changes approved by(approvals tab) and request ID associated to it.

These are in reference to change initiated due to Requests and who approved the change approval stage alone.


MSSQL:


SELECT "chdt"."CHANGEID" AS "Change ID", "chdt"."TITLE" AS "Title", LONGTODATE("chdt"."CREATEDTIME") AS "Created Time", "chdt"."SCHEDULEDSTARTTIME" AS "Scheduled Start Time", "chdt"."SCHEDULEDENDTIME" AS "Scheduled End", "chdt"."COMPLETEDTIME" AS "Completed Time", "orgaaa"."FIRST_NAME" AS "Change Requester", "ownaaa"."FIRST_NAME" AS "Change Owner", "priodef1"."PRIORITYNAME" AS "Priority", "urgdef"."NAME" AS "Urgency", "ctdef"."NAME" AS "Change Type", "oldStageDef"."NAME" AS "Old Status", "approvaldef"."STATUSNAME" AS "Approval Status", "catadef"."CATEGORYNAME" AS "Category", "subcatadef"."NAME" AS "Subcategory", "itemdef1"."NAME" AS "Item", "impactdef"."NAME" AS "Impact", "chargeTable"."TIMESPENT" AS "Time Spent", "chargeTable"."TOTAL_CHARGE" AS "Total Charges", "chargeTable"."TS_STARTTIME" AS "Time Spent Start Date", "chargeTable"."TS_ENDTIME" AS "Time Spent End Date", "ownaaa1"."FIRST_NAME" AS "Time Spent Technician", "chandes"."FULL_DESCRIPTION" AS "Description", "sdo"."NAME" AS "Site", "riskDef"."NAME" AS "Risk", "qd"."QUEUENAME" AS "Group", "orgsd"."ISVIPUSER" AS "VIP User", "clcodeDef"."NAME" AS "Change Closure Code", "rfc"."NAME" AS "Reason For Change", "stageDef"."DISPLAYNAME" AS "Stage", "statusDef"."STATUSDISPLAYNAME" AS "Status", "wfDef"."NAME" AS "Workflow", "tempDef"."NAME" AS "Change Template", "cmDef"."FIRST_NAME" AS "Change Manager", "chdt"."ISRETROSPECTIVE" AS "Retrospective", "chsladef"."SLANAME" AS "SLA Name", "chdt"."ISOVERDUE" AS "SLA violated", "chanf"."UDF_DATE1" AS "Implementation Start Date", "chanf"."UDF_DATE2" AS "Implementation End Date", "ad"."ORG_NAME" AS "Account",wo.WORKORDERID "Request ID",approved_by_table.approved_user "Approved By" FROM "ChangeDetails" "chdt" LEFT JOIN "Change_Fields" "chanf" ON "chdt"."CHANGEID"="chanf"."CHANGEID" LEFT JOIN "SDUser" "orgsd" ON "chdt"."INITIATORID"="orgsd"."USERID" LEFT JOIN "AaaUser" "orgaaa" ON "orgsd"."USERID"="orgaaa"."USER_ID" LEFT JOIN "SDUser" "ownsd" ON "chdt"."TECHNICIANID"="ownsd"."USERID" LEFT JOIN "AaaUser" "ownaaa" ON "ownsd"."USERID"="ownaaa"."USER_ID" LEFT JOIN "PriorityDefinition" "priodef1" ON "chdt"."PRIORITYID"="priodef1"."PRIORITYID" LEFT JOIN "UrgencyDefinition" "urgdef" ON "chdt"."URGENCYID"="urgdef"."URGENCYID" LEFT JOIN "ChangeTypeDefinition" "ctdef" ON "chdt"."CHANGETYPEID"="ctdef"."CHANGETYPEID" LEFT JOIN "StageDefinition" "oldStageDef" ON "chdt"."STAGEID"="oldStageDef"."STAGEID" LEFT JOIN "ApprovalStatusDefinition" "approvaldef" ON "chdt"."APPR_STATUSID"="approvaldef"."STATUSID" LEFT JOIN "CategoryDefinition" "catadef" ON "chdt"."CATEGORYID"="catadef"."CATEGORYID" LEFT JOIN "SubCategoryDefinition" "subcatadef" ON "chdt"."SUBCATEGORYID"="subcatadef"."SUBCATEGORYID" LEFT JOIN "ItemDefinition" "itemdef1" ON "chdt"."ITEMID"="itemdef1"."ITEMID" LEFT JOIN "ImpactDefinition" "impactdef" ON "chdt"."IMPACTID"="impactdef"."IMPACTID" LEFT JOIN "ChangeToCharge" "changeCharge" ON "chdt"."CHANGEID"="changeCharge"."CHANGEID" LEFT JOIN "ChargesTable" "chargeTable" ON "changeCharge"."CHARGEID"="chargeTable"."CHARGEID" LEFT JOIN "AaaUser" "ownaaa1" ON "chargeTable"."TECHNICIANID"="ownaaa1"."USER_ID" LEFT JOIN "ChangeToDescription" "chandes" ON "chdt"."CHANGEID"="chandes"."CHANGEID" LEFT JOIN "RiskDefinition" "riskDef" ON "chdt"."RISKID"="riskDef"."RISKID" LEFT JOIN "SiteDefinition" "siteDef" ON "chdt"."SITEID"="siteDef"."SITEID" LEFT JOIN "SDOrganization" "sdo" ON "siteDef"."SITEID"="sdo"."ORG_ID" LEFT JOIN "QueueDefinition" "qd" ON "chdt"."GROUPID"="qd"."QUEUEID" LEFT JOIN "ReasonForChangeDetails" "rfc" ON "chdt"."REASONFORCHANGEID"="rfc"."ID" LEFT JOIN "Change_StageDefinition" "stageDef" ON "chdt"."WFSTAGEID"="stageDef"."WFSTAGEID" LEFT JOIN "Change_StatusDefinition" "statusDef" ON "chdt"."WFSTATUSID"="statusDef"."WFSTATUSID" LEFT JOIN "ChangeWF_Definition" "wfDef" ON "chdt"."WFID"="wfDef"."ID" LEFT JOIN "ChangeTemplate" "tempDef" ON "chdt"."TEMPLATEID"="tempDef"."TEMPLATEID" LEFT JOIN "AaaUser" "cmDef" ON "chdt"."CHANGEMANAGERID"="cmDef"."USER_ID" LEFT JOIN "ChangeToClosureCode" "clcodeMapDef" ON "chdt"."CHANGEID"="clcodeMapDef"."CHANGEID" LEFT JOIN "Change_ClosureCode" "clcodeDef" ON "clcodeMapDef"."ID"="clcodeDef"."ID" LEFT JOIN "ChangeSLADef" "chsladef" ON "chdt"."SLAID"="chsladef"."SLAID" INNER JOIN "AccountSiteMapping" "asm" ON "chdt"."siteid"="asm"."siteid" INNER JOIN "AccountDefinition" "ad" ON "asm"."accountid"="ad"."org_id" INNER JOIN IncidentToChangeMapping icm ON chdt.CHANGEID=icm.CHANGEID LEFT JOIN WorkOrder wo ON icm.WORKORDERID=wo.WORKORDERID  LEFT JOIN (SELECT role.changeid, max(us.first_name) AS name FROM changeroleusermapping ROLE LEFT JOIN aaauser us ON ROLE.userid=us.user_id  WHERE ROLE.roleid=5 GROUP BY ROLE.changeid)app ON chdt.changeid=app.changeid LEFT JOIN (SELECT comments.changeid, commenteduser.first_name AS approved_user FROM changestatuscomments comments LEFT JOIN sduser "sdu1" ON  comments.commentedby=sdu1.userid LEFT JOIN aaauser "commenteduser" ON sdu1.userid= commenteduser.user_id LEFT JOIN change_statusdefinition cstd ON comments.wfstatusid=cstd.wfstatusid LEFT JOIN change_stagedefinition csgd ON comments.wfstageid=csgd.wfstageid WHERE statusname='Approved' and csgd.name='Approval')approved_by_table ON chdt.changeid=approved_by_table.changeid

          • Related Articles

          • Query to show requests and its associations with Problems, Changes and Projects

            MSSQL: SELECT wo.WORKORDERID AS "Request ID", pim.problemid "Associated Problem ID", icm.changeid "Associated Change ID", wtp.projectid "Associated Project ID", CASE WHEN (wo.is_catalog_template) = 'false' THEN 'Incident' ELSE 'Service Request' END ...
          • Query to show Problems, its associated incidents and change_ MSSQL

            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", "impactdef"."NAME" AS "Problem Impact", ...
          • Query to show Approved tickets per Approver

            PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", std.STATUSNAME "Request Status", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", ApprovalDetails.EMAIL "Approver Email", asd.stagename "Stage", ...
          • Query to show Change Roles in change requests

            MSSQL: SELECT "chdt"."CHANGEID" AS "Change ID", "chdt"."TITLE" AS "Title", LONGTODATE("chdt"."CREATEDTIME") AS "Created Time", LONGTODATE("chdt"."SCHEDULEDSTARTTIME") AS "Scheduled Start Time", LONGTODATE("chdt"."SCHEDULEDENDTIME") AS "Scheduled ...
          • Query to list the pending approvals of a Service Approver

            PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE "Subject",ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time",appsau.first_name "Approver", asd.statusname "Approval Status" FROM ...