select chdt.changeid AS "Change Request Number",

ctDef.NAME as "Change Type",

requester.FIRST_NAME as "Change Requester",

owner.FIRST_NAME as "Change Owner",

chdt.title as "Title",

QueueDef.QUEUENAME as "Group",

longtodate(chdt.createdtime) as "Created Time",

longtodate(chdt.COMPLETEDTIME) AS "Completed Time",

statusDef.STATUSNAME as "Status",

appr.STATUSNAME as "Approval Status",

ctd.FULL_DESCRIPTION as "Description",

rfc.NAME as "Reason for change",

priDef.PRIORITYNAME as "Priority",

urgDef.NAME as "Urgency",

stageDef.NAME as "Stage",

wf.WORKFLOWNAME as "Workflow",

manager.FIRST_NAME as "Change Manager",

temp.NAME as "Change Template",

impDef.NAME as "Impact",

riskDef.NAME as "Risk",

chdt.DESCRIPTION as "Short Description",

cf.UDF_CHAR2 as "Short Description",

cf.UDF_CHAR3 as "Service Affected",

longtodate(chdt.scheduledstarttime) as "CR Scheduled Start",

longtodate(chdt.scheduledendtime) as "CR Scheduled End",

longtodate(initial_task.actual_start) as "CR Actual Start",

longtodate(closure_task.actual_end) as "CR Actual End",

case when (initial_task.actual_start >= chdt.scheduledstarttime) and (closure_task.actual_end <=

chdt.scheduledendtime) then 'False' else 'True'

End as "Overall CR Scheduled Violation status",

longtodate(window_task.scheduled_start) "CR_Service Downtime Window Scheduled Start",

longtodate(window_task.scheduled_end)  "CR_Service Downtime Window Scheduled End",

longtodate(window_task.actual_start) "CR_Service Downtime Window Actual Start",

longtodate(window_task.actual_end) "CR_Service Downtime Window Actual End",

case when (window_task.actual_start >= window_task.scheduled_start) and

(window_task.actual_end <= window_task.scheduled_end) then 'False' else 'True'

End as "Service Downtime Window Violation status",

longtodate(duration_task.scheduled_start) "CR_System Downtime Window Scheduled Start",

longtodate(duration_task.scheduled_end) "CR_System Downtime Window Scheduled End",

longtodate(duration_task.actual_start) "CR_System Downtime Window Actual Start",

longtodate(duration_task.actual_end) "CR_System Downtime Window Actual End",

case when (duration_task.actual_start >= duration_task.scheduled_start) and

(duration_task.actual_end <= duration_task.scheduled_end) then 'False' else 'True'

End as "System Downtime Window Violation status" from changedetails chdt  left join

(select ctt.changeid,win_task.scheduledstarttime as "scheduled_start",

win_task.scheduledendtime as "scheduled_end",win_task.actualstarttime as

"actual_start",win_task.actualendtime as "actual_end",win_task.isoverdue as "violation_status"

from changedetails cha_det LEFT JOIN ChangeToTaskDetails ctt ON ctt.CHANGEID =

cha_det.CHANGEID

LEFT JOIN TaskDetails win_task ON win_task.TASKID =ctt.TASKID where win_task.title like

'%Service Downtime%')window_task on window_task.changeid = chdt.changeid left join

(select ctt.changeid,duration.scheduledstarttime as "scheduled_start", duration.scheduledendtime

as "scheduled_end",duration.actualstarttime as "actual_start",duration.actualendtime as

"actual_end",duration.isoverdue as "violation_status" from changedetails cha_det LEFT JOIN

ChangeToTaskDetails ctt ON ctt.CHANGEID = cha_det.CHANGEID

LEFT JOIN TaskDetails duration ON duration.TASKID =ctt.TASKID where duration.title like

'%System Downtime%')duration_task on duration_task.changeid = chdt.changeid

LEFT JOIN ChangeTypeDefinition ctDef ON  ctDef.CHANGETYPEID =

chdt.CHANGETYPEID

LEFT JOIN CategoryDefinition catDef ON  catDef.CATEGORYID = chdt.CATEGORYID

LEFT JOIN AaaUser requester ON requester.USER_ID = chdt.INITIATORID

LEFT JOIN AaaUser owner ON owner.USER_ID = chdt.TECHNICIANID

LEFT JOIN QueueDefinition queueDef ON queueDef.QUEUEID = chdt.GROUPID

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 Change_StatusDefinition statusDef ON statusDef.WFSTATUSID = chdt.WFSTATUSID

LEFT JOIN ApprovalStatusDefinition appr ON appr.STATUSID = chdt.APPR_STATUSID

LEFT JOIN ChangeToDescription ctd ON ctd.CHANGEID = chdt.CHANGEID

LEFT JOIN ReasonForChangeDetails rfc ON rfc.ID = chdt.REASONFORCHANGEID

LEFT JOIN ChangeToService cserv ON cserv.CHANGEID = chdt.CHANGEID

LEFT JOIN Change_Fields cf on chdt.changeid=cf.changeid

LEFT JOIN ServiceDefinition servDef ON servDef.SERVICEID = cserv.SERVICEID

LEFT JOIN PriorityDefinition priDef ON priDef.PRIORITYID = chdt.PRIORITYID

LEFT JOIN UrgencyDefinition urgDef ON urgDef.URGENCYID = chdt.URGENCYID

LEFT JOIN Change_StageDefinition stageDef ON stageDef.WFSTAGEID = chdt.WFSTAGEID  

LEFT JOIN Workflow wf ON wf.workflowid = chdt.WFID

LEFT JOIN Change_ClosureCode ccode ON ccode.ID = chdt.CLOSURECODEID

LEFT JOIN AaaUser manager ON manager.USER_ID = chdt.CHANGEMANAGERID

LEFT JOIN ChangeTemplate temp ON temp.TEMPLATEID = chdt.TEMPLATEID

LEFT JOIN ImpactDefinition impDef ON impDef.IMPACTID = chdt.IMPACTID

LEFT JOIN ItemDefinition itemDef ON itemDef.ITEMID = chdt.ITEMID

LEFT JOIN RiskDefinition riskDef ON riskDef.RISKID = chdt.RISKID

LEFT JOIN SdOrganization sdOrg ON sdOrg.ORG_ID = chdt.SITEID

LEFT JOIN ChangeSlaDef csla ON csla.slaid = chdt.slaid

LEFT JOIN SubCategoryDefinition subDef ON  subDef.SUBCATEGORYID =

chdt.SUBCATEGORYID

LEFT JOIN (select ctt.changeid,ini_task.scheduledstarttime as "scheduled_start",

ini_task.scheduledendtime as "scheduled_end",ini_task.actualstarttime as

"actual_start",ini_task.actualendtime as "actual_end",ini_task.isoverdue as "violation_status"

from changedetails cha_det LEFT JOIN ChangeToTaskDetails ctt ON ctt.CHANGEID =

cha_det.CHANGEID

LEFT JOIN TaskDetails ini_task ON ini_task.TASKID =ctt.TASKID where ini_task.title like

'%CR_Initial Phase%')initial_task on initial_task.changeid = chdt.changeid

LEFT JOIN (select ctt.changeid,closure_task.scheduledstarttime as "scheduled_start",

closure_task.scheduledendtime as "scheduled_end",closure_task.actualstarttime as

"actual_start",closure_task.actualendtime as "actual_end",closure_task.isoverdue as

"violation_status" from changedetails cha_det LEFT JOIN ChangeToTaskDetails ctt ON

ctt.CHANGEID = cha_det.CHANGEID LEFT JOIN TaskDetails closure_task ON

closure_task.TASKID =ctt.TASKID where closure_task.title like 'CR_Review and Closure Task (SOC)%')closure_task on closure_task.changeid = chdt.changeid

where (CONVERT(datetime,((dateadd(second,chdt.scheduledstarttime /1000 + chdt.scheduledstarttime % 1000 + 6*60*60, '19700101'))))) between '2021-10-21 00:01:00.000' and '2021-12-19 23:59:59.000'