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 End", longtodate("chdt"."COMPLETEDTIME") AS "Completed Time", "orgaaa"."FIRST_NAME" AS "Change Requester", "ownaaa"."FIRST_NAME" AS "Change Owner",

chgimplr.first_name as "Implementer",chgappr.first_name as "Change Approver",chgrev.first_name as "Change Reviewer",chglm.first_name as "Line Manager", 

"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", "sdo"."NAME" AS "Site", "riskDef"."NAME" AS "Risk", "qd"."QUEUENAME" AS "Group", "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",longtodate(subtime.commentedon) as "Submission Time" ,longtodate(plantime.commentedon) as "Planning Time",longtodate(apptime.commentedon) as "Approval Time",longtodate(imptime.commentedon) as "Implementation Time",longtodate(reviewtime.commentedon) as "Review Time",longtodate(closetime.commentedon) as "Close Time"  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 "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 "RiskDefinition" "riskDef" ON "chdt"."RISKID"="riskDef"."RISKID" LEFT JOIN "QueueDefinition" "qd" ON "chdt"."GROUPID"="qd"."QUEUEID" LEFT JOIN "SiteDefinition" "siteDef" ON "chdt"."SITEID"="siteDef"."SITEID" LEFT JOIN "SDOrganization" "sdo" ON "siteDef"."SITEID"="sdo"."ORG_ID" 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 (select min(csc.commentedon) as commentedon,csc.changeid  from changestatuscomments csc left join change_Stagedefinition cstd on cstd.wfstageid = csc.wfstageid where cstd.name = 'Submission' group by csc.changeid)subtime on chdt.changeid=subtime.changeid  left join (select min(csc.commentedon)  as commentedon,csc.changeid  from changestatuscomments csc left join change_Stagedefinition cstd on cstd.wfstageid = csc.wfstageid  where cstd.name = 'Planning' group by csc.changeid)plantime on chdt.changeid=plantime.changeid left join (select min(csc.commentedon) as commentedon,csc.changeid  from changestatuscomments csc left join change_Stagedefinition cstd on cstd.wfstageid = csc.wfstageid  where cstd.name = 'Approval' group by csc.changeid )apptime on chdt.changeid=apptime.changeid left join (select min(csc.commentedon) as commentedon,csc.changeid  from changestatuscomments csc left join change_Stagedefinition cstd on cstd.wfstageid = csc.wfstageid left join change_statusdefinition cstsd on cstsd.wfstatusid = csc.wfstatusid where cstd.name = 'Implementation' group by csc.changeid )imptime on chdt.changeid=imptime.changeid  left join (select max(csc.commentedon) as commentedon,csc.changeid  from changestatuscomments csc left join change_Stagedefinition cstd on cstd.wfstageid = csc.wfstageid  where  cstd.name = 'Review' group by csc.changeid )reviewtime on chdt.changeid=reviewtime.changeid  left join (select min(csc.commentedon) as commentedon,csc.changeid  from changestatuscomments csc left join change_Stagedefinition cstd on cstd.wfstageid = csc.wfstageid  where cstd.name = 'Close' group by csc.changeid )closetime on chdt.changeid=closetime.changeid LEFT JOIN (select chgrolemap.changeid, implr.first_name from changeroleusermapping chgrolemap inner join changeroles chgrole on (chgrolemap.roleid = chgrole.id and chgrole.name='Implementer') left join aaauser implr on chgrolemap.userid = implr.user_id) chgimplr on chdt.changeid = chgimplr.changeid LEFT JOIN (select chgrolemap.changeid, appr.first_name from changeroleusermapping chgrolemap inner join changeroles chgrole on (chgrolemap.roleid = chgrole.id and chgrole.name='ChangeApprover') left join aaauser appr on chgrolemap.userid = appr.user_id) chgappr on chdt.changeid = chgappr.changeid  LEFT JOIN (select chgrolemap.changeid, rev.first_name from changeroleusermapping chgrolemap inner join changeroles chgrole on (chgrolemap.roleid = chgrole.id and chgrole.name='Reviewer') left join aaauser rev on chgrolemap.userid = rev.user_id) chgrev on chdt.changeid = chgrev.changeid LEFT JOIN (select chgrolemap.changeid, lm.first_name from changeroleusermapping chgrolemap inner join changeroles chgrole on (chgrolemap.roleid = chgrole.id and chgrole.name='Line Manager') left join aaauser lm on chgrolemap.userid = lm.user_id) chglm on chdt.changeid = chglm.changeid  WHERE  ( ( ( "chdt"."CREATEDTIME" >= 1585679400000) AND ( ( "chdt"."CREATEDTIME" != 0 ) AND ( "chdt"."CREATEDTIME" IS NOT NULL ) ) ) AND ( ( "chdt"."CREATEDTIME" <=1663180199000 ) AND ( ( ( "chdt"."CREATEDTIME" != 0 ) AND ( "chdt"."CREATEDTIME" IS NOT NULL ) ) AND ( "chdt"."CREATEDTIME" != -1 ) ) ) )