ECO_Revenue_Tool_LUV2_Extracts

#  Edits needed if running on another scenario - search in replace in Notepad:
#   - The MySQL directory:  Here, its 'data_request_eco_revenue_tool'
#   - The name of the parcels table with the ST and PTBA districts:  Here, its 'luv2_parcels_ptba_st'
#   - The directory structure with the UrbanSim run outputs in MySQL:  Here, its 'luv2_final_outputs_XXXX' where XXXX is the year (2014,2020...2050)
#   - Sector Number that's retail:  Here, its (7)... but needs to be (5) in the new 13 sector system

#  Also might need to add parcel_id to the households and jobs table.  
#  Uncomment the following section if so

/*
alter table luv2_final_outputs_2014.households add column parcel_id integer;
update luv2_final_outputs_2014.households set parcel_id = 0;
alter table luv2_final_outputs_2014.households add index(building_id);
update luv2_final_outputs_2014.households inner join luv2_final_outputs_2014.buildings
on luv2_final_outputs_2014.households.building_id = luv2_final_outputs_2014.buildings.building_id
set luv2_final_outputs_2014.households.parcel_id = luv2_final_outputs_2014.buildings.parcel_id;
alter table luv2_final_outputs_2014.households add index(parcel_id);

alter table luv2_final_outputs_2014.jobs add column parcel_id integer;
update luv2_final_outputs_2014.jobs set parcel_id = 0;
alter table luv2_final_outputs_2014.jobs add index(building_id);
update luv2_final_outputs_2014.jobs inner join luv2_final_outputs_2014.buildings
on luv2_final_outputs_2014.jobs.building_id = luv2_final_outputs_2014.buildings.building_id
set luv2_final_outputs_2014.jobs.parcel_id = luv2_final_outputs_2014.buildings.parcel_id;
alter table luv2_final_outputs_2014.jobs add index(parcel_id);

alter table luv2_final_outputs_2020.households add column parcel_id integer;
update luv2_final_outputs_2020.households set parcel_id = 0;
alter table luv2_final_outputs_2020.households add index(building_id);
update luv2_final_outputs_2020.households inner join luv2_final_outputs_2020.buildings
on luv2_final_outputs_2020.households.building_id = luv2_final_outputs_2020.buildings.building_id
set luv2_final_outputs_2020.households.parcel_id = luv2_final_outputs_2020.buildings.parcel_id;
alter table luv2_final_outputs_2020.households add index(parcel_id);

alter table luv2_final_outputs_2020.jobs add column parcel_id integer;
update luv2_final_outputs_2020.jobs set parcel_id = 0;
alter table luv2_final_outputs_2020.jobs add index(building_id);
update luv2_final_outputs_2020.jobs inner join luv2_final_outputs_2020.buildings
on luv2_final_outputs_2020.jobs.building_id = luv2_final_outputs_2020.buildings.building_id
set luv2_final_outputs_2020.jobs.parcel_id = luv2_final_outputs_2020.buildings.parcel_id;
alter table luv2_final_outputs_2020.jobs add index(parcel_id);

alter table luv2_final_outputs_2025.households add column parcel_id integer;
update luv2_final_outputs_2025.households set parcel_id = 0;
alter table luv2_final_outputs_2025.households add index(building_id);
update luv2_final_outputs_2025.households inner join luv2_final_outputs_2025.buildings
on luv2_final_outputs_2025.households.building_id = luv2_final_outputs_2025.buildings.building_id
set luv2_final_outputs_2025.households.parcel_id = luv2_final_outputs_2025.buildings.parcel_id;
alter table luv2_final_outputs_2025.households add index(parcel_id);

alter table luv2_final_outputs_2025.jobs add column parcel_id integer;
update luv2_final_outputs_2025.jobs set parcel_id = 0;
alter table luv2_final_outputs_2025.jobs add index(building_id);
update luv2_final_outputs_2025.jobs inner join luv2_final_outputs_2025.buildings
on luv2_final_outputs_2025.jobs.building_id = luv2_final_outputs_2025.buildings.building_id
set luv2_final_outputs_2025.jobs.parcel_id = luv2_final_outputs_2025.buildings.parcel_id;
alter table luv2_final_outputs_2025.jobs add index(parcel_id);

alter table luv2_final_outputs_2030.households add column parcel_id integer;
update luv2_final_outputs_2030.households set parcel_id = 0;
alter table luv2_final_outputs_2030.households add index(building_id);
update luv2_final_outputs_2030.households inner join luv2_final_outputs_2030.buildings
on luv2_final_outputs_2030.households.building_id = luv2_final_outputs_2030.buildings.building_id
set luv2_final_outputs_2030.households.parcel_id = luv2_final_outputs_2030.buildings.parcel_id;
alter table luv2_final_outputs_2030.households add index(parcel_id);

alter table luv2_final_outputs_2030.jobs add column parcel_id integer;
update luv2_final_outputs_2030.jobs set parcel_id = 0;
alter table luv2_final_outputs_2030.jobs add index(building_id);
update luv2_final_outputs_2030.jobs inner join luv2_final_outputs_2030.buildings
on luv2_final_outputs_2030.jobs.building_id = luv2_final_outputs_2030.buildings.building_id
set luv2_final_outputs_2030.jobs.parcel_id = luv2_final_outputs_2030.buildings.parcel_id;
alter table luv2_final_outputs_2030.jobs add index(parcel_id);

alter table luv2_final_outputs_2035.households add column parcel_id integer;
update luv2_final_outputs_2035.households set parcel_id = 0;
alter table luv2_final_outputs_2035.households add index(building_id);
update luv2_final_outputs_2035.households inner join luv2_final_outputs_2035.buildings
on luv2_final_outputs_2035.households.building_id = luv2_final_outputs_2035.buildings.building_id
set luv2_final_outputs_2035.households.parcel_id = luv2_final_outputs_2035.buildings.parcel_id;
alter table luv2_final_outputs_2035.households add index(parcel_id);

alter table luv2_final_outputs_2035.jobs add column parcel_id integer;
update luv2_final_outputs_2035.jobs set parcel_id = 0;
alter table luv2_final_outputs_2035.jobs add index(building_id);
update luv2_final_outputs_2035.jobs inner join luv2_final_outputs_2035.buildings
on luv2_final_outputs_2035.jobs.building_id = luv2_final_outputs_2035.buildings.building_id
set luv2_final_outputs_2035.jobs.parcel_id = luv2_final_outputs_2035.buildings.parcel_id;
alter table luv2_final_outputs_2035.jobs add index(parcel_id);

alter table luv2_final_outputs_2040.households add column parcel_id integer;
update luv2_final_outputs_2040.households set parcel_id = 0;
alter table luv2_final_outputs_2040.households add index(building_id);
update luv2_final_outputs_2040.households inner join luv2_final_outputs_2040.buildings
on luv2_final_outputs_2040.households.building_id = luv2_final_outputs_2040.buildings.building_id
set luv2_final_outputs_2040.households.parcel_id = luv2_final_outputs_2040.buildings.parcel_id;
alter table luv2_final_outputs_2040.households add index(parcel_id);

alter table luv2_final_outputs_2040.jobs add column parcel_id integer;
update luv2_final_outputs_2040.jobs set parcel_id = 0;
alter table luv2_final_outputs_2040.jobs add index(building_id);
update luv2_final_outputs_2040.jobs inner join luv2_final_outputs_2040.buildings
on luv2_final_outputs_2040.jobs.building_id = luv2_final_outputs_2040.buildings.building_id
set luv2_final_outputs_2040.jobs.parcel_id = luv2_final_outputs_2040.buildings.parcel_id;
alter table luv2_final_outputs_2040.jobs add index(parcel_id);

alter table luv2_final_outputs_2045.households add column parcel_id integer;
update luv2_final_outputs_2045.households set parcel_id = 0;
alter table luv2_final_outputs_2045.households add index(building_id);
update luv2_final_outputs_2045.households inner join luv2_final_outputs_2045.buildings
on luv2_final_outputs_2045.households.building_id = luv2_final_outputs_2045.buildings.building_id
set luv2_final_outputs_2045.households.parcel_id = luv2_final_outputs_2045.buildings.parcel_id;
alter table luv2_final_outputs_2045.households add index(parcel_id);

alter table luv2_final_outputs_2045.jobs add column parcel_id integer;
update luv2_final_outputs_2045.jobs set parcel_id = 0;
alter table luv2_final_outputs_2045.jobs add index(building_id);
update luv2_final_outputs_2045.jobs inner join luv2_final_outputs_2045.buildings
on luv2_final_outputs_2045.jobs.building_id = luv2_final_outputs_2045.buildings.building_id
set luv2_final_outputs_2045.jobs.parcel_id = luv2_final_outputs_2045.buildings.parcel_id;
alter table luv2_final_outputs_2045.jobs add index(parcel_id);

alter table luv2_final_outputs_2050.households add column parcel_id integer;
update luv2_final_outputs_2050.households set parcel_id = 0;
alter table luv2_final_outputs_2050.households add index(building_id);
update luv2_final_outputs_2050.households inner join luv2_final_outputs_2050.buildings
on luv2_final_outputs_2050.households.building_id = luv2_final_outputs_2050.buildings.building_id
set luv2_final_outputs_2050.households.parcel_id = luv2_final_outputs_2050.buildings.parcel_id;
alter table luv2_final_outputs_2050.households add index(parcel_id);

alter table luv2_final_outputs_2050.jobs add column parcel_id integer;
update luv2_final_outputs_2050.jobs set parcel_id = 0;
alter table luv2_final_outputs_2050.jobs add index(building_id);
update luv2_final_outputs_2050.jobs inner join luv2_final_outputs_2050.buildings
on luv2_final_outputs_2050.jobs.building_id = luv2_final_outputs_2050.buildings.building_id
set luv2_final_outputs_2050.jobs.parcel_id = luv2_final_outputs_2050.buildings.parcel_id;
alter table luv2_final_outputs_2050.jobs add index(parcel_id);
*/

#  End of the optional section that adds parcel_id to HHs and Jobs if needed

DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_ptba_2014;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_ptba_2020;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_ptba_2025;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_ptba_2030;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_ptba_2035;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_ptba_2040;

DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_st_2014;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_st_2020;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_st_2025;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_st_2030;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_st_2035;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_st_2040;

DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_city_2014;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_city_2020;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_city_2025;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_city_2030;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_city_2035;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.res_city_2040;

DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_ptba_2014;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_ptba_2020;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_ptba_2025;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_ptba_2030;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_ptba_2035;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_ptba_2040;

DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_st_2014;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_st_2020;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_st_2025;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_st_2030;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_st_2035;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_st_2040;

DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_city_2014;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_city_2020;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_city_2025;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_city_2030;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_city_2035;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.emp_city_2040;

DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_ptba_2014;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_ptba_2020;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_ptba_2025;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_ptba_2030;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_ptba_2035;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_ptba_2040;

DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_st_2014;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_st_2020;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_st_2025;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_st_2030;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_st_2035;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_st_2040;

DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_city_2014;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_city_2020;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_city_2025;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_city_2030;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_city_2035;
DROP TABLE IF EXISTS data_request_eco_revenue_tool.retemp_city_2040;

## 2014

create table data_request_eco_revenue_tool.res_ptba_2014
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2014.households.household_id) as households
,sum(luv2_final_outputs_2014.households.persons) as persons
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2014.households
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2014.households.parcel_id
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.res_st_2014
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2014.households.household_id) as households
,sum(luv2_final_outputs_2014.households.persons) as persons
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2014.households
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2014.households.parcel_id
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.res_city_2014
SELECT
luv2_final_outputs_2014.parcels.city_id
,count(luv2_final_outputs_2014.households.household_id) as households
,sum(luv2_final_outputs_2014.households.persons) as persons
FROM luv2_final_outputs_2014.parcels
inner join luv2_final_outputs_2014.households
on luv2_final_outputs_2014.parcels.parcel_id = luv2_final_outputs_2014.households.parcel_id
GROUP BY
city_id;

create table data_request_eco_revenue_tool.emp_ptba_2014
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2014.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2014.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2014.jobs.parcel_id
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.emp_st_2014
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2014.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2014.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2014.jobs.parcel_id
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.emp_city_2014
SELECT
luv2_final_outputs_2014.parcels.city_id
,count(luv2_final_outputs_2014.jobs.job_id) as jobs
FROM luv2_final_outputs_2014.parcels
inner join luv2_final_outputs_2014.jobs
on luv2_final_outputs_2014.parcels.parcel_id = luv2_final_outputs_2014.jobs.parcel_id
GROUP BY
city_id;

create table data_request_eco_revenue_tool.retemp_ptba_2014
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2014.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2014.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2014.jobs.parcel_id
WHERE luv2_final_outputs_2014.jobs.sector_id in (7)
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.retemp_st_2014
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2014.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2014.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2014.jobs.parcel_id
WHERE luv2_final_outputs_2014.jobs.sector_id in (7)
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.retemp_city_2014
SELECT
luv2_final_outputs_2014.parcels.city_id
,count(luv2_final_outputs_2014.jobs.job_id) as jobs
FROM luv2_final_outputs_2014.parcels
inner join luv2_final_outputs_2014.jobs
on luv2_final_outputs_2014.parcels.parcel_id = luv2_final_outputs_2014.jobs.parcel_id
WHERE luv2_final_outputs_2014.jobs.sector_id in (7)
GROUP BY
city_id;


## 2020

create table data_request_eco_revenue_tool.res_ptba_2020
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2020.households.household_id) as households
,sum(luv2_final_outputs_2020.households.persons) as persons
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2020.households
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2020.households.parcel_id
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.res_st_2020
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2020.households.household_id) as households
,sum(luv2_final_outputs_2020.households.persons) as persons
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2020.households
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2020.households.parcel_id
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.res_city_2020
SELECT
luv2_final_outputs_2020.parcels.city_id
,count(luv2_final_outputs_2020.households.household_id) as households
,sum(luv2_final_outputs_2020.households.persons) as persons
FROM luv2_final_outputs_2020.parcels
inner join luv2_final_outputs_2020.households
on luv2_final_outputs_2020.parcels.parcel_id = luv2_final_outputs_2020.households.parcel_id
GROUP BY
city_id;

create table data_request_eco_revenue_tool.emp_ptba_2020
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2020.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2020.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2020.jobs.parcel_id
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.emp_st_2020
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2020.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2020.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2020.jobs.parcel_id
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.emp_city_2020
SELECT
luv2_final_outputs_2020.parcels.city_id
,count(luv2_final_outputs_2020.jobs.job_id) as jobs
FROM luv2_final_outputs_2020.parcels
inner join luv2_final_outputs_2020.jobs
on luv2_final_outputs_2020.parcels.parcel_id = luv2_final_outputs_2020.jobs.parcel_id
GROUP BY
city_id;

create table data_request_eco_revenue_tool.retemp_ptba_2020
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2020.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2020.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2020.jobs.parcel_id
WHERE luv2_final_outputs_2020.jobs.sector_id in (7)
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.retemp_st_2020
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2020.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2020.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2020.jobs.parcel_id
WHERE luv2_final_outputs_2020.jobs.sector_id in (7)
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.retemp_city_2020
SELECT
luv2_final_outputs_2020.parcels.city_id
,count(luv2_final_outputs_2020.jobs.job_id) as jobs
FROM luv2_final_outputs_2020.parcels
inner join luv2_final_outputs_2020.jobs
on luv2_final_outputs_2020.parcels.parcel_id = luv2_final_outputs_2020.jobs.parcel_id
WHERE luv2_final_outputs_2020.jobs.sector_id in (7)
GROUP BY
city_id;


## 2025

create table data_request_eco_revenue_tool.res_ptba_2025
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2025.households.household_id) as households
,sum(luv2_final_outputs_2025.households.persons) as persons
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2025.households
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2025.households.parcel_id
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.res_st_2025
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2025.households.household_id) as households
,sum(luv2_final_outputs_2025.households.persons) as persons
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2025.households
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2025.households.parcel_id
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.res_city_2025
SELECT
luv2_final_outputs_2025.parcels.city_id
,count(luv2_final_outputs_2025.households.household_id) as households
,sum(luv2_final_outputs_2025.households.persons) as persons
FROM luv2_final_outputs_2025.parcels
inner join luv2_final_outputs_2025.households
on luv2_final_outputs_2025.parcels.parcel_id = luv2_final_outputs_2025.households.parcel_id
GROUP BY
city_id;

create table data_request_eco_revenue_tool.emp_ptba_2025
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2025.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2025.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2025.jobs.parcel_id
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.emp_st_2025
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2025.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2025.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2025.jobs.parcel_id
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.emp_city_2025
SELECT
luv2_final_outputs_2025.parcels.city_id
,count(luv2_final_outputs_2025.jobs.job_id) as jobs
FROM luv2_final_outputs_2025.parcels
inner join luv2_final_outputs_2025.jobs
on luv2_final_outputs_2025.parcels.parcel_id = luv2_final_outputs_2025.jobs.parcel_id
GROUP BY
city_id;

create table data_request_eco_revenue_tool.retemp_ptba_2025
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2025.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2025.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2025.jobs.parcel_id
WHERE luv2_final_outputs_2025.jobs.sector_id in (7)
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.retemp_st_2025
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2025.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2025.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2025.jobs.parcel_id
WHERE luv2_final_outputs_2025.jobs.sector_id in (7)
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.retemp_city_2025
SELECT
luv2_final_outputs_2025.parcels.city_id
,count(luv2_final_outputs_2025.jobs.job_id) as jobs
FROM luv2_final_outputs_2025.parcels
inner join luv2_final_outputs_2025.jobs
on luv2_final_outputs_2025.parcels.parcel_id = luv2_final_outputs_2025.jobs.parcel_id
WHERE luv2_final_outputs_2025.jobs.sector_id in (7)
GROUP BY
city_id;


## 2030

create table data_request_eco_revenue_tool.res_ptba_2030
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2030.households.household_id) as households
,sum(luv2_final_outputs_2030.households.persons) as persons
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2030.households
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2030.households.parcel_id
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.res_st_2030
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2030.households.household_id) as households
,sum(luv2_final_outputs_2030.households.persons) as persons
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2030.households
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2030.households.parcel_id
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.res_city_2030
SELECT
luv2_final_outputs_2030.parcels.city_id
,count(luv2_final_outputs_2030.households.household_id) as households
,sum(luv2_final_outputs_2030.households.persons) as persons
FROM luv2_final_outputs_2030.parcels
inner join luv2_final_outputs_2030.households
on luv2_final_outputs_2030.parcels.parcel_id = luv2_final_outputs_2030.households.parcel_id
GROUP BY
city_id;

create table data_request_eco_revenue_tool.emp_ptba_2030
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2030.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2030.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2030.jobs.parcel_id
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.emp_st_2030
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2030.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2030.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2030.jobs.parcel_id
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.emp_city_2030
SELECT
luv2_final_outputs_2030.parcels.city_id
,count(luv2_final_outputs_2030.jobs.job_id) as jobs
FROM luv2_final_outputs_2030.parcels
inner join luv2_final_outputs_2030.jobs
on luv2_final_outputs_2030.parcels.parcel_id = luv2_final_outputs_2030.jobs.parcel_id
GROUP BY
city_id;

create table data_request_eco_revenue_tool.retemp_ptba_2030
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2030.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2030.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2030.jobs.parcel_id
WHERE luv2_final_outputs_2030.jobs.sector_id in (7)
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.retemp_st_2030
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2030.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2030.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2030.jobs.parcel_id
WHERE luv2_final_outputs_2030.jobs.sector_id in (7)
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.retemp_city_2030
SELECT
luv2_final_outputs_2030.parcels.city_id
,count(luv2_final_outputs_2030.jobs.job_id) as jobs
FROM luv2_final_outputs_2030.parcels
inner join luv2_final_outputs_2030.jobs
on luv2_final_outputs_2030.parcels.parcel_id = luv2_final_outputs_2030.jobs.parcel_id
WHERE luv2_final_outputs_2030.jobs.sector_id in (7)
GROUP BY
city_id;


## 2035

create table data_request_eco_revenue_tool.res_ptba_2035
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2035.households.household_id) as households
,sum(luv2_final_outputs_2035.households.persons) as persons
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2035.households
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2035.households.parcel_id
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.res_st_2035
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2035.households.household_id) as households
,sum(luv2_final_outputs_2035.households.persons) as persons
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2035.households
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2035.households.parcel_id
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.res_city_2035
SELECT
luv2_final_outputs_2035.parcels.city_id
,count(luv2_final_outputs_2035.households.household_id) as households
,sum(luv2_final_outputs_2035.households.persons) as persons
FROM luv2_final_outputs_2035.parcels
inner join luv2_final_outputs_2035.households
on luv2_final_outputs_2035.parcels.parcel_id = luv2_final_outputs_2035.households.parcel_id
GROUP BY
city_id;

create table data_request_eco_revenue_tool.emp_ptba_2035
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2035.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2035.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2035.jobs.parcel_id
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.emp_st_2035
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2035.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2035.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2035.jobs.parcel_id
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.emp_city_2035
SELECT
luv2_final_outputs_2035.parcels.city_id
,count(luv2_final_outputs_2035.jobs.job_id) as jobs
FROM luv2_final_outputs_2035.parcels
inner join luv2_final_outputs_2035.jobs
on luv2_final_outputs_2035.parcels.parcel_id = luv2_final_outputs_2035.jobs.parcel_id
GROUP BY
city_id;

create table data_request_eco_revenue_tool.retemp_ptba_2035
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2035.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2035.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2035.jobs.parcel_id
WHERE luv2_final_outputs_2035.jobs.sector_id in (7)
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.retemp_st_2035
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2035.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2035.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2035.jobs.parcel_id
WHERE luv2_final_outputs_2035.jobs.sector_id in (7)
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.retemp_city_2035
SELECT
luv2_final_outputs_2035.parcels.city_id
,count(luv2_final_outputs_2035.jobs.job_id) as jobs
FROM luv2_final_outputs_2035.parcels
inner join luv2_final_outputs_2035.jobs
on luv2_final_outputs_2035.parcels.parcel_id = luv2_final_outputs_2035.jobs.parcel_id
WHERE luv2_final_outputs_2035.jobs.sector_id in (7)
GROUP BY
city_id;


## 2040

create table data_request_eco_revenue_tool.res_ptba_2040
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2040.households.household_id) as households
,sum(luv2_final_outputs_2040.households.persons) as persons
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2040.households
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2040.households.parcel_id
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.res_st_2040
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2040.households.household_id) as households
,sum(luv2_final_outputs_2040.households.persons) as persons
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2040.households
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2040.households.parcel_id
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.res_city_2040
SELECT
luv2_final_outputs_2040.parcels.city_id
,count(luv2_final_outputs_2040.households.household_id) as households
,sum(luv2_final_outputs_2040.households.persons) as persons
FROM luv2_final_outputs_2040.parcels
inner join luv2_final_outputs_2040.households
on luv2_final_outputs_2040.parcels.parcel_id = luv2_final_outputs_2040.households.parcel_id
GROUP BY
city_id;

create table data_request_eco_revenue_tool.emp_ptba_2040
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2040.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2040.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2040.jobs.parcel_id
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.emp_st_2040
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2040.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2040.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2040.jobs.parcel_id
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.emp_city_2040
SELECT
luv2_final_outputs_2040.parcels.city_id
,count(luv2_final_outputs_2040.jobs.job_id) as jobs
FROM luv2_final_outputs_2040.parcels
inner join luv2_final_outputs_2040.jobs
on luv2_final_outputs_2040.parcels.parcel_id = luv2_final_outputs_2040.jobs.parcel_id
GROUP BY
city_id;

create table data_request_eco_revenue_tool.retemp_ptba_2040
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.PTBA_NAME
,count(luv2_final_outputs_2040.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2040.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2040.jobs.parcel_id
WHERE luv2_final_outputs_2040.jobs.sector_id in (7)
GROUP BY
PTBA_NAME;

create table data_request_eco_revenue_tool.retemp_st_2040
SELECT
data_request_eco_revenue_tool.luv2_parcels_ptba_st.ST_DIST
,count(luv2_final_outputs_2040.jobs.job_id) as jobs
FROM data_request_eco_revenue_tool.luv2_parcels_ptba_st
inner join luv2_final_outputs_2040.jobs
on data_request_eco_revenue_tool.luv2_parcels_ptba_st.parcel_id = luv2_final_outputs_2040.jobs.parcel_id
WHERE luv2_final_outputs_2040.jobs.sector_id in (7)
GROUP BY
ST_DIST;

create table data_request_eco_revenue_tool.retemp_city_2040
SELECT
luv2_final_outputs_2040.parcels.city_id
,count(luv2_final_outputs_2040.jobs.job_id) as jobs
FROM luv2_final_outputs_2040.parcels
inner join luv2_final_outputs_2040.jobs
on luv2_final_outputs_2040.parcels.parcel_id = luv2_final_outputs_2040.jobs.parcel_id
WHERE luv2_final_outputs_2040.jobs.sector_id in (7)
GROUP BY
city_id;