Version : 10524

DB : MSSQL

Output :

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",
"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",
"serdef"."name" AS "Services Affected",
"ci"."CINAME" AS "Assets Involved",
"ad"."ORG_NAME" AS "Account",
approved_by_table.approved_user "Change Approver" ,
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, '') '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=9 FOR XML PATH ('')), 1, 1, '') 'Reviewer',
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, '') 'Line Manager',
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=5 FOR XML PATH ('')), 1, 1, '') 'Change Approver',
"chstcom"."comments" AS "Status Comments",
( select count(iwc.workorderid) from incidenttochangemapping iwc left join ChangeDetails chdt1 on iwc.CHANGEID=chdt1.CHANGEID where chdt1.CHANGEID=chdt.CHANGEID ) AS "No. Of Ticket Associated to change Count",
cr.RolloutPlan "Roll Out Plan",
roll.First_name "Roll out plan by",
longtodate(cr.rolloutplan_on) "rolloutplan_on",
cr.BackOutPlan "Back Out Plan",
back.First_name "Back out plan by",
longtodate(cr.backoutplan_on) "backoutplan_on",
cr.CheckList "Check List",
chec.First_name "checklist_by",
longtodate(cr.checklist_on) "cr.checklist_on",
cr.Review "Review",
rev.First_name "review_by",
longtodate(cr.review_on) "Review on",
cr.impactdesc "Impact",
imp.First_name "impactdesc_by",
longtodate(cr.impactdesc_on) "Impact on",
longtodate(cr.next_review_on) "Next review on",
chdowntime.description "Downtime Description",
longtodate(chdowntime.starttime) "Downtime STARTTIME",
longtodate(chdowntime.endtime) "Downtime ENDTIME",
cabau.FIRST_NAME AS "Cab Member",
chtask.title "Task Title",
longtodate(chtask.actualstarttime) "Task Start Time",
longtodate(chtask.actualendtime) "Task End Time" 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 "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"
LEFT JOIN "changetoservice" "chtoser" ON "chdt"."changeid"="chtoser"."changeid"
LEFT JOIN "servicedefinition" "serdef" ON "chtoser"."serviceid"="serdef"."serviceid"
LEFT JOIN "changestatuscomments" "chstcom" ON "chdt"."changeid"="chstcom"."changeid"
LEFT JOIN "changetoci" "chci" ON "chdt"."changeid"="chci"."changeid"
LEFT JOIN "ci" "ci" ON "chci"."ciid"="ci"."ciid"
LEFT JOIN ChangeResolution cr ON chdt.CHANGEID=cr.CHANGEID
left join aaauser roll on cr.Rolloutplan_by=roll.user_id
left join aaauser back on cr.backoutplan_by=back.user_id
left join aaauser chec on cr.checklist_by=chec.user_id
left join aaauser rev on cr.review_by=rev.user_id
left join aaauser imp on cr.impactdesc_by=rev.user_id
LEFT JOIN "change_downtimedetails" "chdowntime" ON "chdt"."changeid"="chdowntime"."changeid"
LEFT JOIN Changetocab chcab ON chdt.CHANGEID=chcab.CHANGEID
LEFT JOIN "SDUser" "cabsd" ON "chcab"."userid"="cabsd"."USERID"
left join aaauser cabau on cabsd.userid=cabau.user_id
LEFT JOIN "changetotaskdetails" "chtotask" ON "chdt"."changeid"="chtotask"."changeid"
LEFT JOIN "taskdetails" "chtask" ON "chtotask"."TASKID"="chtask"."taskid"
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

Note : To avoid performance issue use date filter.