SELECT CIT.CITATION_ID AS CITATION_ID, CIT.TICKET_NUMBER AS TICKET_NUMBER, CIT.CITATION_TYPE_CODE AS CITATION_TYPE_CODE, CTC.DESCRIPTION AS CITATION_TYPE_DESC, CIT.AGENCY_CODE AS AGENCY_CODE, AC.AGENCY_DESC AS AGENCY_DESC, CIT.PLAINTIFF AS PLAINTIFF, CIT.STATE_DIST_CODE AS STATE_DIST_CODE, SDC.DESCRIPTION AS STATE_DIST_DESC, CIT.CITY_CODE AS CITY_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'CITY_CODES' AND EC.CODE = CIT.CITY_CODE) AS CITY_DESC, CIT.COUNTY_CODE AS COUNTY_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'COUNTY_CODES' AND EC.CODE = CIT.COUNTY_CODE) AS COUNTY_DESC, CIT.CITATION_DATE AS CITATION_DATE, CIT.FILED_DATE AS FILED_DATE, CIT.TWP_RD AS TWP_RD, CV.VEHICLE_ID AS VEHICLE_ID, VE.YEAR AS VEHICLE_YEAR, VE.VMA_VEHICLE_MAKE_CODE AS VEHICLE_MAKE_CODE, VMA.VEHICLE_MAKE_DESC AS VEHICLE_MAKE_DESC, VE.VMO_VEHICLE_MODEL_CODE AS VEHICLE_MODEL_CODE, VMO.VEHICLE_MODEL_DESC AS VEHICLE_MODEL_DESC, VE.VST_VEHICLE_STYLE_CODE AS VEHICLE_STYLE_CODE, VST.VEHICLE_STYLE_DESC AS VEHICLE_STYLE_DESC, VE.VTC_TYPE_CODE AS VEHICLE_TYPE_CODE, VTC.TYPE_DESC AS VEHICLE_TYPE_DESC, VD.LIC_TYPE_CODE AS LIC_TYPE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'LIC_TYPE_CODES' AND EC.CODE = VD.LIC_TYPE_CODE) AS LIC_TYPE_DESC, VD.LIC_STATE_CODE AS LIC_STATE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'STATE_CODES' AND EC.CODE = VD.LIC_STATE_CODE) AS LIC_STATE_DESC, VD.LIC_MONTH AS LIC_MONTH, VD.LIC_YEAR AS LIC_YEAR, CP.PERSON_ID AS PERSON_ID, MP.SEX_CODE AS PERSON_SEX_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'SEX_CODES' AND EC.CODE = MP.SEX_CODE) AS PERSON_SEX_DESC, MP.RACE_CODE AS PERSON_RACE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'RACE_CODES' AND EC.CODE = MP.RACE_CODE) AS PERSON_RACE_DESC, MP.ETHNICITY_CODE AS PERSON_ETHNICITY_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'ETHNICITY_CODES' AND EC.CODE = MP.ETHNICITY_CODE) AS PERSON_ETHNICITY_DESC, PD.AGE AS PERSON_AGE, PD.SKN_CD_SKIN_CODE AS PERSON_SKIN_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'SKIN_CODES' AND EC.CODE = PD.SKN_CD_SKIN_CODE) AS PERSON_SKIN_DESC, PD.BLD_CD_BUILD_CODE AS PERSON_BUILD_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'BUILD_CODES' AND EC.CODE = PD.BLD_CD_BUILD_CODE) AS PERSON_BUILD_DESC, CIT.PERCEIVED_RACE_CODE AS PERCEIVED_RACE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'RACE_CODES' AND EC.CODE = CIT.PERCEIVED_RACE_CODE) AS PERCEIVED_RACE_DESC, CIT.RACE_KNOWN AS RACE_KNOWN, CIT.DURATION_CODE AS DURATION_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'STOP_DURATION_CODES' AND EC.CODE = CIT.DURATION_CODE) AS DURATION_DESC, CRI.SEARCH_TYPE_CODE AS SEARCH_TYPE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'SEARCH_TYPE_CODES' AND EC.CODE = CRI.SEARCH_TYPE_CODE) AS SEARCH_TYPE_DESC, CRI.ARREST_TYPE_CODE AS ARREST_TYPE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'RP_ARREST_TYPE_CODES' AND EC.CODE = CRI.ARREST_TYPE_CODE) AS ARREST_TYPE_DESC, CRI.CONTRABAND_TYPE_CODE AS CONTRABAND_TYPE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'CONTRABAND_FOUND_CODES' AND EC.CODE = CRI.CONTRABAND_TYPE_CODE) AS CONTRABAND_TYPE_DESC, CIT.RESULT_OF_STOP_CODE AS RESULT_OF_STOP_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = 'CITATION_RESULT_OF_STOP_CODES' AND EC.CODE = CIT.RESULT_OF_STOP_CODE) AS RESULT_OF_STOP_DESC, CIT.PHYSICAL_FORCE_FLAG AS PHYSICAL_FORCE_FLAG FROM CITATIONS CIT LEFT OUTER JOIN CITATION_TYPE_CODES CTC ON CTC.CITATION_TYPE_CODE = CIT.CITATION_TYPE_CODE LEFT OUTER JOIN AGENCY_CODES AC ON AC.AGENCY_CODE = CIT.AGENCY_CODE LEFT OUTER JOIN STATE_DISTRICT_CODES SDC ON SDC.STATE_DIST_CODE = CIT.STATE_DIST_CODE LEFT OUTER JOIN CITATION_VEHICLES CV ON CV.CITATION_ID = CIT.CITATION_ID LEFT OUTER JOIN VEHICLES VE ON VE.VEHICLE_ID = CV.VEHICLE_ID LEFT OUTER JOIN VEHICLE_MAKES VMA ON VMA.VEHICLE_MAKE_CODE = VE.VMA_VEHICLE_MAKE_CODE LEFT OUTER JOIN VEHICLE_MODELS VMO ON VMO.VEHICLE_MODEL_CODE = VE.VMO_VEHICLE_MODEL_CODE AND VMO.VMA_VEHICLE_MAKE_CODE = VE.VMA_VEHICLE_MAKE_CODE LEFT OUTER JOIN VEHICLE_STYLES VST ON VST.VEHICLE_STYLE_CODE = VE.VST_VEHICLE_STYLE_CODE AND VST.VTC_TYPE_CODE = VE.VTC_TYPE_CODE LEFT OUTER JOIN VEHICLE_TYPE_CODES VTC ON VTC.TYPE_CODE = VE.VTC_TYPE_CODE LEFT OUTER JOIN (SELECT VD1.*, ROW_NUMBER() OVER (PARTITION BY VD1.VEHICLE_ID ORDER BY VD1.DATE_OF_INFO DESC) AS VD_ROW_NUM FROM VEHICLE_DESCRIPTIONS VD1) VD ON VD.VEHICLE_ID = CV.VEHICLE_ID AND VD_ROW_NUM = 1 LEFT OUTER JOIN CITATION_PEOPLE CP ON CP.CITATION_ID = CIT.CITATION_ID LEFT OUTER JOIN MASTER_PEOPLE MP ON MP.MASTER_PERSON_ID = CP.PERSON_ID LEFT OUTER JOIN (SELECT PD1.*, ROW_NUMBER() OVER (PARTITION BY PD1.PER_PERSON_ID ORDER BY PD1.DATE_OF_INFO DESC) AS PD_ROW_NUM FROM PHYSICAL_DESCRIPTIONS PD1) PD ON PD.PER_PERSON_ID = CP.PERSON_ID AND PD_ROW_NUM = 1 LEFT OUTER JOIN CITATION_RACIAL_INFO CRI ON CRI.CITATION_ID = CIT.CITATION_ID GROUP BY CIT.CITATION_ID, CIT.TICKET_NUMBER, CIT.CITATION_TYPE_CODE, CTC.DESCRIPTION, CIT.AGENCY_CODE, AC.AGENCY_DESC, CIT.PLAINTIFF, CIT.STATE_DIST_CODE, SDC.DESCRIPTION, CIT.CITY_CODE, CIT.COUNTY_CODE, CIT.CITATION_DATE, CIT.FILED_DATE, CIT.TWP_RD, CV.VEHICLE_ID, VE.YEAR, VE.VMA_VEHICLE_MAKE_CODE, VMA.VEHICLE_MAKE_DESC, VE.VMO_VEHICLE_MODEL_CODE, VMO.VEHICLE_MODEL_DESC, VE.VST_VEHICLE_STYLE_CODE, VST.VEHICLE_STYLE_DESC, VE.VTC_TYPE_CODE, VTC.TYPE_DESC, VD.LIC_TYPE_CODE, VD.LIC_STATE_CODE, VD.LIC_MONTH, VD.LIC_YEAR, CP.PERSON_ID, MP.SEX_CODE, MP.RACE_CODE, MP.ETHNICITY_CODE, PD.AGE, PD.SKN_CD_SKIN_CODE, PD.BLD_CD_BUILD_CODE, CIT.PERCEIVED_RACE_CODE, CIT.RACE_KNOWN, CIT.DURATION_CODE, CRI.SEARCH_TYPE_CODE, CRI.ARREST_TYPE_CODE, CRI.CONTRABAND_TYPE_CODE, CIT.RESULT_OF_STOP_CODE, CIT.PHYSICAL_FORCE_FLAG