/* **Scrubbed queries meant as an example of SQL code I have written. In no way does this code represents actual data or data tables** */
/* crimes_historical */
SELECT RPT_NO, DATE_OCCURRED, DATE_APPROVED, STNUM || ' ' || STDIR || ' '|| STREET || ' ' || CITY || ' ' || STATE AS ADDRESS, UCR, FBI, "PRIMARY",
DESCRIPTION, STATUS,
INDX, LOCATION, LOCATION_DESCR, DOMESTIC, SHOOTINGS, VEHICLE_THEFT, METHOD, METHOD_DESCR, BEATASGN, CAU, MOTIVE_DESCR,
BEAT, DISTRICT, WARPT_NO
FROM DB.CRIMES
--LEFT JOIN PRS_USER.ADDR_ADDRESS_MASTER c
--ON a.STNUM = c.STREET_NO AND a.STDIR = c.STREET_DIRECTION AND a.STREET = c.STREET_NME
WHERE DATE_APPROVED >= '11-FEB-2019'
AND DATE_APPROVED <= '10-MAR-2019'
AND STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL')
--AND "CLASS" IN ('BURGLARY', 'ROBBERY', 'HOMICIDE', 'MOTOR VEHICLE THEFT', 'CRIM SEXUAL ASSAULT', 'THEFT')
AND UCR IN ('0110','0261','0262','0263','0264','0265','0266','0271','0272','0274','0275','0281','0291','031A','031B','0312','0313','0320','0325',
'0326','033A','033B','0330','0331','0334','0337','0340','041A','041B','0420','0430','0440','0450','0451','0452','0453','0454','0461','0462','0475','0479',
'0480','0481','0482','0483','0484','0487','0488','0489','1753','1754','0610','0620','0630','0650','0710','0720','0810','0910','0915','0917','0918','0920',
'0925','0927','0928','0930','0935','0937','0938')
AND FBI IN ('00','01A','01B','02','03','04A','04B','05','06','07','09','01B','08A','08B','10','11','12','13','14','15','16','17','18','19','20','22',
'24','26','MCC','TRF','WRT','21')
AND DISTRICT IN ('04', '05', '06', '07', '22');
/* Index crimes */
SELECT *
FROM DB.CRIMES
WHERE (DATE_OCCURRED >= '28-JAN-2018' AND DATE_OCCURRED <= '24-FEB-2018'
OR DATE_OCCURRED >= '28-JAN-2019' AND DATE_OCCURRED <= '24-FEB-2019')
AND INDX IN ('I')
AND STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL')
AND AREA IN ('3');
/*Shootings*/
/* shootings without the lat/long data */
SELECT a.RPT_NO, a.BEAT, b.TABLE_SOURCE
FROM DB.CRIMES a
RIGHT JOIN RPT.COMPSTAT_SHOOTING b
ON a.RPT_NO = b.RPT_NO
WHERE a.DATE_OCCURRED >= '01-JAN-2018'
AND a.DATE_OCCURRED <= '31-DEC-2018'
--AND A.DISTRICT IN ('04','05','06','07','22');
/* The crimes table with shootings attached */
/* crimes_historical */
SELECT *
FROM DB.CRIMES a
--LEFT JOIN RPT.COMPSTAT_SHOOTING b
--ON a.RPT_NO = b.RPT_NO
--LEFT JOIN PRS_USER.ADDR_ADDRESS_MASTER c
--ON a.STNUM = c.STREET_NO AND a.STDIR = c.STREET_DIRECTION AND a.STREET = c.STREET_NME
WHERE DATE_OCCURRED BETWEEN TO_DATE('01-MAY-2013') AND TO_DATE('30-SEP-2018')
AND (rtrim((to_char(DATE_OCCURRED,'MON'))) in ('MAY','JUN','JUL','AUG','SEP'))
--AND a.FBI IN ('04B','04','01A','01B','02','03','04A','05','07')
AND UCR in ('031A','031B','0326','033A','033B')
AND (FBI IN ('00','01A','01B','02','03','04A','04B','05','06','07','09','01B','08A','08B','10',
'11','12','13','14','15','16','17','18','19','20','22','24','26','MCC','TRF','WRT','21'))
AND a.STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL')
AND a.DISTRICT IN ('02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '15', '25');
/*Shooting Victims*/
SELECT RPT_NO, COUNT(RPT_NO) AS VICTIMS
FROM RPT.COMPSTAT_SHOOTING_VICTIM
WHERE DATE_OCCURRED >= '04-FEB-2019'
AND DATE_OCCURRED <= '03-MAR-2019'
AND DISTRICT IN ('03', '08', '09', '10')
GROUP BY RPT_NO;
/*Inventories*/
SELECT DISTINCT INCIDENT_NO, RECOVERED_DATE, STREET_NO || ' ' || STREET_DIRECTION || ' ' || STREET_NME || ' ' || a.CITY || ' ' || STATE AS REC_ADDRESS, a.DISTRICT,
INVENTORY_TYPE, UCR, RPT_NO, DATE_OCCURRED
FROM DB.EVIDENCENVENTORIES a
INNER JOIN DB.CRIMES b
ON a.INCIDENT_NO = b.RPT_NO
WHERE b.DATE_OCCURRED >= '04-FEB-2019'
AND b.DATE_OCCURRED <= '03-MAR-2019'
AND a.DISTRICT IN ('03', '08')
AND a.INVENTORY_TYPE IN ('EXPENDED SHELL', 'FIRED BULLET');
/*Inventories v2*/
SELECT DISTINCT a.INCIDENT_NO, a.CREATED_DATE, a.STREET_NO || ' ' || a.STREET_DIRECTION || ' ' || a.STREET_NME || ' ' || a.CITY || ' ' || a.STATE AS REC_ADDRESS, a.DISTRICT,
a.INVENTORY_TYPE, a.INCIDENT_TYPE, b.TABLE_SOURCE
FROM DB.EVIDENCENVENTORIES a
LEFT JOIN RPT.COMPSTAT_SHOOTING b
ON a.INCIDENT_NO = b.RPT_NO
WHERE RECOVERED_DATE >= '11-FEB-2019'
AND RECOVERED_DATE <= '10-MAR-2019'
AND a.DISTRICT IN ('10')
AND INVENTORY_TYPE IN ('EXPENDED SHELL', 'FIRED BULLET');
/* STREET_STOP Data */
SELECT CONTACT_NO, CONTACT_SOURCE, CONTACT_TYPE, CONTACT_DATE, DISTRICT, BEAT, PD_UNIT_NO
FROM DB.STREET_STOP_DETAILS
WHERE CONTACT_DATE >= '11-FEB-2019'
AND CONTACT_DATE < '10-MAR-2019'
AND DISTRICT IN ('07', '04');
/*ORDINANCE VIOLATIONS*/
SELECT *
FROM DB.PD_ORDINANCE_VIOLATION
WHERE OVISSUED_DATE BETWEEN TO_DATE('01-JAN-2019') AND TO_DATE('28-FEB-2019')
AND (rtrim((to_char(OVISSUED_DATE,'MON'))) in ('JAN', 'FEB', 'MAR', 'OCT', 'NOV', 'DEC'))
AND UNIT_NUMBER IN ('701', '702', '704');
/* crimes historical */
SELECT RPT_NO, DATE_OCCURRED, DATE_APPROVED, STNUM || ' ' || STDIR || ' '|| STREET || ' ' || CITY || ' ' || STATE AS ADDRESS, UCR, FBI, "PRIMARY",
DESCRIPTION, STATUS,
INDX, LOCATION, LOCATION_DESCR, DOMESTIC, SHOOTINGS, VEHICLE_THEFT, METHOD, METHOD_DESCR, BEATASGN, CAU, MOTIVE_DESCR,
BEAT, DISTRICT, WARPT_NO
FROM DB.CRIMES
--LEFT JOIN PRS_USER.ADDR_ADDRESS_MASTER c
--ON a.STNUM = c.STREET_NO AND a.STDIR = c.STREET_DIRECTION AND a.STREET = c.STREET_NME
WHERE DATE_APPROVED >= '11-FEB-2019'
AND DATE_APPROVED <= '10-MAR-2019'
AND STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL')
--AND "PRIMARY" IN ('BURGLARY', 'ROBBERY', 'HOMICIDE', 'MOTOR VEHICLE THEFT', 'CRIM SEXUAL ASSAULT', 'THEFT')
AND UCR IN ('0110','0261','0262','0263','0264','0265','0266','0271','0272','0274','0275','0281','0291','031A','031B','0312','0313','0320','0325',
'0326','033A','033B','0330','0331','0334','0337','0340','041A','041B','0420','0430','0440','0450','0451','0452','0453','0454','0461','0462','0475','0479',
'0480','0481','0482','0483','0484','0487','0488','0489','1753','1754','0610','0620','0630','0650','0710','0720','0810','0910','0915','0917','0918','0920',
'0925','0927','0928','0930','0935','0937','0938')
AND FBI IN ('00','01A','01B','02','03','04A','04B','05','06','07','09','01B','08A','08B','10','11','12','13','14','15','16','17','18','19','20','22',
'24','26','MCC','TRF','WRT','21')
AND DISTRICT IN ('04', '05', '06', '07', '22');
/* Index crimes */
SELECT *
FROM DB.CRIMES
WHERE (DATE_OCCURRED >= '28-JAN-2018' AND DATE_OCCURRED <= '24-FEB-2018'
OR DATE_OCCURRED >= '28-JAN-2019' AND DATE_OCCURRED <= '24-FEB-2019')
AND INDX IN ('I')
AND STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL')
AND AREA IN ('3');
/* SELECT an RPT_NO # to investigate */
SELECT *
FROM DB.CRIMES a
LEFT JOIN PRS_USER.ADDR_ADDRESS_MASTER c
ON a.STNUM = c.STREET_NO AND a.STDIR = c.STREET_DIRECTION AND a.STREET = c.STREET_NME
WHERE RPT_NO IN ('AA279023');
/*Shootings*/
/* shootings without the lat/long data */
SELECT a.RPT_NO, a.BEAT, b.TABLE_SOURCE
FROM DB.CRIMES a
RIGHT JOIN RPT.COMPSTAT_SHOOTING b
ON a.RPT_NO = b.RPT_NO
WHERE a.DATE_OCCURRED >= '01-JAN-2018'
AND a.DATE_OCCURRED <= '31-DEC-2018'
--AND A.DISTRICT IN ('04','05','06','07','22');
/* Min date of any table */
SELECT MIN(DATE_OCCURRED)
FROM RPT.COMPSTAT_SHOOTING;
/* The crimes table with shootings attached */
/* crimes_historical */
SELECT *
FROM DB.CRIMES a
--LEFT JOIN RPT.COMPSTAT_SHOOTING b
--ON a.RPT_NO = b.RPT_NO
--LEFT JOIN PRS_USER.ADDR_ADDRESS_MASTER c
--ON a.STNUM = c.STREET_NO AND a.STDIR = c.STREET_DIRECTION AND a.STREET = c.STREET_NME
WHERE DATE_OCCURRED BETWEEN TO_DATE('01-MAY-2013') AND TO_DATE('30-SEP-2018')
AND (rtrim((to_char(DATE_OCCURRED,'MON'))) in ('MAY','JUN','JUL','AUG','SEP'))
--AND a.FBI IN ('04B','04','01A','01B','02','03','04A','05','07')
AND UCR in ('031A','031B','0326','033A','033B')
AND (FBI IN ('00','01A','01B','02','03','04A','04B','05','06','07','09','01B','08A','08B','10',
'11','12','13','14','15','16','17','18','19','20','22','24','26','MCC','TRF','WRT','21'))
AND a.STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL')
AND a.DISTRICT IN ('02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '15', '25');
/*Shooting Victims*/
SELECT RPT_NO, COUNT(RPT_NO) AS VICTIMS
FROM RPT.COMPSTAT_SHOOTING_VICTIM
WHERE DATE_OCCURRED >= '04-FEB-2019'
AND DATE_OCCURRED <= '03-MAR-2019'
AND DISTRICT IN ('03', '08', '09', '10')
GROUP BY RPT_NO;
/*Homicide Counts*/
SELECT RPT_NO_NO, COUNT(RPT_NO_NO) AS HOMICIDES
FROM DB.HOMICIDE
WHERE COMPSTAT_DATE >= '01-JAN-2019'
AND COMPSTAT_DATE <= '27-APR-2019'
GROUP BY RPT_NO_NO;
/*Inventories*/
SELECT DISTINCT INCIDENT_NO, RECOVERED_DATE, STREET_NO || ' ' || STREET_DIRECTION || ' ' || STREET_NME || ' ' || a.CITY || ' ' || STATE AS REC_ADDRESS, a.DISTRICT,
INVENTORY_TYPE, UCR, RPT_NO, DATE_OCCURRED
FROM DB.EVIDENCENVENTORIES a
INNER JOIN DB.CRIMES b
ON a.INCIDENT_NO = b.RPT_NO
WHERE b.DATE_OCCURRED >= '04-FEB-2019'
AND b.DATE_OCCURRED <= '03-MAR-2019'
AND a.DISTRICT IN ('03', '08')
AND a.INVENTORY_TYPE IN ('EXPENDED SHELL', 'FIRED BULLET');
/*Inventories v2*/
SELECT DISTINCT a.INCIDENT_NO, a.CREATED_DATE, a.STREET_NO || ' ' || a.STREET_DIRECTION || ' ' || a.STREET_NME || ' ' || a.CITY || ' ' || a.STATE AS REC_ADDRESS, a.DISTRICT,
a.INVENTORY_TYPE, a.INCIDENT_TYPE, b.TABLE_SOURCE
FROM DB.EVIDENCENVENTORIES a
LEFT JOIN RPT.COMPSTAT_SHOOTING b
ON a.INCIDENT_NO = b.RPT_NO
WHERE RECOVERED_DATE >= '11-FEB-2019'
AND RECOVERED_DATE <= '10-MAR-2019'
AND a.DISTRICT IN ('10')
AND INVENTORY_TYPE IN ('EXPENDED SHELL', 'FIRED BULLET');
/* STREET_STOP Data */
SELECT CONTACT_NO, CONTACT_SOURCE, CONTACT_TYPE, CONTACT_DATE, DISTRICT, BEAT, PD_UNIT_NO
FROM DB.STREET_STOP_DETAILS
WHERE CONTACT_DATE >= '11-FEB-2019'
AND CONTACT_DATE < '10-MAR-2019'
AND DISTRICT IN ('07', '04');
/*ORDINANCE_VIOLATION*/
SELECT *
FROM DB.PD_ORDINANCE_VIOLATION2
WHERE NOVSSUED_DATE BETWEEN TO_DATE('01-JAN-2019') AND TO_DATE('28-FEB-2019')
AND (rtrim((to_char(NOVSSUED_DATE,'MON'))) in ('JAN', 'FEB', 'MAR', 'OCT', 'NOV', 'DEC'))
AND UNIT_NUMBER IN ('701', '702', '704');
/*CFS*/
SELECT EVENTNUMBER, ENTRY_RECEIVED_DATE, CLOSE_COMPLETED_DATE, OCCSTREETNUMBER || ' ' || OCCSTREETDIR || ' '|| OCCSTREETNAME || ' ' || 'Chicago IL' AS OCC_ADDRESS,
OCCLOCATION, "ZONE", UNIT, BEAT_OF_OCCURRENCE, BEAT_OF_SERVICE, DISTRICT_OF_OCCURRENCE, DISTRICT_OF_SERVICE, SECTOR_OF_OCCURRENCE, SECTOR_OF_SERVICE,
SERVICESTREETNUMBER || ' ' || SERVICESTREETDIR || ' ' || SERVICESTREETNAME AS SERVICE_ADDRESS, SERVICELOCATION, INIT_911_DISPATCH_DESCR, FIN_911_DISPATCH_DESCR
FROM DB.SERVICE_CALLS
WHERE ENTRY_RECEIVED_DATE >= '11-MAR-2019'
AND ENTRY_RECEIVED_DATE <= '07-APR-2019'
AND FIN_911_DISPATCH IN ('COMINT')
AND UNIT LIKE ('12%_%_%')
;
/*CFS RAPS*/
SELECT EVENTNUMBER, ENTRY_RECEIVED_DATE, CLOSE_COMPLETED_DATE, OCCSTREETNUMBER || ' ' || OCCSTREETDIR || ' '|| OCCSTREETNAME || ' ' || 'Chicago IL' AS OCC_ADDRESS,
OCCLOCATION, "ZONE", UNIT, BEAT_OF_OCCURRENCE, BEAT_OF_SERVICE, DISTRICT_OF_OCCURRENCE, DISTRICT_OF_SERVICE, SECTOR_OF_OCCURRENCE, SECTOR_OF_SERVICE,
SERVICESTREETNUMBER || ' ' || SERVICESTREETDIR || ' ' || SERVICESTREETNAME AS SERVICE_ADDRESS, SERVICELOCATION, INIT_911_DISPATCH_DESCR, FIN_911_DISPATCH_DESCR,
FIN_DISPOSITION_DESCR
FROM DB.SERVICE_CALLS
WHERE ENTRY_RECEIVED_DATE >= '28-MAY-2018'
AND ENTRY_RECEIVED_DATE <= '03-SEP-2018'
AND FIN_911_DISPATCH_DESCR IN ('RADIO ASSIGNMENTS PENDING')
;
/* Arrest table without the long narrative field WARNING lots of text fields/data */
SELECT ARREST, OFFENDER, ARREST_DATE, ARR_YEAR, ARR_MONTH, arr_day_of_month, arr_day_of_year, arr_hour,
arr_beat, arr_district, PD_district, arresting_unit, arresting_beat, street_no, street_direction, street_nme,
city, state, zip, county, location_code, location_descr, statute, stat_descr, UCR_code, class,
secondary_class, o_district, o_beat, birth_date, age, birth_place, sex_code, race_code, unique_identifier_no, PD_arrest,
arrest_unit_descr
FROM DB.ARREST
WHERE ARREST_DATE >= '18-MAR-2019'
AND ARREST_DATE <= '14-APR-2019'
AND PD_ARREST = 'Y'
AND ARR_DISTRICT = '11';
SELECT *
FROM DB.HOMICIDE
WHERE INJURY_DATE >= '01-JAN-2015'
AND INVOLUNTARY = 'N'
AND JUSTIFIABLE = 'N'
AND COMPSTAT = 'Y';
/*Homicide Counts*/
SELECT INJURY_DESCRIPTION, COUNT(INJURY_DESCRIPTION)
FROM DB.HOMICIDE
GROUP BY INJURY_DESCRIPTION;
/* shootings without the lat/long data */
SELECT a.RPT_NO, a.DATE_OCCURRED, a.DATE_APPROVED, a.STNUM || ' ' || a.STDIR || ' '|| a.STREET || ' ' || a.CITY || ' ' || a.STATE AS ADDRESS, a.UCR, a.FBI, "PRIMARY",
a.DESCRIPTION, a.STATUS,
a.INDX, a.LOCATION, a.LOCATION_DESCR, a.DOMESTIC, a. a.SHOOTINGS, a.VEHICLE_THEFT, a.METHOD, a.METHOD_DESCR, a.BEATASGN, a.CAU, a.MOTIVE_DESCR,
a.BEAT, a.DISTRICT
FROM DB.CRIMES a
RIGHT JOIN RPT.COMPSTAT_SHOOTING b
ON a.RPT_NO = b.RPT_NO
WHERE a.DATE_OCCURRED >= '01-JAN-2011'
AND a.DATE_OCCURRED <= '07-JUN-2019'
AND a.STREET LIKE '%LAKE SHORE%'
OR a.STREET LIKE'%SKYWAY%'
AND STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL');
/* Query for all crimes */
SELECT RPT_NO, DATE_OCCURRED, INV_ADDRESSES, PRIMARY, DESCRIPTION, LOCATION_DESCR, BEAT, DISTRICT, FBI, FBI_DESCR, FBINDX, UCR
FROM DB.CRIMES
WHERE TO_CHAR(DATE_OCCURRED, 'YYYY') = 2018 AND PRIMARY NOT IN ('NON-CRIMINAL', 'OTHER OFFENSE');
/* Shootings (with lat/long data */
/* Shootings */
SELECT *
FROM DB.CRIMES a
RIGHT JOIN RPT.COMPSTAT_SHOOTING b
ON a.RPT_NO = b.RPT_NO
LEFT JOIN PRS_USER.ADDR_ADDRESS_MASTER c
ON a.STNUM = c.STREET_NO AND a.STDIR = c.STREET_DIRECTION AND a.STREET = c.STREET_NME
WHERE a.DATE_OCCURRED >= '01-JAN-2019'
AND a.DATE_OCCURRED < '09-APRIL-2019'
AND STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL');
/* shootings without the lat/long data */
SELECT *
FROM DB.CRIMES a
RIGHT JOIN RPT.COMPSTAT_SHOOTING b
ON a.RPT_NO = b.RPT_NO
WHERE a.DATE_OCCURRED >= '01-JAN-2011'
AND a.DATE_OCCURRED <= '31-JAN-2019'
AND a.STREET LIKE '%LAKE SHORE%'
OR a.STREET LIKE'%SKYWAY%'
AND STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL');
/* The crimes table with shootings attached */
/* crimes_historical */
SELECT *
FROM DB.CRIMES a
--LEFT JOIN RPT.COMPSTAT_SHOOTING b
--ON a.RPT_NO = b.RPT_NO
--LEFT JOIN PRS_USER.ADDR_ADDRESS_MASTER c
--ON a.STNUM = c.STREET_NO AND a.STDIR = c.STREET_DIRECTION AND a.STREET = c.STREET_NME
WHERE DATE_OCCURRED BETWEEN TO_DATE('01-MAY-2013') AND TO_DATE('30-SEP-2018')
AND (rtrim((to_char(DATE_OCCURRED,'MON'))) in ('MAY','JUN','JUL','AUG','SEP'))
--AND a.FBI IN ('04B','04','01A','01B','02','03','04A','05','07')
AND UCR in ('031A','031B','0326','033A','033B')
AND (FBI IN ('00','01A','01B','02','03','04A','04B','05','06','07','09','01B','08A','08B','10',
'11','12','13','14','15','16','17','18','19','20','22','24','26','MCC','TRF','WRT','21'))
AND a.STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL')
AND a.DISTRICT IN ('02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '15', '25');
/*Motor Vehicle Theft*/
SELECT *
FROM DB.CRIMES
WHERE DISTRICT IN ('10')
AND UCR IN ('0910','0920','0915','0917','0930')
AND DATE_OCCURRED >= '03-JUN-2018'
AND STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL');
/* Calls for service shots fired- for all cfs comment out 'init_911_dispatch' line */
SELECT *
FROM DB.SERVICE_CALLS
WHERE ENTRY_RECEIVED_DATE >= '16-NOV-2018'
AND ENTRY_RECEIVED_DATE < '19-NOV-2018'
AND INIT_911_DISPATCH IN ('SHOTS','SHOTSF');
/* An example of pulling a narrative for a crime
DESCRIPTION from the NARRATIVE table*/
SELECT a.*, b.DESCRIPTION
FROM DB.CRIMES a
LEFT JOIN DB.NARRATIVE b
ON a.RPT_NO = b.RPT_NO_NO
WHERE a.UCR IN ('0910','0915','0930','0935')
AND a.DISTRICT = '04'
AND a.DATE_OCCURRED >= '01-OCT-18'
AND a.DATE_OCCURRED < '01-DEC-18'
AND a.STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL');
/* Homicides table, 2014 to 2018 */
SELECT *
FROM DB.HOMICIDE
WHERE JUSTIFIABLE = 'N'
AND COMPSTAT = 'Y'
AND INVOLUNTARY = 'N'
AND INJURY_DATE >= '01-JAN-2014'
AND INJURY_DATE < '01-JAN-2019';
/* Arrest table without the long narrative field- feel free to include it all just know it becomes a pain with large amounts of data */
SELECT ARREST, OFFENDER, ARREST_DATE, ARR_YEAR, ARR_MONTH, arr_day_of_month, arr_day_of_year, arr_hour,
arr_beat, arr_district, PD_district, arresting_unit, arresting_beat, street_no, street_direction, street_nme,
city, state, zip, county, location_code, location_descr, statute, stat_descr, UCR_code, class,
secondary_class, o_district, o_beat, birth_date, age, birth_place, sex_code, race_code, unique_identifier_no, PD_arrest,
arrest_unit_descr
FROM DB.ARREST
WHERE ARREST_DATE >= '01-JAN-2018'
AND ARREST_DATE <= '31-DEC-2018'
--AND ARR_DISTRICT = '02';
/* WHERE ARR_YEAR >= '2018'
AND ARR_YEAR < '2018'; */
SELECT *
FROM DB.ARREST
WHERE ARR_YEAR = '2018';
/* Traffic stops */
SELECT *
FROM DB.TRAFFIC_CC
WHERE CONTACT_DATE >= '01-JAN-2016 00:00:00'
AND CONTACT_DATE < '01-JAN-2019 00:00:00'
AND CONTACT_TYPE = 'TRAFFIC';
/* STREET_STOP Data */
SELECT CONTACT_NO, CONTACT_SOURCE, CONTACT_TYPE, CONTACT_DATE, DISTRICT, BEAT, PD_UNIT_NO
FROM DB.STREET_STOP_DETAILS
WHERE CONTACT_DATE >= '11-FEB-2019'
AND CONTACT_DATE < '10-MAR-2019'
AND DISTRICT IN ('07', '04');
SELECT CONTACT_NO, CONTACT_SOURCE, CONTACT_TYPE, CONTACT_DATE, DISTRICT, BEAT
FROM DB.STREET_STOP_DETAILS
WHERE TO_CHAR(CONTACT_DATE, 'YYYY') = 2018;
SELECT *
FROM DB.SPOTTER_LIVE_MV
WHERE "TIME" >= '01-APR-2019'
AND "TIME" < '01-May-2019'
AND ;
/* community area victim pull */
SELECT a.*, b.*, c.COMMUNITY_AREA
FROM DB.CRIMES a
LEFT JOIN DB.VICTIMS b
ON a.RPT_NO = b.RPT_NO
LEFT JOIN DB.ADDRESSES c
ON a.GEO_CODE = c.GEO_CODE
WHERE a.STATUS NOT IN ('2-UNFOUNDED','7-CLOSED NON-CRIMINAL')
AND a.DATE_OCCURRED >= '28-MAY-2018' AND a.DATE_OCCURRED <= '03-SEP-2018'
AND a.FBI IN ('03', '04A', '04B', '08A', '08B') AND a.SHOOTINGS = 'N';
/* specific query for streets of homicides */
SELECT a.STREET_NO || ' ' || a.STREET_DIRECTION || ' '|| a.STREET_NME || ' ' || a.CITY || ' ' || a.STATE AS ADDRESS, a.*
FROM DB.HOMICIDE a
WHERE INJURY_DATE >= '28-MAY-2018' AND INJURY_DATE <= '03-SEP-2018'
AND JUSTIFIABLE = 'N' AND INVOLUNTARY = 'N' AND COMPSTAT = 'Y';
/* another project specific query */
SELECT a.*, COALESCE(b.FIRST_NAME, c.VFIRST) as first_name, COALESCE(b.LAST_NAME, c.VLAST) as last_name, c.*, e.COMMUNITY_AREA
FROM RPT.COMPSTAT_SHOOTING_VICTIM a
LEFT JOIN DB.INVOLVED_PARTIES b on a.ID=b.ID AND a.TABLE_SOURCE = 'DOC PARTIES'
LEFT JOIN DB.VICTIMS c on a.ID=c.NO AND a.TABLE_SOURCE = 'VICTIMS'
LEFT JOIN DB.CRIMES d on a.RPT_NO=d.RPT_NO
LEFT JOIN DB.ADDRESSES e ON d.GEO_CODE = e.GEO_CODE
WHERE d.DATE_OCCURRED >= '28-MAY-2018' AND d.DATE_OCCURRED <= '03-SEP-2018';