Query to show Detailed Report of Change requests Within an Account (Not working )
Compatible with both POSTGRES and MS-SQL:
Tested in build PGSQL (14300) and MSSQL (14306)
SELECT chdt.CHANGEID "Change ID",chdt.TITLE "Title", longtodate(chdt.CREATEDTIME) "Created Time", longtodate(chdt.SCHEDULEDSTARTTIME)"Scheduled Start Time",longtodate(chdt.SCHEDULEDENDTIME) "Scheduled End",longtodate(chdt.COMPLETEDTIME) "Completed Time",orgaaa.FIRST_NAME "ChangeRequester",ownaaa.FIRST_NAME "ChangeOwner",priodef1.PRIORITYNAME "Priority",urgdef.NAME "Urgency",ctdef.NAME "Change Type",oldStageDef.NAME "Old Status",approvaldef.STATUSNAME "Approval Status",catadef.CATEGORYNAME "Category",subcatadef.NAME "Subcategory",itemdef1.NAME "Item",impactdef.NAME "Impact",chargeTable.TIMESPENT "Time Spent",chargeTable.TOTAL_CHARGE "Total Charges",longtodate(chargeTable.TS_STARTTIME) "Time Spent Start Date", longtodate(chargeTable.TS_ENDTIME) "Time Spent End Date",ownaaa1.FIRST_NAME "Time Spent Technician",chandes.FULL_DESCRIPTION "Description",sdo.NAME "Site",riskDef.NAME "Risk",qd.QUEUENAME "Group",orgsd.ISVIPUSER "VIP User",clcodeDef.NAME "Change Closure Code",rfc.NAME "Reason For Change",stageDef.DISPLAYNAME "Stage",statusDef.STATUSDISPLAYNAME "Status",wfDef.NAME "Workflow",tempDef.NAME "Change Template",cmDef.FIRST_NAME "ChangeManager",chdt.ISRETROSPECTIVE "Retrospective",ad.ORG_NAME "Account" FROM ChangeDetails chdt
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 SiteDefinition siteDef ON chdt.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN RiskDefinition riskDef ON chdt.RISKID=riskDef.RISKID
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
INNER JOIN AccountSiteMapping asm ON chdt.siteid=asm.siteid
INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id WHERE (asm.ACCOUNTID IN (1,3,602))
Also, the WHERE criteria can be replaced with SITEID as shown below:
WHERE (chdt.SITEID IN (3301,2,6603,3302))
Not working on Latest builds
New to ADSelfService Plus?
Related Articles
Query to generate the report based on the total number of requests actioned by the technicians (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) This is a comprehensive report on the total number of requests updated by a technicians. This query provides a holistic view of technician activity like updating resolution process, such as updating or ...Query to retrieve the requests that are created and completed within the given time frame
Tested in: 14610 & 14301 The following queries will return the IDs and counts of requests created within a given time frame and completed (Resolved, Closed, Cancelled) within the same time frame. QUERY FOR INCIDENT REQUESTS: SELECT ...Query report to show Open requests without open tasks
PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...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", ...Query to retrieve both the live and archived requests details
Database: Pgsql Query: SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", id.NAME AS "Impact", ud.NAME AS "Urgency", rtd.NAME AS "Request Type", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved ...