MySQL_Scripts/create_import_test_control_total_tables at master · msimonson-psrc/MySQL_Scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
alter table annual_employment_control_totals add index(city_id);


#create table temp_county_20
SELECT
#annual_household_control_totals.city_id
#,cities_orig_to_run.city_name
cities_orig_to_run.county_id
,annual_household_control_totals.year
,annual_household_control_totals.persons_min
,annual_household_control_totals.persons_max
,annual_household_control_totals.workers_min
,annual_household_control_totals.workers_max
,annual_household_control_totals.income_min
,annual_household_control_totals.income_max
,sum(annual_household_control_totals.total_number_of_households) as tot_hh
FROM craigs_test_run.annual_household_control_totals
inner join craigs_test_run.cities_orig_to_run
on craigs_test_run.annual_household_control_totals.city_id = craigs_test_run.cities_orig_to_run.city_id
where annual_household_control_totals.year in (2020)
group by
#annual_household_control_totals.city_id
#,cities_orig_to_run.city_name
cities_orig_to_run.county_id
,annual_household_control_totals.year
,annual_household_control_totals.persons_min
,annual_household_control_totals.persons_max
,annual_household_control_totals.workers_min
,annual_household_control_totals.workers_max
,annual_household_control_totals.income_min
,annual_household_control_totals.income_max
;

SELECT
#annual_household_control_totals.city_id
#,cities_orig_to_run.city_name
cities_orig_to_run.county_id
,annual_employment_control_totals.year
,annual_employment_control_totals.home_based_status
,annual_employment_control_totals.sector_id
,sum(annual_employment_control_totals.total_number_of_jobs) as tot_jobs
FROM craigs_test_run.annual_employment_control_totals
inner join craigs_test_run.cities_orig_to_run
on craigs_test_run.annual_employment_control_totals.city_id = craigs_test_run.cities_orig_to_run.city_id
#where annual_employment_control_totals.year in (2020)
group by
#annual_household_control_totals.city_id
#,cities_orig_to_run.city_name
cities_orig_to_run.county_id
,annual_employment_control_totals.year
,annual_employment_control_totals.home_based_status
,annual_employment_control_totals.sector_id
;

## Sample CSV import scripts

#  Import for MPD records

drop table craigs_test_run.annual_household_contro_totals_craigtestrun;

create table craigs_test_run.annual_household_control_totals_craigstestrun
(
total_number_of_households integer(11) not null
,city_id integer(11) not null
,year integer(11) not null
,persons_min integer(11) not null
,income_min integer(11) not null
,workers_min integer(11) not null
,workers_max integer(11) not null
,income_max integer(11) not null
,persons_max integer(11) not null
);

LOAD DATA LOCAL INFILE 'T:/2019September/MarkS/annual_household_control_totals_craigstestrun.csv'
INTO TABLE craigs_test_run.annual_household_control_totals_craigstestrun
FIELDS TERMINATED BY ','
#ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
total_number_of_households
,city_id
,year
,persons_min
,income_min
,workers_min
,workers_max
,income_max
,persons_max
);

create table craigs_test_run.annual_employment_control_totals_craigstestrun
(
city_id integer(11) not null
,home_based_status integer(11) not null
,year integer(11) not null
,total_number_of_jobs integer(11) not null
,sector_id integer(11) not null
);

LOAD DATA LOCAL INFILE 'T:/2019September/MarkS/annual_employment_control_totals_craigstestrun.csv'
INTO TABLE craigs_test_run.annual_employment_control_totals_craigstestrun
FIELDS TERMINATED BY ','
#ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(
city_id
,home_based_status
,year
,total_number_of_jobs
,sector_id
);