create schema Ottawa_311_update;

use ottawa_311_update;

create table if not exists SC_2017_test(
id_num mediumInt not null auto_increment primary key,
creation_date datetime,
ward varchar(50),
call_description varchar(35),
call_type varchar(50),
maintenance_yard varchar(75),
priority_name varchar(120)
);

   #January
load data local infile 
"c:\\01-sr-2017.csv"
into Table SC_2017_test
Fields terminated by ',' enclosed by '"' Lines terminated by '\n'
 Ignore 1 Lines
 (maintenance_yard, call_type, call_description, priority_name, ward, @temp, @temp2)
	set creation_date = makedate('2017', dayofyear('2017-01-01'));
    
    select count(*)
    from sc_2017_test;
    
    select *
    from sc_2017_test
    order by creation_date desc
    limit 100;
    
    select *
    from sc_2017_test
    where call_type ='Animals';
    
    #Feburary
 load data local infile 
"c:\\02-sr-2017.csv"
into Table SC_2017_test
Fields terminated by ',' enclosed by '"' Lines terminated by '\n'
 Ignore 1 Lines
 (maintenance_yard, call_type, call_description, priority_name, ward, @temp, @temp2)
	set creation_date = makedate('2017', dayofyear('2017-02-01'));
    
    select *
    from sc_2017_test
    order by creation_date desc;
    
        #March
 load data local infile 
"c:\\03-sr-2017.csv"
into Table SC_2017_test
Fields terminated by ',' enclosed by '"' Lines terminated by '\n'
 Ignore 1 Lines
 (maintenance_yard, call_type, call_description, priority_name, ward, @temp, @temp2)
	set creation_date = makedate('2017', dayofyear('2017-03-01'));
    
            #April
 load data local infile 
"c:\\04-sr-2017.csv"
into Table SC_2017_test
Fields terminated by ',' enclosed by '"' Lines terminated by '\n'
 Ignore 1 Lines
 (maintenance_yard, call_type, call_description, priority_name, ward, @temp, @temp2)
	set creation_date = makedate('2017', dayofyear('2017-04-01'));
    
    
                #May
 load data local infile 
"c:\\05-sr-2017.csv"
into Table SC_2017_test
Fields terminated by ',' enclosed by '"' Lines terminated by '\n'
 Ignore 1 Lines
 (maintenance_yard, call_type, call_description, priority_name, ward, @temp, @temp2)
	set creation_date = makedate('2017', dayofyear('2017-05-01'));
    
                    #June
 load data local infile 
"c:\\06-sr-2017.csv"
into Table SC_2017_test
Fields terminated by ',' enclosed by '"' Lines terminated by '\n'
 Ignore 1 Lines
 (maintenance_yard, call_type, call_description, priority_name, ward, @temp, @temp2)
	set creation_date = makedate('2017', dayofyear('2017-06-01'));
    
                       #July
 load data local infile 
"c:\\07-sr-2017.csv"
into Table SC_2017_test
Fields terminated by ',' enclosed by '"' Lines terminated by '\n'
 Ignore 1 Lines
 (maintenance_yard, call_type, call_description, priority_name, ward, @temp, @temp2)
	set creation_date = makedate('2017', dayofyear('2017-07-01'));
    
    
                           #August
 load data local infile 
"c:\\08-sr-2017.csv"
into Table SC_2017_test
Fields terminated by ',' enclosed by '"' Lines terminated by '\n'
 Ignore 1 Lines
 (maintenance_yard, call_type, call_description, priority_name, ward, @temp, @temp2)
	set creation_date = makedate('2017', dayofyear('2017-08-01'));
    
    select *
    from sc_2017_test
    order by creation_date desc;
    
  ##### Having a complete dataset for all the years, allows us to conduct
  ##### queries for specific years, as we have done with 2017. 
  
  ##### In order to make it easier to query all the tables at once, we have combined
  ##### the tables using a combination of a "select" and "union all " query, than then creating 
  ##### what's called a "View" which you can see below, and is explaind on page 108 of The Data Journalist. We have used a 
  ##### 
    create or replace view SC_master as 
	select * from sc_2013 union all
    select * from sc_2014 union all
    select * from sc_2015 union all
    select * from sc_2016 union all
    select * from sc_2017_test;
    
    ##### now that we have added the 2017 dataset to the previous year, 
    select * from sc_master
    order by creation_date desc
    limit 10000;
    
    select count(id_num) 'complaints', year(creation_date) 'year'
    from sc_master
    group by year
    order by complaints desc;
    
select *
from sc_master
where creation_date < '2016-12-31' AND call_description like '%Dog Bite%';

select count(*) 'NumberofBites', trim(ward)
from sc_master
where call_description like '%Dog Bite%' 
group by ward
order by NumberofBites desc;

select count(*) 'NumberofBites', ward, month(creation_date) month
from sc_master
where call_description like '%Dog Bite%' 
group by ward, month
order by creation_date desc;


select count(*), year(creation_date) 
from sc_master 
group by year(creation_date);


select ward, count(*) 'NumberofBites'
from sc_master
where call_description like '%Dog Bite%'
group by ward with rollup;


select *
from sc_master
WHERE call_description LIKE '%graffiti%';

select COUNT(*), WARD
from sc_master
WHERE call_description LIKE '%graffiti%'
GROUP BY WARD
ORDER BY COUNT(*) DESC;


select  count(*) 'Number of Complaints', ward
from sc_master
where call_description like '%Unsightly%' or call_description like'%Limb%'
group by ward
order by count(*) desc;