use ottawa_parking;


CREATE TABLE tickets_2010 (
    id_Column MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Issue_No VARCHAR(8),
    Issue_Date DATE,
    Issue_Time TIME,
    Agency VARCHAR(29),
    Officer_Badge VARCHAR(5),
    Beat VARCHAR(4),
    VIOCode VARCHAR(20),
    VIODescription VARCHAR(70),
    VIOType VARCHAR(40),
    VIOFine VARCHAR(5),
    VIOlateFee VARCHAR(3),
    Street VARCHAR(45),
    a_between VARCHAR(25),
    a_and VARCHAR(25),
    side_of_street VARCHAR(10),
    meter_number VARCHAR(10),
    total_voided VARCHAR(10),
    total_non_cash_credit VARCHAR(10),
    total_fines_and_fees VARCHAR(10),
    total_credits VARCHAR(10),
    total_fines VARCHAR(10),
    amount_due VARCHAR(10),
    due_date DATE,
    REC_Status VARCHAR(2),
    REC_Status_Date DATE,
    REC_Cleared_Reason VARCHAR(2),
    Void_Status VARCHAR(2),
    Review_Code VARCHAR(3),
    Trial_Code VARCHAR(5)
);

CREATE TABLE tickets_2011 (
    id_Column MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Issue_No VARCHAR(8),
    Issue_Date DATE,
    Issue_Time TIME,
    Agency VARCHAR(29),
    Officer_Badge VARCHAR(5),
    Beat VARCHAR(4),
    VIOCode VARCHAR(20),
    VIODescription VARCHAR(70),
    VIOType VARCHAR(40),
    VIOFine VARCHAR(5),
    VIOlateFee VARCHAR(3),
    Street VARCHAR(45),
    a_between VARCHAR(25),
    a_and VARCHAR(25),
    side_of_street VARCHAR(10),
    meter_number VARCHAR(10),
    total_voided VARCHAR(10),
    total_non_cash_credit VARCHAR(10),
    total_fines_and_fees VARCHAR(10),
    total_credits VARCHAR(10),
    total_fines VARCHAR(10),
    amount_due VARCHAR(10),
    due_date DATE,
    REC_Status VARCHAR(2),
    REC_Status_Date DATE,
    REC_Cleared_Reason VARCHAR(2),
    Void_Status VARCHAR(2),
    Review_Code VARCHAR(3),
    Trial_Code VARCHAR(5)
);

CREATE TABLE tickets_2012 (
    id_Column MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Issue_No VARCHAR(8),
    Issue_Date DATE,
    Issue_Time TIME,
    Agency VARCHAR(29),
    Officer_Badge VARCHAR(5),
    Beat VARCHAR(4),
    VIOCode VARCHAR(17),
    VIODescription VARCHAR(70),
    VIOType VARCHAR(40),
    VIOFine VARCHAR(5),
    VIOlateFee VARCHAR(3),
    Street VARCHAR(45),
    a_between VARCHAR(20),
    a_and VARCHAR(20),
    side_of_street VARCHAR(5),
    meter_number VARCHAR(5),
    total_voided VARCHAR(4),
    total_non_cash_credit VARCHAR(4),
    total_fines_and_fees VARCHAR(6),
    total_credits VARCHAR(6),
    total_fines VARCHAR(5),
    amount_due VARCHAR(6),
    due_date DATE,
    REC_Status VARCHAR(2),
    REC_Status_Date DATE,
    REC_Cleared_Reason VARCHAR(2),
    Void_Status VARCHAR(2),
    Review_Code VARCHAR(3),
    Trial_Code VARCHAR(5)
);

CREATE TABLE tickets_2013 (
    id_Column MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Issue_No VARCHAR(8),
    Issue_Date DATE,
    Issue_Time TIME,
    Agency VARCHAR(29),
    Officer_Badge VARCHAR(5),
    Beat VARCHAR(4),
    VIOCode VARCHAR(20),
    VIODescription VARCHAR(70),
    VIOType VARCHAR(40),
    VIOFine VARCHAR(5),
    VIOlateFee VARCHAR(3),
    Street VARCHAR(45),
    a_between VARCHAR(25),
    a_and VARCHAR(25),
    side_of_street VARCHAR(10),
    meter_number VARCHAR(10),
    total_voided VARCHAR(10),
    total_non_cash_credit VARCHAR(10),
    total_fines_and_fees VARCHAR(10),
    total_credits VARCHAR(10),
    total_fines VARCHAR(10),
    amount_due VARCHAR(10),
    due_date DATE,
    REC_Status VARCHAR(2),
    REC_Status_Date DATE,
    REC_Cleared_Reason VARCHAR(2),
    Void_Status VARCHAR(2),
    Review_Code VARCHAR(3),
    Trial_Code VARCHAR(5)
);

CREATE TABLE tickets_2014 (
    id_Column MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Issue_No VARCHAR(8),
    Issue_Date DATE,
    Issue_Time TIME,
    Agency VARCHAR(29),
    Officer_Badge VARCHAR(5),
    Beat VARCHAR(4),
    VIOCode VARCHAR(20),
    VIODescription VARCHAR(70),
    VIOType VARCHAR(40),
    VIOFine VARCHAR(5),
    VIOlateFee VARCHAR(3),
    Street VARCHAR(45),
    a_between VARCHAR(25),
    a_and VARCHAR(25),
    side_of_street VARCHAR(10),
    meter_number VARCHAR(10),
    total_voided VARCHAR(10),
    total_non_cash_credit VARCHAR(10),
    total_fines_and_fees VARCHAR(10),
    total_credits VARCHAR(10),
    total_fines VARCHAR(10),
    amount_due VARCHAR(10),
    due_date DATE,
    REC_Status VARCHAR(2),
    REC_Status_Date DATE,
    REC_Cleared_Reason VARCHAR(2),
    Void_Status VARCHAR(2),
    Review_Code VARCHAR(3),
    Trial_Code VARCHAR(5)
);

CREATE TABLE tickets_2015 (
    id_Column MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Issue_No VARCHAR(8),
    Issue_Date DATE,
    Issue_Time TIME,
    Agency VARCHAR(29),
    Officer_Badge VARCHAR(5),
    Beat VARCHAR(4),
    VIOCode VARCHAR(20),
    VIODescription VARCHAR(70),
    VIOType VARCHAR(40),
    VIOFine VARCHAR(5),
    VIOlateFee VARCHAR(3),
    Street VARCHAR(45),
    a_between VARCHAR(25),
    a_and VARCHAR(25),
    side_of_street VARCHAR(10),
    meter_number VARCHAR(10),
    total_voided VARCHAR(10),
    total_non_cash_credit VARCHAR(10),
    total_fines_and_fees VARCHAR(10),
    total_credits VARCHAR(10),
    total_fines VARCHAR(10),
    amount_due VARCHAR(10),
    due_date DATE,
    REC_Status VARCHAR(2),
    REC_Status_Date DATE,
    REC_Cleared_Reason VARCHAR(2),
    Void_Status VARCHAR(2),
    Review_Code VARCHAR(3),
    Trial_Code VARCHAR(5)
);

CREATE TABLE tickets_2015_16 (
    id_Column MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Issue_No VARCHAR(8),
    Issue_Date DATE,
    Issue_Time TIME,
    Agency VARCHAR(29),
    Officer_Badge VARCHAR(5),
    Beat VARCHAR(4),
    VIOCode VARCHAR(20),
    VIODescription VARCHAR(70),
    VIOType VARCHAR(40),
    VIOFine VARCHAR(5),
    VIOlateFee VARCHAR(3),
    Street VARCHAR(45),
    a_between VARCHAR(25),
    a_and VARCHAR(25),
    side_of_street VARCHAR(10),
    meter_number VARCHAR(10),
    total_voided VARCHAR(10),
    total_non_cash_credit VARCHAR(10),
    total_fines_and_fees VARCHAR(10),
    total_credits VARCHAR(10),
    total_fines VARCHAR(10),
    amount_due VARCHAR(10),
    due_date DATE,
    REC_Status VARCHAR(2),
    REC_Status_Date DATE,
    REC_Cleared_Reason VARCHAR(2),
    Void_Status VARCHAR(2),
    Review_Code VARCHAR(3),
    Trial_Code VARCHAR(5)
);

CREATE TABLE tickets_2016_17 (
    id_Column MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Issue_No VARCHAR(8),
    Issue_Date DATE,
    Issue_Time TIME,
    Agency VARCHAR(29),
    Officer_Badge VARCHAR(5),
    Beat VARCHAR(4),
    VIOCode VARCHAR(20),
    VIODescription VARCHAR(70),
    VIOType VARCHAR(40),
    VIOFine VARCHAR(5),
    VIOlateFee VARCHAR(3),
    Street VARCHAR(45),
    a_between VARCHAR(25),
    a_and VARCHAR(25),
    side_of_street VARCHAR(10),
    meter_number VARCHAR(10),
    total_voided VARCHAR(10),
    total_non_cash_credit VARCHAR(10),
    total_fines_and_fees VARCHAR(10),
    total_credits VARCHAR(10),
    total_fines VARCHAR(10),
    amount_due VARCHAR(10),
    due_date DATE,
    REC_Status VARCHAR(2),
    REC_Status_Date DATE,
    REC_Cleared_Reason VARCHAR(2),
    Void_Status VARCHAR(2),
    Review_Code VARCHAR(3),
    Trial_Code VARCHAR(5)
);

drop table tickets_2016_17; 


#2010
load data local infile 
"E:\\Documents and Important Documents\\Journalism and Databases\\Ottawa Parking Tickets\\Data\\O Parking Data 2010.csv"
into Table tickets_2010
character set 'utf8'
	Fields terminated by "," enclosed by """" Lines terminated by "\n"
 Ignore 3 Lines
(Issue_No, @Issue_Date, @Issue_Time, Agency, Officer_Badge, 
Beat, VIOCode, VIODescription, VIOType, VIOFine, 
VIOlateFee, Street, a_between, a_and, side_of_street ,
meter_number, total_voided, total_non_cash_credit , total_fines_and_fees, total_credits, 
total_fines, amount_due, @due_date, REC_Status, @REC_Status_Date, 
REC_Cleared_Reason, Void_Status, Review_Code, Trial_Code)
	set Issue_Date = str_to_date(@Issue_Date, '%d-%b-%y'),
	issue_time = str_to_date(@Issue_Time,'%l:%i%p'),
    due_date = str_to_date(@due_date, '%d-%b-%y'),
    REC_Status_Date = str_to_date(@REC_Status_Date, '%d-%b-%y');
    
#2011
load data local infile 
"E:\\Documents and Important Documents\\Journalism and Databases\\Ottawa Parking Tickets\\Data\\O Parking Data 2011.csv"
into Table tickets_2011
character set 'utf8'
	Fields terminated by "," enclosed by """" Lines terminated by "\n"
 Ignore 3 Lines
(Issue_No, @Issue_Date, @Issue_Time, Agency, Officer_Badge, 
Beat, VIOCode, VIODescription, VIOType, VIOFine, 
VIOlateFee, Street, a_between, a_and, side_of_street ,
meter_number, total_voided, total_non_cash_credit , total_fines_and_fees, total_credits, 
total_fines, amount_due, @due_date, REC_Status, @REC_Status_Date, 
REC_Cleared_Reason, Void_Status, Review_Code, Trial_Code)
	set Issue_Date = str_to_date(@Issue_Date, '%d-%b-%y'),
	issue_time = str_to_date(@Issue_Time,'%l:%i%p'),
    due_date = str_to_date(@due_date, '%d-%b-%y'),
    REC_Status_Date = str_to_date(@REC_Status_Date, '%d-%b-%y');

#2012
load data local infile 
"E:\\Documents and Important Documents\\Journalism and Databases\\Ottawa Parking Tickets\\Data\\O Parking Data 2012.csv"
into Table tickets_2012
character set 'utf8'
	Fields terminated by "," enclosed by """" Lines terminated by "\n"
 Ignore 3 Lines
(Issue_No, @Issue_Date, @Issue_Time, Agency, Officer_Badge, 
Beat, VIOCode, VIODescription, VIOType, VIOFine, 
VIOlateFee, Street, a_between, a_and, side_of_street ,
meter_number, total_voided,total_non_cash_credit , total_fines_and_fees, total_credits, 
total_fines, amount_due, @due_date, REC_Status, @REC_Status_Date, 
REC_Cleared_Reason, Void_Status, Review_Code, Trial_Code)
	set Issue_Date = str_to_date(@Issue_Date, '%d-%b-%y'),
	issue_time = str_to_date(@Issue_Time,'%l:%i%p'),
    due_date = str_to_date(@due_date, '%d-%b-%y'),
    REC_Status_Date = str_to_date(@REC_Status_Date, '%d-%b-%y');
    
#2013
load data local infile
"E:\\Documents and Important Documents\\Journalism and Databases\\Ottawa Parking Tickets\\Data\\O Parking Data 2013.csv"
into Table tickets_2013
character set 'utf8'
	Fields terminated by "," enclosed by """" Lines terminated by "\n"
 Ignore 3 Lines
(Issue_No, @Issue_Date, @Issue_Time, Agency, Officer_Badge, 
Beat, VIOCode, VIODescription, VIOType, VIOFine, 
VIOlateFee, Street, a_between, a_and, side_of_street ,
meter_number, total_voided,total_non_cash_credit , total_fines_and_fees, total_credits, 
total_fines, amount_due, @due_date, REC_Status, @REC_Status_Date, 
REC_Cleared_Reason, Void_Status, Review_Code, Trial_Code)
	set Issue_Date = str_to_date(@Issue_Date, '%d-%b-%y'),
	issue_time = str_to_date(@Issue_Time,'%l:%i%p'),
    due_date = str_to_date(@due_date, '%d-%b-%y'),
    REC_Status_Date = str_to_date(@REC_Status_Date, '%d-%b-%y');
    
#2014
load data local infile 
"E:\\Documents and Important Documents\\Journalism and Databases\\Ottawa Parking Tickets\\Data\\O Parking Data 2014.csv"
into Table tickets_2014
character set 'utf8'
	Fields terminated by "," enclosed by """" Lines terminated by "\n"
 Ignore 3 Lines
(Issue_No, @Issue_Date, @Issue_Time, Agency, Officer_Badge, 
Beat, VIOCode, VIODescription, VIOType, VIOFine, 
VIOlateFee, Street, a_between, a_and, side_of_street ,
meter_number, total_voided,total_non_cash_credit , total_fines_and_fees, total_credits, 
total_fines, amount_due, @due_date, REC_Status, @REC_Status_Date, 
REC_Cleared_Reason, Void_Status, Review_Code, Trial_Code)
	set Issue_Date = str_to_date(@Issue_Date, '%d-%b-%y'),
	issue_time = str_to_date(@Issue_Time,'%l:%i%p'),
    due_date = str_to_date(@due_date, '%d-%b-%y'),
    REC_Status_Date = str_to_date(@REC_Status_Date, '%d-%b-%y');
    
#2015
load data local infile 
"E:\\Documents and Important Documents\\Journalism and Databases\\Ottawa Parking Tickets\\Data\\O Parking Data 2015.csv"
into Table tickets_2015
character set 'utf8'
	Fields terminated by "," enclosed by """" Lines terminated by "\n"
 Ignore 3 Lines
(Issue_No, Issue_Date, @Issue_Time, Agency, Officer_Badge,
Beat, VIOCode, VIODescription, VIOType, VIOFine,
VIOlateFee, Street, a_between, a_and, side_of_street,
meter_number, total_voided, total_non_cash_credit , total_fines_and_fees, total_credits,
total_fines, amount_due, due_date, REC_Status, REC_Status_Date,
REC_Cleared_Reason, Void_Status, Review_Code, Trial_Code)
	set issue_time = str_to_date(@Issue_Time,'%l:%i%p');
    
    select *
    from tickets_2015
    order by Issue_Date desc
    limit 100;
    
  #2015-16 
load data local infile 
"c:\\tickets_2015_16.csv"
into Table tickets_2015_16
character set 'utf8'
	Fields terminated by "," enclosed by """" Lines terminated by "\n"
 Ignore 1 Lines
(Issue_No, @Issue_Date, @Issue_Time, Agency, Officer_Badge,
Beat, VIOCode, VIODescription, VIOType, VIOFine,
VIOlateFee, Street, a_between, a_and, side_of_street,
meter_number, total_voided, total_non_cash_credit , total_fines_and_fees, total_credits,
total_fines, amount_due, @due_date, REC_Status, @REC_Status_Date,
REC_Cleared_Reason, Void_Status, Review_Code, Trial_Code)
	set Issue_Date = str_to_date(@Issue_Date, '%d-%b-%y'),
	issue_time = str_to_date(@Issue_Time,'%l:%i%p'),
    due_date = str_to_date(@due_date, '%d-%b-%y'),
    REC_Status_Date = str_to_date(@REC_Status_Date, '%d-%b-%y');
      
      
    select *
    from tickets_2015_16
    order by due_date asc;
    
    
    #2016-17 
load data local infile 
"c:\\tickets_2016_17.csv"
into Table tickets_2016_17
character set 'utf8'
	Fields terminated by "," enclosed by """" Lines terminated by "\n"
 Ignore 1 Lines
(Issue_No, @Issue_Date, @Issue_Time, Agency, Officer_Badge,
Beat, VIOCode, VIODescription, VIOType, VIOFine,
VIOlateFee, Street, a_between, a_and, side_of_street,
meter_number, total_voided, total_non_cash_credit , total_fines_and_fees, total_credits,
total_fines, amount_due, @due_date, REC_Status, @REC_Status_Date,
REC_Cleared_Reason, Void_Status, Review_Code, Trial_Code)
	set Issue_Date = str_to_date(@Issue_Date, '%d-%b-%y'),
	issue_time = str_to_date(@Issue_Time,'%l:%i%p'),
    due_date = str_to_date(@due_date, '%d-%b-%y'),
    REC_Status_Date = str_to_date(@REC_Status_Date, '%d-%b-%y');
    
    select count(*)
    from tickets_2016_17;
    
    
    select *
    from tickets_2016_17
    order by Issue_Date desc;
    
    ####Create the master table or view;
    
	create view master_view as 
	select * from tickets_2010 Union all
	select * from tickets_2011 Union all
	select * from tickets_2012 Union all
	select * from tickets_2013 Union all
    select * from tickets_2014 Union all
	select * from tickets_2015 Union all
	select * from tickets_2015_2016 Union all
    select * from tickets_2016_17;
    
    select count(*) 
    from master_view;
    
    select *
    from master_view
    order by Issue_Date desc
    limit 100;
    
select count(*)
from master_view;
### this query produces 2,761.442 records

 
 ##### Select query statements
 
 select *
 from master_view
 where Issue_Date > '2017-01-01' 
 and (Agency like '%Algonquin%' or Agency like '%carleton%' or Agency like '%La Cite Collegiale%' or Agency like '%University of Ottawa%');
 
 select Year(Issue_Date) as Year, 
format(count(total_fines_and_fees),0) NumberOfTickets, 
format(Sum(total_fines_and_fees),1) as Total
from master_view
group by year
order by NumberOfTickets  desc;

 
select Agency, count(id_Column) 'Number of tickets'
from master_view
group by Agency
order by count(id_Column) desc;

select Officer_Badge, count(id_Column) 'Number of tickets'
from master_view
group by Officer_Badge
order by count(id_Column) desc;

Select VIODescription, count(id_Column) 'Number of infractions'
from master_view
group by VIODescription
order by count(id_Column) desc;
 
select
concat_ws(' ',Street,'between',a_between,'and',a_and,'and',side_of_street) Address,
count(id_Column) 'Number of tickets'
from master_view
group by Address
order by 'Number of tickets' desc;

select
concat_ws(' ',Street,'between',a_between,'and',a_and,'and',side_of_street) Address,
count(id_Column) 'Number of tickets'
from master_view
where Issue_Date >'2017-01-01'
group by Address
order by 'Number of tickets' desc;

select year(Issue_Date) as Year, 
format(count(id_Column),0) as NumberOfTickets, 
format(sum(total_fines_and_fees),0) 'Total fines'
from master_view
where Agency like '%La Cite Collegiale%'
or Agency like'%Carleton Parking%' 
or Agency like'% Univeristy of Ottawa%'
or Agency like'%Algonquin College%'
group by Year
order by NumberOfTickets desc;

select Year(Issue_Date) as Year, 
format(count(total_fines_and_fees),0) NumberOfTickets, 
format(Sum(total_fines_and_fees),1) as Total
from master_view
group by year
order by  NumberOfTickets desc;

select Issue_Date, 
concat_ws(' ',Street,'between',a_between,'and',a_and,'and',side_of_street) Address,
total_fines_and_fees as fines, VIODescription
from master_view
order by fines desc
limit 1000;

select *
from master_view
where VIODescription = 'STOP IN A SCHOOL BUS LOADING ZONE' and Issue_Date >'2017-01-01';