###################################################################
-- MyLEVs.sql
-- 12/22/09
-- 
-- This script uses the database "LEV_Standards" to  create a new database, "MyLEVs" 
-- with adjusted LEV emission rates for only a subset of model years
--
-- Users must edit the last command in the script to specify the desired model years.
-- See "Instructions for using LEV and NLEV Inputs for MOVES"

###################################################################
-- Create database

drop database if exists MyLEVs;
create database MyLEVs;
use MyLEVs;

###################################################################

-- Create a table to hold the adjusted LEV emission rates.

CREATE TABLE mylevs.emissionratebyage (
  `sourceBinID` bigint(20) NOT NULL default '0',
  `polProcessID` smallint(6) NOT NULL default '0',
  `opModeID` smallint(6) NOT NULL default '0',
  `ageGroupID` smallint(6) NOT NULL default '0',
  `meanBaseRate` float default NULL,
  `meanBaseRateCV` float default NULL,
  `meanBaseRateIM` float default NULL,
  `meanBaseRateIMCV` float default NULL,
  `dataSourceId` smallint(6) default NULL,
  PRIMARY KEY  (`ageGroupID`,`opModeID`,`polProcessID`,`sourceBinID`),
  UNIQUE KEY `XPKEmissionRateByAge` 
    (`sourceBinID`,`polProcessID`,`opModeID`,`ageGroupID`),
  KEY `polProcessID` (`polProcessID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

###################################################################

-- Table listing short codes and model year.

-- shortModYrGroupID | modelYearGroupName 
--                94 | 1994               
--                95 | 1995               
--                96 | 1996               
--                97 | 1997               
--                98 | 1998               
--                99 | 1999               
--                20 | 2000               
--                21 | 2001               
--                22 | 2002               
--                23 | 2003               
--                24 | 2004               
--                25 | 2005               
--                26 | 2006               
--                27 | 2007               
--                28 | 2008               
--                29 | 2009               
--                30 | 2010               
--                31 | 2011               
--                32 | 2012               
--                33 | 2013               
--                34 | 2014               
--                35 | 2015               
--                36 | 2016               
--                37 | 2017               
--                38 | 2018               
--                39 | 2019               
--                40 | 2020
--                07 | 2021-2050

-- Model years 1994-2002 are LEV1 program model years.
-- Model years 2003-2050 are LEV2 program model years. 

###################################################################

-- Insert values for LEV emission rates for years covered by program.

-- Edit this command as described in "Instructions for using LEV and NLEV Inputs for MOVES" 

--       In the sequence of short codes at the bottom of this section,
--       keep the codes that correspond to model years in the LEV Program; and
--       delete the codes that correspond to model years NOT in the LEV Program.
--       Make sure that a comma is not between a number and a parenthesis.

insert into mylevs.emissionratebyage (
	sourcebinid,
	polprocessid,
	opmodeid,
	agegroupid,
	meanbaserate,
	meanbaseratecv,
	meanbaserateim,
	meanbaserateimcv,
	datasourceid )
select  
	sourcebinid,
	polprocessid,
	opmodeid,
	agegroupid,
	meanbaserate,
	meanbaseratecv,
	meanbaserateim,
	meanbaserateimcv,
	datasourceid 
from LEV_Standards.emissionratebyage
where polprocessid in (101,201,301,102,202,302)
and mid(sourcebinid,8,2) in 
	(94,95,96,97,98,99,20,21,22,
	23,24,25,26,27,28,29,30,31,32,
	33,34,35,36,37,38,39,40,07);

###################################################################














