SELECT DE.DISPATCH_ID AS DISPATCH_ID, DE.CALL_RECEIVED_CODE AS CALL_RECEIVED_CODE, NVL(CRC.DESCRIPTION,DE.CALL_RECEIVED_CODE) AS CALL_RECEIVED_DESC, DE.CALL_PRIORITY AS CALL_PRIORITY_CODE, DPC.DESCRIPTION AS CALL_PRIORITY_DESC, DE.CALL_TYPE AS CALL_TYPE_CODE, NVL(CTC.CALL_TYPE_DESC,DE.CALL_TYPE) AS CALL_TYPE_DESC, DET.EVENT_TYPE_CODE, DET.DESCRIPTION AS EVENT_TYPE_DESC, CDC.CALL_DISPOSITION_CODE AS DISPOSITION_TYPE_CODE, CDC.CALL_DISPOSITION_DESC AS DISPOSITION_TYPE_DESC, PRIMARY_OFFICERS.OFFICER_ID AS PRIMARY_OFFICER_ID, PRIMARY_OFFICERS.CAD_PERSID PRIMARY_CAD_PERSON_ID, PRIMARY_OFFICERS.OFFICER_LNAME ||', '|| PRIMARY_OFFICERS.OFFICER_FNAME AS PRIMARY_OFFICER_NAME, SERVICE_AGENCY.AGENCY_CODE AS PRIMARY_SERVICE_AGENCY_CODE, SERVICE_AGENCY.AGENCY_DESC AS PRIMARY_SERVICE_AGENCY_DESC, NVL(SERVICE_ORG.AGENCY_CODE,SERVICE_AGENCY.AGENCY_CODE) AS PRIMARY_SERVICE_ORG_CODE, NVL(SERVICE_ORG.AGENCY_DESC,SERVICE_AGENCY.AGENCY_DESC) AS PRIMARY_SERVICE_ORG_DESC, COUNT(DISTINCT(ALL_OFFICERS.SERVICE_AGENCY)) AS SERVICE_AGENCY_COUNT, RESPONDING_AGENCY.AGENCY_CODE AS PRIMARY_RESPONDING_AGENCY_CODE, RESPONDING_AGENCY.AGENCY_DESC AS PRIMARY_RESPONDING_AGENCY_DESC, NVL(RESPONDING_ORG.AGENCY_CODE,RESPONDING_AGENCY.AGENCY_CODE) AS PRIMARY_RESPONDING_ORG_CODE, NVL(RESPONDING_ORG.AGENCY_DESC,RESPONDING_AGENCY.AGENCY_DESC) AS PRIMARY_RESPONDING_ORG_DESC, COUNT(DISTINCT(ALL_OFFICERS.OFFICER_AGENCY)) AS RESPONDING_AGENCY_COUNT, DE.AGENCY AS CAD_AGENCY_CODE, CAD_AGENCY.AGENCY_DESC AS CAD_AGENCY_DESC, NVL(CAD_ORG.AGENCY_CODE,DE.AGENCY) AS CAD_ORG_CODE, NVL(CAD_ORG.AGENCY_DESC,CAD_AGENCY.AGENCY_DESC) AS CAD_ORG_DESC, DE.DISPATCH_USER AS CALL_ONWER_ID, IJIS_OWNER.LNAME || ', ' || IJIS_OWNER.FNAME AS CALL_OWNER_NAME, DE.CALL_DATE AS CALL_DATE, DE.DISPATCH_TIME AS DISPATCH_DATE, DE.ONSCENE_DATE AS ONSCENE_DATE, MAX(ALL_OFFICERS.CLEAR_DATE) AS CLEAR_DATE, DE.CLOSE_DATE AS CLOSE_DATE, TO_CHAR(DE.CALL_DATE,'DAY') AS DAY_OF_WEEK, COUNT(DISTINCT(DISP_CASES.INCIDENT_ID)) AS INCIDENT_COUNT, COUNT(DISTINCT(DISP_ARRESTS.ARREST_ID)) AS ARREST_COUNT, -- Time To Dispatch (First Unit Dispatch Time - Call Time) TO_NUMBER(TO_CHAR( ( MIN(ALL_OFFICERS.DISPATCH_DATE) - DE.CALL_DATE )*24*60, '99999999999999.99' ) ) AS TIME_TO_DISPATCH_HRS, -- Time To On Scene (First Unit On Scene Time - Dispatch Time) TO_NUMBER(TO_CHAR( ( MIN(ALL_OFFICERS.ONSCENE_DATE) - DE.DISPATCH_DATE )*24*60, '99999999999999.99' ) ) AS TIME_TO_ON_SCENE_HRS, --Time On Scene (Max Clear Time - First On Scene Time) TO_NUMBER(TO_CHAR( ( MAX(ALL_OFFICERS.CLEAR_DATE) - MIN(ALL_OFFICERS.ONSCENE_DATE) )*24*60, '99999999999999.99' ) ) AS TIME_ON_SCENE_HRS, --Total Call Time (Close Date/Time - Call Date/Time) TO_NUMBER(TO_CHAR( ( DE.CLOSE_DATE - DE.CALL_DATE )*24*60, '99999999999999.99' ) ) AS TOTAL_CALL_TIME_HRS, --Total Man Hours On Scene (Sum of all units clear date/time - on scene time) TO_NUMBER(TO_CHAR( SUM(ALL_OFFICERS.CLEAR_DATE - ALL_OFFICERS.ONSCENE_DATE)*24*60, '99999999999999.99' ) ) AS TOTAL_MAN_HRS, ( SELECT COUNT(*) FROM DISPATCH_EVENT_OFFICERS DEO WHERE DEO.DISPATCH_ID = DE.DISPATCH_ID AND DEO.DISPATCH_DATE IS NOT NULL ) AS TOTAL_UNITS_DISPATCHED, ( SELECT COUNT(*) FROM DISPATCH_EVENT_OFFICERS DEO WHERE DEO.DISPATCH_ID = DE.DISPATCH_ID AND DEO.ONSCENE_DATE IS NOT NULL ) AS TOTAL_UNITS_ONSCENE, COUNT(ALL_OFFICERS.OFFICER_ID) AS TOTAL_OFFICER_COUNT, DE.LATITUDE AS LATITUDE, DE.LONGITUDE AS LONGITUDE, DE.STREET_NUMBER AS STREET_NUMBER, DE.STREET_DIRECTION AS STREET_DIR_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'DIRECTION_CODES' AND EC.CODE = DE.STREET_DIRECTION) AS STREET_DIR_DESC, DE.STREET_NAME AS STREET_NAME, DE.STREET_CD_STREET_TYPE_CODE AS STREET_TYPE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'STREET_TYPE_CODES' AND EC.CODE = DE.STREET_CD_STREET_TYPE_CODE) AS STREET_TYPE_DESC, DE.STREET_DIR_SUFFIX AS STREET_DIR_SUFFIX, DE.SUB_TYPE AS SUB_TYPE, DE.SUB_NUMBER AS SUB_NUMBER, DE.CITY AS CITY, DE.STATE AS STATE, DE.ZIP5 AS ZIP5, DE.ZIP4 AS ZIP4, DE.SUB_BEAT AS SUB_BEAT, DE.BEAT AS BEAT, DE.CROSS_STREET_NUMBER AS CROSS_STREET_NUMBER, DE.CROSS_STREET_DIRECTION AS CROSS_STREET_DIR_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'DIRECTION_CODES' AND EC.CODE = DE.CROSS_STREET_DIRECTION) AS CROSS_STREET_DIR_DESC, DE.CROSS_STREET AS CROSS_STREET, DE.CROSS_STREET_TYPE_CODE AS CROSS_STREET_TYPE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'STREET_TYPE_CODES' AND EC.CODE = DE.CROSS_STREET_TYPE_CODE) AS CROSS_STREET_TYPE_DESC, DE.CROSS_STREET_DIR_SUFFIX AS CROSS_STREET_DIR_SUFFIX, DE.BOUNDARY_STREET AS BOUNDARY_STREET, DE.MILE_POST_NBR AS MILE_POST_NBR, DE.DISTANCE_FROM AS DISTANCE_FROM, DE.DISTANCE_UNITS AS DISTANCE_UNITS, DE.DIRECTION_FROM AS DIRECTION_FROM FROM DISPATCH_EVENTS DE LEFT OUTER JOIN CALL_RECEIVED_CODES CRC ON CRC.CALL_RECEIVED_CODE = DE.CALL_RECEIVED_CODE AND DE.AGENCY = CRC.AGENCY_CODE LEFT OUTER JOIN EJS_CODES DPC ON DPC.CODE_TYPE = 'DISPATCH_PRIORITY_CODES' AND DPC.CODE = DE.CALL_PRIORITY LEFT OUTER JOIN CALL_TYPE_CODES CTC ON CTC.CALL_TYPE_CODE = DE.CALL_TYPE LEFT OUTER JOIN AGENCY_CODES CAD_AGENCY ON CAD_AGENCY.AGENCY_CODE = DE.AGENCY LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS POD ON POD.AGENCY_CODE = CAD_AGENCY.AGENCY_CODE LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS POD2 ON POD2.STRUCTURE_ID = POD.STRUCTURE_ID AND POD2.IS_ROOT = 'Y' LEFT OUTER JOIN AGENCY_CODES CAD_ORG ON CAD_ORG.AGENCY_CODE = POD2.AGENCY_CODE LEFT OUTER JOIN IJIS_USER_PROFILES IJIS_OWNER ON DE.DISPATCH_USER = IJIS_OWNER.LOGIN_ID LEFT OUTER JOIN DISPATCH_EVENT_TYPES DET ON DE.EVENT_TYPE_CODE = DET.EVENT_TYPE_CODE LEFT OUTER JOIN CALL_DISPOSITION_CODES CDC ON CDC.CALL_DISPOSITION_CODE = DE.CALL_DISPOSITION_CODE AND CDC.EVENT_TYPE_CODE = DE.EVENT_TYPE_CODE LEFT OUTER JOIN DISPATCH_EVENT_CASES DISP_CASES ON DISP_CASES.DISPATCH_ID = DE.DISPATCH_ID LEFT OUTER JOIN ARREST_DISPATCHES DISP_ARRESTS ON DISP_ARRESTS.DISPATCH_ID = DE.DISPATCH_ID LEFT OUTER JOIN DISPATCH_EVENT_OFFICERS PRIMARY_OFFICERS ON PRIMARY_OFFICERS.DISPATCH_ID = DE.DISPATCH_ID AND PRIMARY_OFFICERS.PRIMARY='Y' LEFT OUTER JOIN DISPATCH_EVENT_OFFICERS ALL_OFFICERS ON ALL_OFFICERS.DISPATCH_ID = DE.DISPATCH_ID LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS SERVICE_POD1 ON SERVICE_POD1.AGENCY_CODE = PRIMARY_OFFICERS.SERVICE_AGENCY LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS SERVICE_POD2 ON SERVICE_POD2.STRUCTURE_ID = SERVICE_POD1.STRUCTURE_ID AND SERVICE_POD2.IS_ROOT = 'Y' LEFT OUTER JOIN AGENCY_CODES SERVICE_ORG ON SERVICE_ORG.AGENCY_CODE = SERVICE_POD2.AGENCY_CODE LEFT OUTER JOIN AGENCY_CODES SERVICE_AGENCY ON SERVICE_AGENCY.AGENCY_CODE = PRIMARY_OFFICERS.SERVICE_AGENCY LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS RESPONDING_POD1 ON RESPONDING_POD1.AGENCY_CODE = PRIMARY_OFFICERS.OFFICER_AGENCY LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS RESPONDING_POD2 ON RESPONDING_POD2.STRUCTURE_ID = RESPONDING_POD1.STRUCTURE_ID AND RESPONDING_POD2.IS_ROOT = 'Y' LEFT OUTER JOIN AGENCY_CODES RESPONDING_ORG ON RESPONDING_ORG.AGENCY_CODE = RESPONDING_POD2.AGENCY_CODE LEFT OUTER JOIN AGENCY_CODES RESPONDING_AGENCY ON RESPONDING_AGENCY.AGENCY_CODE = PRIMARY_OFFICERS.OFFICER_AGENCY GROUP BY DE.DISPATCH_ID, DE.CALL_RECEIVED_CODE, CRC.DESCRIPTION, DE.CALL_PRIORITY, DPC.DESCRIPTION, DE.CALL_TYPE, CTC.CALL_TYPE_DESC, DET.EVENT_TYPE_CODE, DET.DESCRIPTION, CDC.CALL_DISPOSITION_CODE, CDC.CALL_DISPOSITION_DESC, PRIMARY_OFFICERS.OFFICER_ID, PRIMARY_OFFICERS.CAD_PERSID, PRIMARY_OFFICERS.OFFICER_LNAME, PRIMARY_OFFICERS.OFFICER_FNAME, PRIMARY_OFFICERS.SERVICE_AGENCY, PRIMARY_OFFICERS.OFFICER_AGENCY, DE.AGENCY, CAD_AGENCY.AGENCY_DESC, CAD_ORG.AGENCY_CODE, CAD_ORG.AGENCY_DESC, DE.DISPATCH_USER, IJIS_OWNER.LNAME, IJIS_OWNER.FNAME, DE.CALL_DATE, DE.DISPATCH_TIME, DE.ONSCENE_DATE, DE.CLOSE_DATE, DE.CALL_DATE, DE.DISPATCH_DATE, DE.LATITUDE, DE.LONGITUDE, DE.STREET_NUMBER, DE.STREET_DIRECTION, DE.STREET_NAME, DE.STREET_CD_STREET_TYPE_CODE, DE.STREET_DIR_SUFFIX, DE.SUB_TYPE, DE.SUB_NUMBER, DE.CITY, DE.STATE, DE.ZIP5, DE.ZIP4, DE.SUB_BEAT, DE.BEAT, DE.CROSS_STREET_NUMBER, DE.CROSS_STREET_DIRECTION, DE.CROSS_STREET, DE.CROSS_STREET_TYPE_CODE, DE.CROSS_STREET_DIR_SUFFIX, DE.BOUNDARY_STREET, DE.MILE_POST_NBR, DE.DISTANCE_FROM, DE.DISTANCE_UNITS, DE.DIRECTION_FROM, SERVICE_ORG.AGENCY_CODE, SERVICE_ORG.AGENCY_DESC, RESPONDING_ORG.AGENCY_CODE, RESPONDING_ORG.AGENCY_DESC, SERVICE_AGENCY.AGENCY_CODE, SERVICE_AGENCY.AGENCY_DESC, RESPONDING_AGENCY.AGENCY_CODE, RESPONDING_AGENCY.AGENCY_DESC