select COUNT(DISTINCT O.REC_ID) as COUNT, TRUNC(I.START_DATE) as START_DATE, I.START_DATE as START_DATE_TIME, TRIM(TO_CHAR(I.START_DATE,'DAY')) as DAY, TRIM(TO_CHAR(I.START_DATE,'MONTH')) as MONTH, TRIM(TO_CHAR(I.START_DATE,'YYYY')) as YEAR, TRIM(TO_CHAR(I.START_DATE,'HH24MI')) as TIME, I.AGNCY_CD_AGENCY_CODE as AGENCY_CODE, LC.CODE AS LAYER_CODE, LC.DESCRIPTION AS LAYER_DESC from INCIDENTS I INNER JOIN OFFENSES O ON I.INCIDENT_ID = O.INC_INCIDENT_ID INNER JOIN NIBRS_OFFENSES NO ON O.OFFNS_CD_OFFENSE_CODE = NO.OFFENSE_CODE INNER JOIN NIBRS_CODES NC ON NO.NIBRS_CODE = NC.NIBRS_CODE INNER JOIN EJS_CODES LC ON NC.LAYER_CODE = LC.CODE AND NC.LAYER_CODE_TYPE = LC.CODE_TYPE WHERE I.START_DATE IS NOT NULL AND O.SUPP_SEQ = (SELECT MAX(O2.SUPP_SEQ) FROM OFFENSES O2 WHERE O2.INC_INCIDENT_ID = I.INCIDENT_ID AND O2.OFFNS_CD_OFFENSE_CODE = O.OFFNS_CD_OFFENSE_CODE) GROUP BY I.AGNCY_CD_AGENCY_CODE, TRUNC(I.START_DATE), I.START_DATE, TO_CHAR(I.START_DATE,'DAY'), TO_CHAR(I.START_DATE,'MONTH'), TO_CHAR(I.START_DATE,'YYYY'), TO_CHAR(I.START_DATE,'HH24MI'), LC.CODE, LC.DESCRIPTION ORDER BY TRUNC(I.START_DATE) DESC