
-- Version: 2014 09 24
-- Verify version Update at end of Script: '20120509'  (yyyymmdd)
-- MOVES default database version movesdb20151028 (MOVES2014a).  ADB 12-2-2015
-- Updated Check #49, #125 and #132. (#31-#39)
-- Imcoverage checks updated to ignore useIMyn="N".
-- Disabled checks #48 and #52 to accomdate the FuelSupplyYear table going only to 2015.
-- Changed error 302 to describe: 'See errImCoverage table'  (2014 06 23) kjr
-- changed 'order by' for cursor curIMCov                    (2014 06 24) kjr
-- changed placement of 'set LENDMY = CENDMY;'               (2014 06 24) kjr
-- added drop procedur at end                                (2014 07 21) kjr
-- Converted to MOVES2014 (no SCCRoadTypeDistribution table, no isLeapYear fieldin the monthVMTFraction table, no countyID field in the fuelSupply table) -- ADB 12-2-2015 

Drop   table if exists     errImCoverage;
Create Table if Not Exists errImCoverage (
  Cpol   int,          -- polProcessId
  Ccou   int,          -- CountyId
  Cyea   int,          -- yearId
  Csou   int,          -- sourceTypeId
  Cfue   int,          -- fuelTypeId
  LENDMY int,          -- last    row's end model year
  CBEGMY int,          -- Current row's beg model year
  CENDMY int,          -- Current row's end model year
  useIMyn char(1),
  Reason varchar(40) );



-- ----------------------------------
-- ----------------------------------

-- 3 Im Coverage stored proc

-- select '  .. 1 Im Coverage Stored Proc';




Delimiter $$
DROP   PROCEDURE IF EXISTS checkImCoverage$$
CREATE PROCEDURE checkImCoverage()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  declare Cpol, Ccou, Cyea, Csou, Cfue, CBEGMY, CENDMY int;  -- C is for current (row of data)
  declare Lpol, Lcou, Lyea, Lsou, Lfue, LBEGMY, LENDMY int;  -- L is for last     "   "  "
  declare rows_processed,
          rows_skipped   int default 0;
  declare CuseIMyn char(1);
  declare reason char(40);
  declare sameSet int;
  DECLARE curIMCov CURSOR FOR SELECT polProcessId,
                                     countyId,
                                     yearId,
                                     sourceTypeId,
                                     fuelTypeId,
                                     begModelYearId,
                                     endModelYearId,
                                     useIMyn
                                FROM imCoverage
                            ORDER BY useIMyn,
                                     polProcessId,
                                     countyId,
                                     yearId,
                                     sourceTypeId,
                                     fuelTypeId,
                                     begModelYearId,
                                     endModelYearId;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN curIMCov;

  SET Lpol   = -1;
  SET Lcou   = -1;
  SET Lyea   = -1;
  SET Lsou   = -1;
  SET Lfue   = -1;
  SET LENDMY = -1;

  read_loop: LOOP
    FETCH curIMCov INTO Cpol, Ccou, Cyea, Csou, Cfue, CBEGMY, CENDMY, CuseIMyn;

 -- select              Cpol, Ccou, Cyea, Csou, Cfue, CBEGMY, CENDMY, CuseIMyn;

    IF done THEN LEAVE read_loop; END IF;

    if CuseIMyn = 'Y' then

        set rows_processed = rows_processed + 1;


        -- Order Error:  (regardless of sameSet or not)
--      if CBEGMY > CENDMY or mod(rows_processed,5)=0 then  -- to create 'testing' errors
        if CBEGMY > CENDMY then
          Set reason = 'CbegModelYearId>CendModelYearId,';
          INSERT INTO errImCoverage values ( Cpol, Ccou, Cyea, Csou, Cfue, LENDMY, CBEGMY, CENDMY, CuseIMyn, reason );
                           else

          if Cpol=Lpol and Ccou=Lcou and Cyea=Lyea and Csou=Lsou and Cfue=Lfue
                               then  -- in set:

            -- overlap error from consecutive rows
            IF CBEGMY<=LENDMY      then  -- overlap error:
            Set reason = 'CBEGMY<=LENDMY (Overlap)';
            INSERT INTO errImCoverage values ( Cpol, Ccou, Cyea, Csou, Cfue, LENDMY, CBEGMY, CENDMY, CuseIMyn, reason );

            -- Gap from BEGMY to LENDMY > 1
            elseif CBEGMY>LENDMY+1 then  -- gap error:
            Set Reason = 'CBEGMY>LENDMY+1 (GAP)';
            INSERT INTO errImCoverage values ( Cpol, Ccou, Cyea, Csou, Cfue, LENDMY, CBEGMY, CENDMY, CuseIMyn, reason );

                                   end if;  -- end of overlap & gap checking

                               else  -- not in set:

                                 SET Lpol   =   Cpol;
                                 SET Lcou   =   Ccou;
                                 SET Lyea   =   Cyea;
                                 SET Lsou   =   Csou;
                                 SET Lfue   =   Cfue;

                                end if;  -- end of set

                              set LENDMY = CENDMY;

                           end if;  -- end of order check

                      else     -- CuseIMyn check

                        set rows_skipped = rows_skipped + 1;

                      end if;  -- CuseIMyn check

             END LOOP;

  CLOSE curIMCov;

  select 'rows processed:  ', rows_processed;
  select 'rows skipped:    ', rows_skipped;

END;
$$
-- --------------------------------------------------------------------------

-- 1 County DB Checker 3746 lines (start)

select '  .. QA checking CDB';



CREATE TABLE
   County_Database_Checks
 (
   countyID          int(11),
   status            char(20),
   tableName         char(100),
   checkNumber       smallint(6),
   testDescription   char(250),
   testValue         double,
   count             int(11),
   dataBaseName      char(100),
   dayID             smallint(6),
   fuelFormulationID smallint(6),
   fuelTypeId        smallint(6),
   fuelSubtypeID     smallint(6),
   fuelYearID        smallint(6),
   hourDayID         smallint(6),
   hourID            smallint(6),
   HPMSVtypeID       smallint(6),
   monthGroupID      smallint(6),
   monthID           smallint(6),
   roadTypeID        smallint(6),
   sourceTypeID      smallint(6),
   stateID           smallint(6),
   yearID            smallint(6),
   zoneID            int(11),
   msgType           char(50),
   msgDate           date,
   msgTime           time,
   version           char(8),
   sumKeyID          int(11),
   sumKeyDescription char(50)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;



Insert into County_Database_Checks set tableName = 'auditLog';
Insert into County_Database_Checks set tableName = 'avft';
Insert into County_Database_Checks set tableName = 'avgSpeedDistribution';
Insert into County_Database_Checks set tableName = 'county';
Insert into County_Database_Checks set tableName = 'countyYear';

Insert into County_Database_Checks set tableName = 'dayVmtFraction';
Insert into County_Database_Checks set tableName = 'EmissionRateByAge';
Insert into County_Database_Checks set tableName = 'fuelFormulation';
Insert into County_Database_Checks set tableName = 'fuelSupply';
Insert into County_Database_Checks set tableName = 'fuelSupplyYear';

Insert into County_Database_Checks set tableName = 'hourVmtFraction';
Insert into County_Database_Checks set tableName = 'hpmSvTypeYear';
Insert into County_Database_Checks set tableName = 'imCoverage';
Insert into County_Database_Checks set tableName = 'monthVmtFraction';
Insert into County_Database_Checks set tableName = 'roadType';

Insert into County_Database_Checks set tableName = 'roadTypeDistribution';
-- Insert into County_Database_Checks set tableName = 'SCCroadTypeDistribution';
Insert into County_Database_Checks set tableName = 'sourceTypeAgeDistribution';
Insert into County_Database_Checks set tableName = 'sourceTypeYear';
Insert into County_Database_Checks set tableName = 'state';

Insert into County_Database_Checks set tableName = 'year';
Insert into County_Database_Checks set tableName = 'zoneMonthHour';
Insert into County_Database_Checks set tableName = 'zone';
Insert into County_Database_Checks set tableName = 'zoneRoadType';    -- fixed, exactly 24 rows


Update      County_Database_Checks set msgType   = 'Table Check';
Update      County_Database_Checks set msgDate   = curDate();
Update      County_Database_Checks set msgTime   = curTime();


Drop   table if exists tempB;
Create table           tempB
select distinct
       table_Name as TableName,
       table_rows
from   information_schema.TABLES
where  TABLE_SCHEMA = (select database())
  and  TABLE_ROWS >= 0
  and  Table_Name in ( 'auditlog',
                       'avft',
                       'avgSpeedDistribution',
                       'county',
                       'countyYear',

                       'dayVmtFraction',
                       'EmissionRateByAge',
                       'fuelFormulation',
                       'fuelSupply',
                       'fuelSupplyYear',

                       'hourVmtFraction',
                       'hpmSvTypeYear',
                       'imCoverage',
                       'monthVmtFraction',
                       'roadType',

                       'roadTypeDistribution',
--                       'SCCroadTypeDistribution',
                       'sourceTypeAgeDistribution',
                       'sourceTypeYear',
                       'state',

                       'year',
                       'zone',
                       'zoneMonthHour',
                       'zoneRoadType'
                     );                      -- 24 -- not fixed, up to 24 table names



Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'auditLog'
                                                                            and  b.TableName = 'auditLog';               -- update  0

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'avft'
                                                                            and  b.TableName = 'avft';                   -- update  1

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'avgSpeedDistribution'
                                                                            and  b.TableName = 'avgSpeedDistribution';   -- update  2

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'county'
                                                                            and  b.TableName = 'county';                 -- update  3

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'countyYear'
                                                                            and  b.TableName = 'countyYear';             -- update  4

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'dayVmtFraction'
                                                                            and  b.TableName = 'dayVmtFraction';         -- update  5


Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'emissionRateByAge'
                                                                            and  b.TableName = 'emissionRateByAge';      -- update  6

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'fuelFormulation'
                                                                            and  b.TableName = 'fuelFormulation';        -- update  7

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'fuelSupply'
                                                                            and  b.TableName = 'fuelSupply';             -- update  8

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'fuelSupplyYear'
                                                                            and  b.TableName = 'fuelSupplyYear';         -- update  9

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'hourVmtFraction'
                                                                            and  b.TableName = 'hourVmtFraction';        -- update  10


Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'hpmSvTypeYear'
                                                                            and  b.TableName = 'hpmSvTypeYear';          -- update  11

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'imCoverage'
                                                                            and  b.TableName = 'imCoverage';             -- update  12

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'monthVmtFraction'
                                                                            and  b.TableName = 'monthVmtFraction';       -- update  13

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'roadType'
                                                                            and  b.TableName = 'roadType';               -- update  14

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'roadTypeDistribution'
                                                                            and  b.TableName = 'roadTypeDistribution';   -- update  15


-- Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'SCCroadTypeDistribution'
--                                                                            and  b.TableName = 'SCCroadTypeDistribution';   -- update  16

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'sourceTypeAgeDistribution'
                                                                            and  b.TableName = 'sourceTypeAgeDistribution'; -- update  17

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'sourceTypeYear'
                                                                            and  b.TableName = 'sourceTypeYear';            -- update  18

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'state'
                                                                            and  b.TableName = 'state';         -- update  19

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'year'
                                                                            and  b.TableName = 'year';          -- update  20

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'zone'
                                                                            and  b.TableName = 'zone';          -- update  21

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'zoneMonthHour'
                                                                            and  b.TableName = 'zoneMonthHour'; -- update  22

Update County_Database_Checks as a, tempb as b set a.count = b.table_rows Where  a.tableName = 'zoneRoadType'
                                                                            and  b.TableName = 'zoneRoadType';  -- update  23

Update County_Database_Checks set testDescription = 'Present'       where count is not null;
Update County_Database_Checks set testDescription = 'Table Missing' where count is     null;

Update County_Database_Checks set status          = 'ERROR'         where count is     null; 


Delete from County_Database_Checks where count is not null;


-- ----------------------------------------------------------------------------------------
-- Create missing tables to keep error-checking code from crashing-------------------------
-- ----------------------------------------------------------------------------------------


-- auditlog
CREATE TABLE IF NOT EXISTS `auditlog` (
  `whenHappened`     datetime          NOT NULL,
  `importerName`     varchar(100)      NOT NULL,
  `briefDescription` varchar(100)  DEFAULT NULL,
  `fullDescription`  varchar(4096) DEFAULT NULL,
  KEY `logByDate`     (`whenHappened`),
  KEY `logByImporter` (`importerName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- avft
CREATE TABLE IF NOT EXISTS `avft` (
  `sourceTypeID` smallint(6) NOT NULL,
  `modelYearID` smallint(6) NOT NULL,
  `fuelTypeID` smallint(6) NOT NULL,
  `engTechID` smallint(6) NOT NULL,
  `fuelEngFraction` double NOT NULL,
  PRIMARY KEY (`sourceTypeID`,`modelYearID`,`fuelTypeID`,`engTechID`),
  KEY `sourceTypeID` (`sourceTypeID`),
  KEY `modelYearID` (`modelYearID`),
  KEY `fuelTypeID` (`fuelTypeID`),
  KEY `engTechID` (`engTechID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- avgspeeddistribution
CREATE TABLE IF NOT EXISTS `avgspeeddistribution` (
  `sourceTypeID` smallint(6) NOT NULL DEFAULT '0',
  `roadTypeID` smallint(6) NOT NULL DEFAULT '0',
  `hourDayID` smallint(6) NOT NULL DEFAULT '0',
  `avgSpeedBinID` smallint(6) NOT NULL DEFAULT '0',
  `avgSpeedFraction` float DEFAULT NULL,
  PRIMARY KEY (`avgSpeedBinID`,`hourDayID`,`roadTypeID`,`sourceTypeID`),
  KEY `sourceTypeID` (`sourceTypeID`),
  KEY `roadTypeID` (`roadTypeID`),
  KEY `hourDayID` (`hourDayID`),
  KEY `avgSpeedBinID` (`avgSpeedBinID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- county
CREATE TABLE IF NOT EXISTS `county` (
  `countyID` int(11) NOT NULL DEFAULT '0',
  `stateID` smallint(6) NOT NULL DEFAULT '0',
  `countyName` char(50) DEFAULT NULL,
  `altitude` char(1) DEFAULT NULL,
  `GPAFract` float DEFAULT NULL,
  `barometricPressure` float DEFAULT NULL,
  `barometricPressureCV` float DEFAULT NULL,
  PRIMARY KEY (`countyID`),
  KEY `countyID` (`countyID`,`stateID`),
  KEY `stateID` (`stateID`,`countyID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- countyyear
CREATE TABLE IF NOT EXISTS `countyyear` (
  `countyID` int(11) NOT NULL DEFAULT '0',
  `yearID` smallint(6) NOT NULL DEFAULT '0',
  `refuelingVaporProgramAdjust` float NOT NULL DEFAULT '0',
  `refuelingSpillProgramAdjust` float NOT NULL DEFAULT '0',
  PRIMARY KEY (`countyID`,`yearID`),
  KEY `countyID` (`countyID`),
  KEY `yearID` (`yearID`),
  KEY `yearID_2` (`yearID`,`countyID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- dayvmtfraction
CREATE TABLE IF NOT EXISTS `dayvmtfraction` (
  `sourceTypeID` smallint(6) NOT NULL DEFAULT '0',
  `monthID` smallint(6) NOT NULL DEFAULT '0',
  `roadTypeID` smallint(6) NOT NULL DEFAULT '0',
  `dayID` smallint(6) NOT NULL DEFAULT '0',
  `dayVMTFraction` float DEFAULT NULL,
  PRIMARY KEY (`dayID`,`monthID`,`roadTypeID`,`sourceTypeID`),
  KEY `sourceTypeID` (`sourceTypeID`),
  KEY `monthID` (`monthID`),
  KEY `roadTypeID` (`roadTypeID`),
  KEY `dayID` (`dayID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- emissionratebyage
CREATE TABLE IF NOT EXISTS `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`),
  KEY `sourceBinID` (`sourceBinID`),
  KEY `opModeID` (`opModeID`),
  KEY `ageGroupID` (`ageGroupID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


-- fuelformulation
CREATE TABLE IF NOT EXISTS `fuelformulation` (
  `fuelFormulationID` smallint(6) NOT NULL DEFAULT '0',
  `fuelSubtypeID` smallint(6) NOT NULL DEFAULT '0',
  `RVP` float DEFAULT NULL,
  `sulfurLevel` float NOT NULL DEFAULT '30',
  `ETOHVolume` float DEFAULT NULL,
  `MTBEVolume` float DEFAULT NULL,
  `ETBEVolume` float DEFAULT NULL,
  `TAMEVolume` float DEFAULT NULL,
  `aromaticContent` float DEFAULT NULL,
  `olefinContent` float DEFAULT NULL,
  `benzeneContent` float DEFAULT NULL,
  `e200` float DEFAULT NULL,
  `e300` float DEFAULT NULL,
  `volToWtPercentOxy` float DEFAULT NULL,
  `BioDieselEsterVolume` float DEFAULT NULL,
  `CetaneIndex` float DEFAULT NULL,
  `PAHContent` float DEFAULT NULL,
  `T50` float DEFAULT NULL,
  `T90` float DEFAULT NULL,
  PRIMARY KEY (`fuelFormulationID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- fuelsupply
CREATE TABLE IF NOT EXISTS `fuelsupply` (
-- commented out following line 12-01-15
-- `countyID` int(11) NOT NULL DEFAULT '0',
-- added following line 12-01-15
  `fuelregionID` int(11) NOT NULL DEFAULT '0',
  `fuelYearID` smallint(6) NOT NULL DEFAULT '0',
  `monthGroupID` smallint(6) NOT NULL DEFAULT '0',
  `fuelFormulationID` smallint(6) NOT NULL DEFAULT '0',
  `marketShare` float DEFAULT NULL,
  `marketShareCV` float DEFAULT NULL,
-- commented out following line 12-01-15
-- PRIMARY KEY (`countyID`,`fuelFormulationID`,`monthGroupID`,`fuelYearID`),
-- added following line 12-01-15
  PRIMARY KEY (`fuelregionID`,`fuelFormulationID`,`monthGroupID`,`fuelYearID`),
-- commented out following line 12-01-15
-- KEY `countyID` (`countyID`),
-- added following line 12-01-15
  KEY `fuelregionID` (`fuelregionID`),
  KEY `yearID` (`fuelYearID`),
  KEY `monthGroupID` (`monthGroupID`),
  KEY `fuelSubtypeID` (`fuelFormulationID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- fuelsupplyyear
CREATE TABLE IF NOT EXISTS `fuelsupplyyear` (
  `fuelYearID` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`fuelYearID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- hourvmtfraction
CREATE TABLE IF NOT EXISTS `hourvmtfraction` (
  `sourceTypeID` smallint(6) NOT NULL DEFAULT '0',
  `roadTypeID` smallint(6) NOT NULL DEFAULT '0',
  `dayID` smallint(6) NOT NULL DEFAULT '0',
  `hourID` smallint(6) NOT NULL DEFAULT '0',
  `hourVMTFraction` float DEFAULT NULL,
  PRIMARY KEY (`dayID`,`hourID`,`roadTypeID`,`sourceTypeID`),
  KEY `sourceTypeID` (`sourceTypeID`),
  KEY `roadTypeID` (`roadTypeID`),
  KEY `dayID` (`dayID`),
  KEY `hourID` (`hourID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- hpmsvtypeyear
CREATE TABLE IF NOT EXISTS `hpmsvtypeyear` (
  `HPMSVtypeID` smallint(6) NOT NULL DEFAULT '0',
  `yearID` smallint(6) NOT NULL DEFAULT '0',
  `VMTGrowthFactor` float DEFAULT NULL,
  `HPMSBaseYearVMT` float DEFAULT NULL,
  `baseYearOffNetVMT` float DEFAULT NULL,
  PRIMARY KEY (`HPMSVtypeID`,`yearID`),
  KEY `HPMSVtypeID` (`HPMSVtypeID`),
  KEY `yearID` (`yearID`),
  KEY `yearID_2` (`yearID`,`HPMSVtypeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- imcoverage
CREATE TABLE IF NOT EXISTS `imcoverage` (
  `polProcessID`     smallint(6)     NOT NULL DEFAULT '0',
  `stateID`          smallint(6)     NOT NULL DEFAULT '0',
  `countyID`         int(11)         NOT NULL DEFAULT '0',
  `yearID`           smallint(6)     NOT NULL DEFAULT '0',
  `sourceTypeID`     smallint(6)     NOT NULL DEFAULT '0',
  `fuelTypeID`       smallint(6)     NOT NULL DEFAULT '0',
  `IMProgramID`      smallint(6)     NOT NULL DEFAULT '0',
  `begModelYearID`   smallint(6)     NOT NULL DEFAULT '0',
  `endModelYearID`   smallint(6)     NOT NULL DEFAULT '0',
  `inspectFreq`      smallint(6) DEFAULT NULL,
  `testStandardsID`  smallint(6)     NOT NULL DEFAULT '0',
  `useIMyn`          char(1)         NOT NULL DEFAULT 'Y',
  `complianceFactor` float       DEFAULT NULL,
  PRIMARY KEY (`polProcessID`,`stateID`,`countyID`,`yearID`,`sourceTypeID`,`fuelTypeID`,`IMProgramID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- monthvmtfraction
CREATE TABLE IF NOT EXISTS `monthvmtfraction` (
  `sourceTypeID` smallint(6) NOT NULL DEFAULT '0',
--   `isLeapYear` char(1) NOT NULL DEFAULT '',  -- This is old MOVES2010 format.
  `monthID` smallint(6) NOT NULL DEFAULT '0',
  `monthVMTFraction` float DEFAULT NULL,
--  PRIMARY KEY (`isLeapYear`,`monthID`,`sourceTypeID`),
  PRIMARY KEY (`monthID`,`sourceTypeID`),
  KEY `sourceTypeID` (`sourceTypeID`),
--  KEY `isLeapYear` (`isLeapYear`),
  KEY `monthID` (`monthID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- roadtype
CREATE TABLE IF NOT EXISTS `roadtype` (
  `roadTypeID` smallint(6) NOT NULL DEFAULT '0',
  `roadDesc` char(50) DEFAULT NULL,
  `rampFraction` float DEFAULT NULL,
  PRIMARY KEY (`roadTypeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- roadtypedistribution
CREATE TABLE IF NOT EXISTS `roadtypedistribution` (
  `sourceTypeID` smallint(6) NOT NULL DEFAULT '0',
  `roadTypeID` smallint(6) NOT NULL DEFAULT '0',
  `roadTypeVMTFraction` float DEFAULT NULL,
  PRIMARY KEY (`roadTypeID`,`sourceTypeID`),
  KEY `sourceTypeID` (`sourceTypeID`),
  KEY `roadTypeID` (`roadTypeID`),
  KEY `roadTypeID_2` (`roadTypeID`,`sourceTypeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- sccroadtypedistribution
-- CREATE TABLE IF NOT EXISTS `sccroadtypedistribution` (
--  `roadTypeID` smallint(6) NOT NULL DEFAULT '0',
--  `zoneID` int(11) NOT NULL DEFAULT '0',
--  `SCCRoadTypeID` smallint(6) NOT NULL DEFAULT '0',
--  `SCCRoadTypeFraction` float DEFAULT NULL,
--  `SCCRoadTypeFractionCV` float DEFAULT NULL,
--  PRIMARY KEY (`roadTypeID`,`SCCRoadTypeID`,`zoneID`),
--  KEY `roadTypeID` (`roadTypeID`,`zoneID`,`SCCRoadTypeID`)
-- ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- sourcetypeagedistribution
CREATE TABLE IF NOT EXISTS `sourcetypeagedistribution` (
  `sourceTypeID` smallint(6) NOT NULL DEFAULT '0',
  `yearID` smallint(6) NOT NULL DEFAULT '0',
  `ageID` smallint(6) NOT NULL DEFAULT '0',
  `ageFraction` float DEFAULT NULL,
  PRIMARY KEY (`ageID`,`sourceTypeID`,`yearID`),
  KEY `sourceTypeID` (`sourceTypeID`),
  KEY `yearID` (`yearID`),
  KEY `ageID` (`ageID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- sourcetypeyear
CREATE TABLE IF NOT EXISTS `sourcetypeyear` (
  `yearID` smallint(6) NOT NULL DEFAULT '0',
  `sourceTypeID` smallint(6) NOT NULL DEFAULT '0',
  `salesGrowthFactor` float DEFAULT NULL,
  `sourceTypePopulation` float DEFAULT NULL,
  `migrationrate` float DEFAULT NULL,
  PRIMARY KEY (`sourceTypeID`,`yearID`),
  KEY `yearID` (`yearID`),
  KEY `sourceTypeID` (`sourceTypeID`),
  KEY `sourceTypeID_2` (`sourceTypeID`,`yearID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- state
CREATE TABLE IF NOT EXISTS `state` (
  `stateID` smallint(6) NOT NULL DEFAULT '0',
  `stateName` char(25) DEFAULT NULL,
  `stateAbbr` char(2) DEFAULT NULL,
  PRIMARY KEY (`stateID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- year
CREATE TABLE IF NOT EXISTS `year` (
  `yearID` smallint(6) NOT NULL DEFAULT '0',
  `isBaseYear` char(1) DEFAULT NULL,
  `fuelYearID` smallint(6) NOT NULL DEFAULT '0',
  PRIMARY KEY (`yearID`),
  KEY `isBaseYear` (`isBaseYear`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- zone
CREATE TABLE IF NOT EXISTS `zone` (
  `zoneID` int(11) NOT NULL DEFAULT '0',
  `countyID` int(11) NOT NULL DEFAULT '0',
  `startAllocFactor` float DEFAULT NULL,
  `idleAllocFactor` float DEFAULT NULL,
  `SHPAllocFactor` float DEFAULT NULL,
  PRIMARY KEY (`zoneID`),
  KEY `countyID` (`countyID`),
  KEY `zoneID` (`zoneID`,`countyID`),
  KEY `countyID_2` (`countyID`,`zoneID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- zonemonthhour
CREATE TABLE IF NOT EXISTS `zonemonthhour` (
  `monthID` smallint(6) NOT NULL DEFAULT '0',
  `zoneID` int(11) NOT NULL DEFAULT '0',
  `hourID` smallint(6) NOT NULL DEFAULT '0',
  `temperature` float DEFAULT NULL,
  `temperatureCV` float DEFAULT NULL,
  `relHumidity` float DEFAULT NULL,
  `heatIndex` float DEFAULT NULL,
  `specificHumidity` float DEFAULT NULL,
  `relativeHumidityCV` float DEFAULT NULL,
  PRIMARY KEY (`hourID`,`monthID`,`zoneID`),
  KEY `monthID` (`monthID`),
  KEY `zoneID` (`zoneID`),
  KEY `hourID` (`hourID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;

-- zoneroadtype
CREATE TABLE IF NOT EXISTS `zoneroadtype` (
  `zoneID` int(11) NOT NULL DEFAULT '0',
  `roadTypeID` smallint(6) NOT NULL DEFAULT '0',
  `SHOAllocFactor` float DEFAULT NULL,
  PRIMARY KEY (`roadTypeID`,`zoneID`),
  KEY `roadTypeID` (`roadTypeID`,`zoneID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;


-- ----------------------------------------------------------------------------------------
-- Data Checking: ------------------------------------------------------------------------
-- ----------------------------------------------------------------------------------------


Insert into   County_Database_Checks ( TableName,  TestDescription )
       values
                                     ("auditlog", "Table Check:");

--       check no. 001  -- no check
--       check no. 002  -- no check
--       check no. 003  -- no check
--       check no. 004  -- no check


--       Table  Check: avft
Insert into   County_Database_Checks ( TableName, TestDescription )
       values
                           ("avft",     "Table Check:");

--       check no. 005
Drop     table if exists tempA;
Create   table           tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     avft
Group by sourceTypeId2;


Update tempA as a set aMatch='yes' where (Select m.sourceTypeId
                                          From   movesdb20151028.sourceUseType as m
                                          Where  a.sourceTypeId2 = m.sourceTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "avft"        as tableName,
         5,
        "sourceTypeId" as testDescription,
         sourceTypeId2 as testValue,
         n             as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 006
Drop     table if exists tempA;
Create   table           tempA
Select   modelYearId  as modelYearId2,
         'no '        as aMatch,
         count(*)     as n
From     avft
Group by modelYearId2;

Update tempA as a set aMatch='yes' where (Select m.modelYearId
                                          From   movesdb20151028.modelYear as m
                                          Where  a.modelYearId2 = m.modelYearId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "avft"       as tableName,
         6,
        "modelYearId" as testDescription,
         modelYearId2 as testValue,
         n            as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 007
Drop     table if exists tempA;
Create   table           tempA
Select   fuelTypeId  as fuelTypeId2,
         'no '       as aMatch,
         count(*)    as n
From     avft
Group by fuelTypeId2;

Update tempA as a set aMatch='yes' where (Select min(m.fuelTypeId)
                                          From   movesdb20151028.fuelSubType as m
                                          Where  a.fuelTypeId2 = m.fuelTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "avft"      as tableName,
         7,
        "fuelTypeId" as testDescription,
         fuelTypeId2 as testValue,
         n           as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 008
Drop     table if exists tempA;
Create   table           tempA
Select   engTechId  as engTechId2,
         'no '      as aMatch,
         count(*)   as n
From     avft
Group by engTechId2;


Update tempA as a set aMatch='yes' where (Select engTechId
                                          From   movesdb20151028.engineTech as m
                                          Where  a.engTechId2 = m.engTechId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "avft"     as tableName,
         8,
        "engTechId" as testDescription,
         engTechId2 as testValue,
         n          as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 009
Drop     table if exists tempA;
Create   table           tempA
Select   fuelEngFraction as fuelEngFraction2,
         'no '           as aMatch,
         count(*)        as n
From     avft
Group by fuelEngFraction2;


Update tempA as a set aMatch='yes' where fuelEngFraction2 >= 0.0 and fuelEngFraction2 <= 1.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "avft"           as tableName,
         9,
        "fuelEngFraction" as testDescription,
         fuelEngFraction2 as testValue,
         n                as count
From     tempA
Where    aMatch <> 'yes';


--       Table  Check: avgspeeddistribution
Insert into   County_Database_Checks ( TableName,              TestDescription )
       values
                           ("avgspeeddistribution", "Table Check:");

--       check no. 010
Drop     table if exists tempA;
Create   table           tempA
Select   avgSpeedBinId   as avgSpeedBinId2,   -- '+1' for testing
         'no '           as aMatch,
         count(*)        as n
From     avgSpeedDistribution
Group by avgSpeedBinId2;

Update   tempA as a set aMatch='yes' where (Select m.avgSpeedBinId
                                            From   movesdb20151028.avgSpeedBin as m
                                            Where  a.avgSpeedBinId2 = m.avgSpeedBinId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "avgSpeedDistribution"    as tableName,
         10,
         "avgSpeedBinId Not Valid" as testDescription,
         avgSpeedBinId2            as testValue,
         n                         as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 011
Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
           sourceTypeId,
           roadTypeId,
           hourDayId )

Select  "avgspeeddistribution"           as tableName,
         11                              as checkNumber,
        "sum of avgSpeedFraction <> 1.0" as testDescription,
         sum(avgSpeedFraction)           as testValue,
           sourceTypeId,
           roadTypeId,
           hourDayId

From     avgspeeddistribution
Group by sourceTypeId,
         roadTypeId,
         hourDayId
Having   testValue <0.999 or testValue >1.001;


--       check no. 012
Drop     table if exists tempA;
Create   table           tempA
Select   hourDayId       as hourDayId2,
         'no '           as aMatch,
         count(*)        as n
From     avgSpeedDistribution
Group by hourDayId2;

Update tempA as a set aMatch='yes' where (Select m.hourDayId
                                          From   movesdb20151028.hourDay as m
                                          Where  a.hourDayId2 = m.hourDayId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "avgSpeedDistribution" as tableName,
         12,
         "hourDayId"            as testDescription,
         hourDayId2             as testValue,
         n                      as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 013
Drop     table if exists tempA;
Create   table           tempA
Select   roadTypeId      as roadTypeId2,
         'no '           as aMatch,
         count(*)        as n
From     avgSpeedDistribution
Group by roadTypeId2;

Update tempA as a set aMatch='yes' where (Select m.roadTypeId
                                          From   movesdb20151028.roadType as m
                                          Where  a.roadTypeId2 = m.roadTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "avgSpeedDistribution" as tableName,
         13,
         "roadTypeId"           as testDescription,
         roadTypeId2            as testValue,
         n                      as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 014
Drop     table if exists tempA;
Create   table           tempA
Select   sourceTypeId    as sourceTypeId2,
         'no '           as aMatch,
         count(*)        as n
From     avgSpeedDistribution
Group by sourceTypeId2;

Update tempA as a set aMatch='yes' where (Select m.sourceTypeId
                                          From   movesdb20151028.sourceUseType as m
                                          Where  a.sourceTypeId2 = m.sourceTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "avgSpeedDistribution" as tableName,
         14,
         "sourceTypeTypeId"     as testDescription,
         sourceTypeId2          as testValue,
         n                      as count
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,  TestDescription )
       values
                           ("county1",   "Table Check:");

--       check no. 015
Drop     table if exists tempA;
Create   table           tempA
Select   countyId    as countyId2,
         'no '       as aMatch,
         count(*)    as n
From     county
Group by countyId2;

Update tempA as a set aMatch='yes' where (Select m.countyId
                                          From   movesdb20151028.county as m
                                          Where  a.countyId2 = m.countyId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "county"   as tableName,
         15,
         "countyId" as testDescription,
         countyId2  as testValue,
         n          as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 016  -- no check


--       check no. 017
Drop     table if exists tempA;
Create   table           tempA
Select   altitude     as altitude2,
         'no '        as aMatch,
         count(*)     as n
From     county
Group by altitude2;

Update tempA as a set aMatch='yes' where altitude2 in ('L','H');

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         -- testValue,
         count  )
Select   "county"              as tableName,
         17,
         "altitude not L or H" as testDescription,
         -- altitude2          as testValue,
         n                     as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 018
Drop     table if exists tempA;
Create   table           tempA
Select   GPAFract     as GPAFract2,
         'no '        as aMatch,
         count(*)     as n
From     county
Group by GPAFract2;

Update tempA set aMatch='yes' where GPAFract2>=0.0 and GPAFract2<=1.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "county"       as tableName,
         18,
         "GPACFract"    as testDescription,
         GPAFract2      as testValue,
         n              as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 019
Drop     table if exists tempA;
Create   table           tempA
Select   barometricPressure as barPre,
         'no '              as aMatch,
         count(*)           as n
From     county
Group by barPre;


Update tempA as a set aMatch='yes' where BarPre>=20.0 and BarPre<=33.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "county"             as tableName,
         19,
         "barometricPressure" as testDescription,
         BarPre               as testValue,
         n                    as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 020  No test

--       check no. 021
Drop     table if exists tempA;
Create   table           tempA
Select   stateId      as stateId2,
         'no '        as aMatch,
         count(*)     as n
From     county
Group by stateId2;

Update tempA as a set aMatch='yes' where (Select m.stateId
                                          From   movesdb20151028.state as m
                                          Where  a.stateId2 = m.stateId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "county"            as tableName,
         21,
         "stateId not valid" as testDescription,
         stateId2            as testValue,
         n                   as count
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,        TestDescription )
       values
                           ("dayVmtFraction", "Table Check:");


--       check no. 022
Drop     table if exists tempA;
Create   table           tempA
Select   dayId        as dayId2,
         'no '        as aMatch,
         count(*)     as n
From     dayVmtFraction
Group by dayId2;


Update tempA as a set aMatch='yes' where (Select m.dayId
                                          From   movesdb20151028.dayOfAnyWeek as m
                                          Where  a.dayId2 = m.dayId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "dayVmtFraction" as tableName,
         22,
         "dayId"          as testDescription,
         dayId2           as testValue,
         n                as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 023
Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
           sourceTypeId,
           monthId,
           roadTypeId )

Select  "dayVmtFraction"               as tableName,
         23                            as checkNumber,
        "sum of dayVMTFraction <> 1.0" as testDescription,
         sum(dayVMTFraction)           as testValue,
           sourceTypeId,
           monthId,
           roadTypeId

From     dayVmtFraction
Group by sourceTypeId,
         monthId,
         roadTypeId
Having   testValue <0.999 or testValue >1.001;


--       check no. 024
Drop     table if exists tempA;
Create   table           tempA
Select   monthId      as monthId2,
         'no '        as aMatch,
         count(*)     as n
From     dayVmtFraction
Group by monthId2;


Update tempA as a set aMatch='yes' where (Select m.monthId
                                          From   movesdb20151028.monthOfAnyYear as m
                                          Where  a.monthId2 = m.monthId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "dayVmtFraction" as tableName,
         24,
         "monthId"        as testDescription,
         monthId2         as testValue,
         n                as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 025
Drop     table if exists tempA;
Create   table           tempA
Select   roadTypeId   as roadTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     movesdb20151028.dayVmtFraction
Group by roadTypeId2;


Update tempA as a set aMatch='yes' where (Select m.roadTypeId
                                          From   movesdb20151028.roadType as m
                                          Where  a.roadTypeId2 = m.roadTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "dayVmtFraction" as tableName,
         25,
         "roadTypeId"     as testDescription,
         roadTypeId2      as testValue,
         n                as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 026
Drop     table if exists tempA;
Create   table           tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     dayVmtFraction
Group by sourceTypeId2;


Update tempA as a set aMatch='yes' where (Select m.sourceTypeId
                                          From   movesdb20151028.sourceUseType as m
                                          Where  a.sourceTypeId2 = m.sourceTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "dayVmtFraction" as tableName,
         26,
         "sourceTypeId"   as testDescription,
         sourceTypeId2    as testValue,
         n                as count
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,         TestDescription )
       values
                           ("fuelFormulation", "Table Check:");

--       check no. 027  no check

--       check no. 028
Drop     table if exists tempA;
Create   table           tempA
Select   fuelSubTypeId as fuelSubTypeId2,
         'no '         as aMatch,
         count(*)      as n
From     fuelFormulation
Group by fuelSubTypeId2;


Update tempA as a set aMatch='yes' where (Select m.fuelSubTypeId
                                          From   movesdb20151028.fuelSubType as m
                                          Where  a.fuelSubTypeId2 = m.fuelSubTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         28,
         "fuelSubTypeId"   as testDescription,
         fuelSubTypeId2    as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 029
Drop     table if exists tempA;
Create   table           tempA
Select   RVP           as RVP2,
         fuelSubTypeId as fuelSubType2,
         'no '         as aMatch,
         count(*)      as n
From     fuelFormulation
Group by RVP2;

Update tempA as a set aMatch='yes' where RVP2>=5.0 and RVP2<=20.0 and fuelSubType2     in (10,11,12,13,14,15);
Update tempA as a set aMatch='yes' where                              fuelSubType2 not in (10,11,12,13,14,15);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         29,
         "RVP"             as testDescription,
         RVP2              as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 030
Drop     table if exists tempA;
Create   table           tempA
Select   sulfurLevel   as sulfurLevel2,
         'no '         as aMatch,
         count(*)      as n
From     fuelFormulation
Group by sulfurLevel2;

Update tempA as a set aMatch='yes' where sulfurLevel2>=0.0 and sulfurLevel2<=5000.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         30,
         "sulfurLevel"     as testDescription,
         sulfurLevel2      as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 031
Drop     table if exists tempA;
Create   table           tempA
Select   ETOHVolume    as ETOHVolume2,
         'no '         as aMatch,
         count(*)      as n,
         fuelsubtypeid as fuelsubtype2
From     fuelFormulation
Where    fuelsubtypeid in (10,11,12,13,14,15)
  and    fuelformulationid >=100
Group by ETOHVolume2;

Update tempA as a set aMatch='yes' where ETOHVolume2>=0.0 and ETOHVolume2<=100.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         31,
         "ETOHVolume"      as testDescription,
         ETOHVolume2       as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 032
Drop     table if exists tempA;
Create   table           tempA
Select   MTBEVolume    as MTBEVolume2,
         'no '         as aMatch,
         count(*)      as n,
         fuelsubtypeid as fuelsubtype2
From     fuelFormulation
Where    fuelsubtypeid in (10,11,12,13,14,15)
  and    fuelformulationid >=100
Group by MTBEVolume2;


Update tempA as a set aMatch='yes' where MTBEVolume2>=0.0 and MTBEVolume2<=100.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         32,
         "MTBEVolume"      as testDescription,
         MTBEVolume2       as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 033
Drop     table if exists tempA;
Create   table           tempA
Select   fuelFormulationID,
         ETBEVolume    as ETBEVolume2,
         'no '         as aMatch,
         count(*)      as n,
         fuelsubtypeid as fuelsubtype2
From     fuelFormulation
Where    fuelsubtypeid in (10,11,12,13,14,15)
  and    fuelformulationid >=100
Group by ETBEVolume2;


Update tempA as a set aMatch='yes' where ETBEVolume2>=0.0 and ETBEVolume2<=100.0;

Insert into County_Database_Checks
       ( TableName,
         fuelFormulationID,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         fuelFormulationID,
         33,
         "ETBEVolume"      as testDescription,
         ETBEVolume2       as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 034
Drop     table if exists tempA;
Create   table           tempA
Select   TAMEVolume    as TAMEVolume2,
         'no '         as aMatch,
         count(*)      as n,
         fuelsubtypeid as fuelsubtype2
From     fuelFormulation
Where    fuelsubtypeid in (10,11,12,13,14,15)
  and    fuelformulationid >=100
Group by TAMEVolume2;

Update tempA as a set aMatch='yes' where TAMEVolume2>=0.0 and TAMEVolume2<=100.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         34,
         "TAMEVolume"      as testDescription,
         TAMEVolume2       as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 035
Drop     table if exists tempA;
Create   table           tempA
Select   aromaticContent as aromaticContent2,
         'no '           as aMatch,
         count(*)        as n,
         fuelsubtypeid   as fuelsubtype2
From     fuelFormulation
Where    fuelsubtypeid in (10,11,12,13,14,15)
  and    fuelformulationid >=100
Group by aromaticContent2;

Update tempA as a set aMatch='yes' where aromaticContent2>=0.0 and aromaticContent2<=55.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         35,
         "aromaticContent" as testDescription,
         aromaticContent2  as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 036
Drop     table if exists tempA;
Create   table           tempA
Select   olefinContent as olefinContent2,
         'no '           as aMatch,
         count(*)        as n,
         fuelsubtypeid   as fuelsubtype2
From     fuelFormulation
Where    fuelsubtypeid in (10,11,12,13,14,15)
  and    fuelformulationid >=100
Group by olefinContent2;

Update tempA as a set aMatch='yes' where olefinContent2>=0.0 and olefinContent2<=25.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         36,
         "olefinContent"   as testDescription,
         olefinContent2    as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 037
Drop     table if exists tempA;
Create   table           tempA
Select   benzeneContent  as benzeneContent2,
         'no '           as aMatch,
         count(*)        as n,
         fuelsubtypeid   as fuelsubtype2
From     fuelFormulation
Where    fuelsubtypeid in (10,11,12,13,14,15)
  and    fuelformulationid >=100
Group by benzeneContent2;

Update tempA as a set aMatch='yes' where benzeneContent2>=0.0 and benzeneContent2<=5.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         37,
         "benzeneContent"  as testDescription,
         benzeneContent2   as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 038
Drop     table if exists tempA;
Create   table           tempA
Select   e200  as e2002,
         'no '           as aMatch,
         count(*)        as n,
         fuelsubtypeid   as fuelsubtype2
From     fuelFormulation
Where    fuelsubtypeid in (10,11,12,13,14,15)
  and    fuelformulationid >=100
Group by e2002;


Update tempA as a set aMatch='yes' where e2002>=0.0 and e2002<=70.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         38,
         "e200"            as testDescription,
         e2002             as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 039
Drop     table if exists tempA;
Create   table           tempA
Select   e300            as e3002,
         'no '           as aMatch,
         count(*)        as n,
         fuelsubtypeid   as fuelsubtype2
From     fuelFormulation
Where    fuelsubtypeid in (10,11,12,13,14,15)
  and    fuelformulationid >=100
Group by e3002;


Update tempA as a set aMatch='yes' where e3002>=0.0 and e3002<=100.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         39,
         "e300"            as testDescription,
         e3002             as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 040
Drop     table if exists tempA;
Create   table           tempA
Select   volToWtPercentOxy as volToWtPercentOxy2,
         'no '             as aMatch,
         count(*)          as n
From     fuelFormulation
Group by volToWtPercentOxy2;


Update tempA as a set aMatch='yes' where volToWtPercentOxy2>=0.0 and volToWtPercentOxy2<=1.0
                                      or volToWtPercentOxy2 is null;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         40,
         "volToWtPercentOxy" as testDescription,
         volToWtPercentOxy2  as testValue,
         n                   as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 041  -- no check
--       check no. 042  -- no check
--       check no. 043  -- no check
--       check no. 044  -- no check
--       check no. 045  -- no check

Insert into   County_Database_Checks ( TableName,    TestDescription )
       values
                           ("fuelSupply", "Table Check:");


--       check no. 046
Drop     table if exists tempA;
Create   table           tempA
-- commented out following line 12-01-15
-- Select   countyId    as countyId2,
-- added following line 12-01-15
Select   fuelregionId    as fuelregionId2,
         'no '       as aMatch,
         count(*)    as n
From     fuelSupply
-- commented out following line 12-01-15
-- Group by countyId2;
-- added following line 12-01-15
Group by fuelregionId2;

-- Updated this line for MOVES2014 compatibility on 12/1/2015
-- Update tempA as a set aMatch='yes' where (Select m.countyId
Update tempA as a set aMatch='yes' where (Select distinct m.regionID
                                          From   movesdb20151028.regioncounty as m
                                          Where  a.fuelRegionId2 = m.regionId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelSupply" as tableName,
         46,
        "fuelRegionId"   as testDescription,
         fuelRegionId2   as testValue,
         n           as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 047
Drop     table if exists tempA;
Create   table           tempA
Select   fuelFormulationID as fuelFormulationID2,
         'no '             as aMatch,
         count(*)          as n
From     fuelSupply
Group by fuelFormulationID2;


Update tempA as a set aMatch='yes' where (Select m.fuelFormulationID
                                          From   fuelformulation as m
                                          Where  a.fuelFormulationID2 = m.fuelFormulationID);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelSupply" as tableName,
         47,
        "fuelFormulationID" as testDescription,
         fuelFormulationID2  as testValue,
         n                   as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 048
Drop     table if exists tempA;
Create   table           tempA
Select   fuelYearId as fuelYearId2,
         'no '      as aMatch,
         count(*)   as n
From     fuelSupply
Group by fuelYearId2;


Update tempA as a set aMatch='yes' where (Select m.fuelYearId
                                          From   movesdb20151028.fuelSupplyYear as m
                                          Where  a.fuelYearId2 = m.fuelYearId);

# disabled for calendar years after 2015.
update tempA set aMatch='yes' where fuelYearId2>2015;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelSupply" as tableName,
         48,
        "fuelYearId"  as testDescription,
         fuelYearId2  as testValue,
         n            as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 049
Drop     table if exists tempA;

-- gasoline fuel types:
Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
           countyId,
           fuelYearId,
           monthGroupId,
           fuelTypeId )

Select  "fuelSupply"                  as tableName,
         49                           as checkNumber,
        "sum of marketShare <> 1.0"   as testDescription,
         sum(marketShare)             as testValue,
       --    countyId,
           fuelRegionId,
           fuelYearId,
           monthGroupId,
           1 as fuelTypeId

From     fuelSupply                  as a,
         fuelFormulation             as b,
         movesdb20151028.fuelSubtype as c

Where    a.fuelFormulationId = b.fuelFormulationId
  and    b.fuelSubTypeId     = c.fuelSubtypeId
  and    c.fuelTypeId in (1,5)

-- updated for MOVES2014 compaitbility on 12/1/2015
-- Group by countyId,
Group by fuelRegionId,
         fuelYearId,
         monthGroupId
Having   testValue <0.999 or testValue >1.001;

-- Non-gasoline fuel types:
Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
           countyId,
           fuelYearId,
           monthGroupId,
           fuelTypeId )

Select  "fuelSupply"                  as tableName,
         49                           as checkNumber,
        "sum of marketShare <> 1.0"   as testDescription,
         sum(marketShare)             as testValue,
-- updated for MOVES2014
--         countyId,
           fuelRegionID,
           fuelYearId,
           monthGroupId,
           fuelTypeId

From     fuelSupply                  as a,
         fuelFormulation             as b,
         movesdb20151028.fuelSubtype as c

Where    a.fuelFormulationId = b.fuelFormulationId
  and    b.fuelSubTypeId     = c.fuelSubtypeId
  and    c.fuelTypeId not in (1,5)

-- Updated for MOVES2014
-- Group by countyId,
Group by fuelRegionId,
         fuelYearId,
         monthGroupId,
         fuelTypeId
Having   testValue <0.999 or testValue >1.001;



--       check no. 050  -- no check

--       check no. 051
Drop     table if exists tempA;
Create   table           tempA
Select   monthGroupId as monthGroupId2,
         'no '        as aMatch,
         count(*)     as n
From     fuelSupply
Group by monthGroupId2;


Update tempA as a set aMatch='yes' where (Select m.monthGroupId
                                          From   movesdb20151028.monthGroupOfAnyYear as m
                                          Where  a.monthGroupId2 = m.monthGroupId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelSupply"   as tableName,
         51,
        "monthGroupId"  as testDescription,
         monthGroupId2  as testValue,
         n              as count
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,        TestDescription )
       values
                           ("fuelSupplyYear", "Table Check:");


--       check no. 052
Drop     table if exists tempA;
Create   table           tempA
Select   fuelYearId   as fuelYearId2,
         'no '        as aMatch,
         count(*)     as n
From     fuelSupplyYear
Group by fuelYearId2;

Update tempA as a set aMatch='yes' where (Select m.fuelYearId
                                          From   movesdb20151028.fuelSupplyYear as m
                                          Where  a.fuelYearId2 = m.fuelYearId);

# disabled for calendar years after 2015.
update tempA set aMatch='yes' where fuelYearId2>2015;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select  "fuelSupplyYear" as tableName,
         52,
        "fuelYearId"     as testDescription,
         fuelYearId2     as testValue,
         n               as count               --
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,         TestDescription )
       values
                           ("hourVmtFraction", "Table Check:");


--       check no. 053
Drop     table if exists tempA;

Insert into County_Database_Checks
       (  tableName,
          checkNumber,
          testDescription,
          testValue,
            sourceTypeId,
            roadTypeId,
            dayId  )

Select   "hourVmtFraction"               as tableName,
          53                             as checkNumber,
         "sum of hourVmtFraction <> 1.0" as tesDescription,
          sum(hourVmtFraction)           as testValue,
            sourceTypeId,
            roadTypeId,
            dayId

From      hourVmtFraction
Group by  sourceTypeId,
          roadTypeId,
          dayId
Having    testValue <0.999 or testValue >1.001;


--       check no. 054
Drop     table if exists tempA;
Create   table           tempA
Select   dayId    as dayId2,
         'no '    as aMatch,
         count(*) as n
From     hourVmtFraction
Group by dayId2;


Update tempA as a set aMatch='yes' where (Select m.dayId
                                          From   movesdb20151028.dayOfAnyWeek as m
                                          Where  a.dayId2 = m.dayId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "hourVmtFraction" as tableName,
         54,
         "dayId"           as testDescription,
         dayId2            as testValue,
         n                 as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 055
Drop     table if exists tempA;
Create   table           tempA
Select   hourId    as hourId2,
         'no '     as aMatch,
         count(*)  as n
From     hourVmtFraction
Group by hourId2;


Update tempA as a set aMatch='yes' where (Select m.hourId
                                          From   movesdb20151028.hourOfAnyDay as m
                                          Where  a.hourId2 = m.hourId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select  "hourVmtFraction" as tableName,
         55,
        "hourId"          as testDescription,
         hourId2          as testValue,
         n                as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 056
Drop     table if exists tempA;
Create   table           tempA
Select   roadTypeId as roadTypeId2,
         'no '      as aMatch,
         count(*)   as n
From     hourVmtFraction
Group by roadTypeId2;


Update tempA as a set aMatch='yes' where (Select m.roadTypeId
                                          From   movesdb20151028.roadType as m
                                          Where  a.roadTypeId2 = m.roadTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select  "hourVmtFraction" as tableName,
         56,
        "roadTypeId"      as testDescription,
         roadTypeId2      as testValue,
         n                as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 057
Drop     table if exists tempA;
Create   table           tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     hourVmtFraction
Group by sourceTypeId2;


Update tempA as a set aMatch='yes' where (Select m.sourceTypeId
                                          From   movesdb20151028.sourceUseType as m
                                          Where  a.sourceTypeId2 = m.sourceTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select  "hourVmtFraction" as tableName,
         57,
        "sourceTypeId"    as testDescription,
         sourceTypeId2    as testValue,
         n                as count               --
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,       TestDescription )
       values
                           ("hpmSvTypeYear", "Table Check:");

--       check no. 058
Drop     table if exists tempA;
Create   table           tempA
Select   HPMSVtypeID  as HPMSVtypeID2,
         'no '        as aMatch,
         count(*)     as n
From     hpmSvTypeYear
Group by HPMSVtypeID2;


Update tempA as a set aMatch='yes' where (Select m.HPMSVtypeID
                                          From   movesdb20151028.hpmsvType as m
                                          Where  a.HPMSVtypeID2 = m.HPMSVtypeID);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select  "hpmSvTypeYear"   as tableName,
         58,
        "hpmSvTypeId"      as testDescription,
         HPMSVtypeId2      as testValue,
         n                 as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 059  -- no check

--       check no. 060  -- no check

--       check no. 061  -- no check

--       check no. 062
Drop     table if exists tempA;
Create   table           tempA
Select   yearId          as yearId2,
         'no '           as aMatch,
         count(*)        as n
From     hpmSvTypeYear
Group by yearId2;

Update tempA as a set aMatch='yes' where (Select m.yearId
                                          From   movesdb20151028.year as m
                                          Where  a.yearId2 = m.yearId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select  "hpmSvTypeYear"   as tableName,
         62,
        "yearId"      as testDescription,
         yearId2      as testValue,
         n            as count               --
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,    TestDescription )
       values
                           ("imCoverage", "Table Check:");

--       check no. 063
Drop     table if exists tempA;
Create   table           tempA
Select   countyId        as countyId2,
         'no '           as aMatch,
         count(*)        as n
From     imCoverage
Group by countyId2;


Update tempA as a set aMatch='yes' where (Select m.countyId
                                          From   movesdb20151028.county as m
                                          Where  a.countyId2 = m.countyId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select  "imCoverage" as tableName,
         63,
        "countyId"      as testDescription,
         countyId2      as testValue,
         n              as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 064
Drop     table if exists tempA;
Create   table           tempA
Select   fuelTypeId      as fuelTypeId2,
         'no '           as aMatch,
         count(*)        as n
From     imCoverage
Group by fuelTypeId2;


Update tempA as a set aMatch='yes' where (Select fuelTypeId
                                          From   movesdb20151028.fuelType as m
                                          Where  a.fuelTypeId2 = m.fuelTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select  "imCoverage" as tableName,
         64,
        "fuelTypeId" as testDescription,
         fuelTypeId2 as testValue,
         n           as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 065  -- no check

--       check no. 066
Drop     table if exists tempA;
Create   table           tempA
Select   polProcessId    as polProcessId2,
         'no '           as aMatch,
         count(*)        as n
From     imCoverage
Group by polProcessId2;


Update tempA as a set aMatch='yes' where (Select polProcessId
                                          From   movesdb20151028.pollutantProcessAssoc as m
                                          Where  a.polProcessId2 = m.polProcessId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select  "imCoverage" as tableName,
         66,
        "polProcessId"      as testDescription,
         polProcessId2      as testValue,
         n                  as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 069
Drop     table if exists tempA;
Create   table           tempA
Select   inspectFreq     as inspectFreq2,
         'no '           as aMatch,
         count(*)        as n
From     imCoverage
Group by inspectFreq2;


Update tempA as a set aMatch='yes' where inspectFreq2 in (1,2);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select  "imCoverage"  as tableName,
         69,
        "inspectFreq" as testDescription,
         inspectFreq2 as testValue,
         n            as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 070  -- no check

--       check no. 071
Drop     table if exists tempA;
Create   table           tempA
Select   useIMyn         as useIMyn2,
         'no '           as aMatch,
         count(*)        as n
From     imCoverage
Group by useIMyn2;


Update tempA as a set aMatch='yes' where useIMyn2 in ('y', 'n');

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
    --   testValue,
         count  )
Select   "imCoverage" as tableName,
         71,
        "useIMyn not Y or N" as testDescription,
    --   useIMyn2      as testValue,
         n         as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 072
Drop     table if exists tempA;
Create   table           tempA
Select   complianceFactor as complianceFactor2,
         'no '            as aMatch,
         count(*)         as n
From     imCoverage
Group by complianceFactor;


Update tempA as a set aMatch='yes' where complianceFactor2>=0.0 and complianceFactor2<=100.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "imCoverage" as tableName,
         72,
        "complianceFactor range" as testDescription,
         complianceFactor2       as testValue,
         n                       as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 073
Drop     table tempA;
Create   table           tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     imCoverage
Group by sourceTypeId2;



Update tempA as a set aMatch='yes' where (Select m.sourceTypeId
                                          From   movesdb20151028.sourceUseType as m
                                          Where  a.sourceTypeId2 = m.sourceTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "imCoverage"  as tableName,
         73,
        "sourceTypeId" as testDescription,
         sourceTypeId2 as testValue,
         n             as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 074
Drop     table if exists tempA;
Create   table           tempA
Select   stateId      as stateId2,
         'no '        as aMatch,
         count(*)     as n
From     imcoverage
Group by stateId2;

Update tempA as a set aMatch='yes' where (Select m.stateId
                                          From   movesdb20151028.state as m
                                          Where  a.stateId2 = m.stateId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "imCoverage"       as tableName,
         74,
         "statId not valid" as testDescription,
         stateId2           as testValue,
         n                  as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 075
Drop     table if exists tempA;
Create   table           tempA
Select   yearId          as yearId2,
         'no '           as aMatch,
         count(*)        as n
From     imcoverage
Group by yearId2;


Update tempA as a set aMatch='yes' where (Select m.yearId
                                          From   movesdb20151028.year as m
                                          Where  a.yearId2 = m.yearId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "imCoverage" as tableName,
         75,
        "yearId"      as testDescription,
         yearId2      as testValue,
         n            as count               --
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,          TestDescription )
       values
                           ("monthVmtFraction", "Table Check:");

--       check no. 076
Drop     table if exists tempA;
Create   table           tempA
-- Select   isLeapYear      as isLeapYear2,
Select   'no '           as aMatch,
         count(*)        as n
From     monthvmtfraction;
-- Group by isLeapYear2;

-- Delete this whole check - there is no more "isLeapYear" in MOVES2014.
-- Update tempA as a set aMatch='yes' where isLeapYear2 in ('y', 'n');

-- Insert into County_Database_Checks
--       ( TableName,
--         CheckNumber,
--         TestDescription,
--    --   testValue,
--         count  )
-- Select   "monthvmtfraction"     as tableName,
--         76,
--        "isLeapYear not Y or N" as testDescription,
--    --   useIMyn2               as testValue,
--         n                      as count
-- From     tempA
-- Where    aMatch <> 'yes';

-- Select * from County_Database_Checks;


--       check no. 077
Drop     table if exists tempA;
Create   table           tempA
Select   monthId      as monthId2,
         'no '        as aMatch,
         count(*)     as n
From     monthVmtFraction
Group by monthId2;


Update tempA as a set aMatch='yes' where (Select m.monthId
                                          From   movesdb20151028.monthOfAnyYear as m
                                          Where  a.monthId2 = m.monthId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "monthVmtFraction" as tableName,
         77,
        "monthId"           as testDescription,
         monthId2           as testValue,
         n                  as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 078
Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
           sourceTypeId  )

Select  "monthVmtFraction"               as tableName,
         78                              as checkNumber,
        "sum of monthVmtFraction <> 1.0" as testDescription,
         sum(monthVmtFraction)           as testValue,
           sourceTypeId

From     monthVmtFraction
Group by sourceTypeId
-- Group by sourceTypeId,
--         isLeapYear
Having   testValue <0.999 or testValue >1.001;


--       check no. 079
Drop     table tempA;
Create   table           tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     monthVmtFraction
Group by sourceTypeId2;


Update tempA as a set aMatch='yes' where (Select m.sourceTypeId
                                          From   movesdb20151028.sourceUseType as m
                                          Where  a.sourceTypeId2 = m.sourceTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "monthVmtFraction" as tableName,
         79,
         "sourceTypeId"     as testDescription,
         sourceTypeId2      as testValue,
         n                  as count
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,  TestDescription )
       values
                           ("roadType", "Table Check:");


--       check no. 080
Drop     table if exists tempA;
Create   table           tempA
Select   roadTypeId as roadTypeId2,
         'no '      as aMatch,
         count(*)   as n
From     roadType
Group by roadTypeId2;


Update tempA as a set aMatch='yes' where (Select m.roadTypeId
                                          From   movesdb20151028.roadType as m
                                          Where  a.roadTypeId2 = m.roadTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "roadType"   as tableName,
         80,
         "roadTypeId" as testDescription,
         roadTypeId2  as testValue,
         n            as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 081  -- no check

--       check no. 082
Drop     table if exists tempA;
Create   table           tempA
Select   rampFraction as rampFraction2,
         'no '        as aMatch,
         count(*)     as n
From     roadType
Group by rampFraction2;


Update tempA as a set aMatch='yes' where rampFraction2>=0.0 and rampFraction2<=1.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "roadType"     as tableName,
         82,
        "rampFraction"  as testDescription,
         rampFraction2  as testValue,
         n              as count               --
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,               TestDescription )
       values
                           ("roadTypeDistribution",  "Table Check:");


--       check no. 083
Drop     table if exists tempA;
Create   table           tempA
Select   roadTypeId as roadTypeId2,
         'no '      as aMatch,
         count(*)   as n
From     roadTypeDistribution
Group by roadTypeId2;

Update tempA as a set aMatch='yes' where (Select m.roadTypeId
                                          From   movesdb20151028.roadType as m
                                          Where  a.roadTypeId2 = m.roadTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "roadTypeDistribution" as tableName,
         83,
        "roadTypeId"            as testDescription,
         roadTypeId2            as testValue,
         n                      as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 084
Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
           sourceTypeId  )

Select  "roadTypeDistribution"              as tableName,
         84                                 as checkNumber,
        "sum of roadTypeVmtFraction <> 1.0" as testDescription,
         sum(roadTypeVmtFraction)           as testValue,
           sourceTypeId

From     roadTypeDistribution
Group by sourceTypeId
Having   testValue <0.999 or testValue >1.001;


--       check no. 085
Drop     table tempA;
Create   table tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     roadTypeDistribution
Group by sourceTypeId2;

Update tempA as a set aMatch='yes' where (Select m.sourceTypeId
                                          From   movesdb20151028.sourceUseType as m
                                          Where  a.sourceTypeId2 = m.sourceTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "roadTypeDistribution" as tableName,
         85,
        "sourceTypeId"          as testDescription,
         sourceTypeId2          as testValue,
         n                      as count
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,                 TestDescription )
       values
                           ("SCCRoadTypeDistribution", "Table Check:");
-- Delete check numbers 086, 087, 088, 089, and 205 because MOVES2014 does not use SCCRoadTypeDistribution anymore.

--       check no. 086
-- Drop     table if exists tempA;
-- Create   table           tempA
-- Select   roadTypeId as roadTypeId2,
--         'no '      as aMatch,
--         count(*)   as n
-- From     SCCRoadTypeDistribution
-- Group by roadTypeId2;

-- Update tempA as a set aMatch='yes' where (Select m.roadTypeId
--                                          From   movesdb20151028.roadType as m
--                                          Where  a.roadTypeId2 = m.roadTypeId);

-- Insert into County_Database_Checks
--       ( TableName,
--         CheckNumber,
--         TestDescription,
--         testValue,
--         count  )
-- Select   "SCCRoadTypeDistribution" as tableName,
--         86,
--        "roadTypeId"               as testDescription,
--         roadTypeId2               as testValue,
--         n                         as count
-- From     tempA
-- Where    aMatch <> 'yes';


--       check no. 087
-- Drop     table if exists tempA;
-- Create   table           tempA
-- Select   zoneId     as zoneId2,
--         'no '      as aMatch,
--         count(*)   as n
-- From     SCCRoadTypeDistribution
-- Group by zoneId2;


-- Update tempA as a set aMatch='yes' where (Select m.zoneId
--                                          From   movesdb20151028.zone as m
--                                          Where  a.zoneId2 = m.zoneId);

-- Insert into County_Database_Checks
--       ( TableName,
--         CheckNumber,
--         TestDescription,
--         testValue,
--         count  )
-- Select   "SCCRoadTypeDistribution" as tableName,
--         87,
--        "zoneId"               as zoneId,
--         zoneId2               as testValue,
--         n                     as count
-- From     tempA
-- Where    aMatch <> 'yes';


--       check no. 088
-- Drop     table if exists tempA;
-- Create   table           tempA
-- Select   SCCroadTypeId as SCCroadTypeId2,
--         'no '         as aMatch,
--         count(*)      as n
-- From     SCCRoadTypeDistribution
-- Group by SCCroadTypeId2;


-- Update tempA as a set aMatch='yes' where (Select min(m.SCCroadTypeId)
--                                          From   movesdb20151028.SCCRoadTypeDistribution as m
--                                          Where  a.SCCroadTypeId2 = m.SCCroadTypeId);

-- Insert into County_Database_Checks
--       ( TableName,
--         CheckNumber,
--         TestDescription,
--         testValue,
--         count  )
-- Select   "SCCRoadTypeDistribution" as tableName,
--         88,
--        "SCCroadTypeId"            as testDescription,
--         SCCroadTypeId2            as testValue,
--         n                         as count
-- From     tempA
-- Where    aMatch <> 'yes';


--       check no. 089
-- Insert into County_Database_Checks
--       ( tableName,
--         checkNumber,
--         testDescription,
--         testValue,
--           roadTypeId,
--           zoneId       )

-- Select  "SCCroadTypeDistribution"           as tableName,
--         89                                 as checkNumber,
--        "sum of SCCroadTypeFraction <> 1.0" as testDescription,
--        sum(SCCroadTypeFraction)           as testValue,
--           roadTypeId,
--           zoneId
--
-- From     SCCroadTypeDistribution
-- Group by roadTypeId,
--          zoneId
-- Having   testValue <0.999 or testValue >1.001;

--       check no. 090  -- no check

Insert into   County_Database_Checks ( TableName,                   TestDescription )
       values
                                     ("sourcetypeagedistribution", "Table Check:");


--       check no. 091  -- see Dave
Drop     table if exists tempA;
Create   table           tempA
Select   ageId         as ageId2,
         'no '         as aMatch,
         count(*)      as n
From     sourceTypeAgeDistribution
Group by ageId2;

update tempa                       as a,        -- djb's
       movesdb20151028.ageCategory as m         -- code
   set aMatch='yes'  where a.ageId2 = m.ageId;  --

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "sourceTypeAgeDistribution" as tableName,
         91,
        "ageId"                      as testDescription,
         ageId2                      as testValue,
         n                           as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 092
Drop     table tempA;
Create   table           tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     sourceTypeAgeDistribution
Group by sourceTypeId2;


Update tempA as a set aMatch='yes' where (Select m.sourceTypeId
                                          From   movesdb20151028.sourceUseType as m
                                          Where  a.sourceTypeId2 = m.sourceTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "sourceTypeAgeDistribution" as tableName,
         92,
        "sourceTypeId"               as testDescription,
         sourceTypeId2               as testValue,
         n                           as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 093
Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
           sourceTypeId,
           yearId       )

Select  "sourceTypeAgeDistribution"   as tableName,
         93                           as checkNumber,
        "sum of AgeFraction <> 1.0"   as testDescription,
         sum(ageFraction)             as testValue,
           sourceTypeId,
           yearId

From     sourceTypeAgeDistribution
Group by sourceTypeId,
         yearId
Having   testValue <0.999 or testValue >1.001;


--       check no. 094
Drop     table tempA;
Create   table           tempA
Select   yearId       as yearId2,
         'no '        as aMatch,
         count(*)     as n
From     sourceTypeAgeDistribution
Group by yearId2;


Update tempA as a set aMatch='yes' where (Select m.yearId
                                          From   movesdb20151028.year as m
                                          Where  a.yearId2 = m.yearId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "sourceTypeAgeDistribution" as tableName,
         94,
        "yearId"                     as testDescription,
         yearId2                     as testValue,
         n                           as count
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,        TestDescription )
       values
                           ("sourceTypeYear", "Table Check:");


--       check no. 095
Drop     table tempA;
Create   table           tempA
Select   sourceTypeId   as sourceTypeId2,
         'no '          as aMatch,
         count(*)       as n
From     sourceTypeYear
Group by sourceTypeId2;


Update tempA as a set aMatch='yes' where (Select min(m.yearId)
                                          From   movesdb20151028.sourceTypeYear as m
                                          Where  a.sourceTypeId2 = m.sourceTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "sourceTypeYear" as tableName,
         95,
        "sourceTypeId"    as testDescription,
         sourceTypeId2    as testValue,
         n                as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 096  -- no check

--       check no. 097
Drop     table tempA;
Create   table           tempA
Select   sum(sourceTypePopulation) as sourceTypePopulation2,
         a.yearId,
         b.HPMSVtypeId,
         b.HPMSBaseYearVMT,
      -- HPMSVmt              as HPMSVmt,
         'no '                as aMatch,
         count(*)             as n

From                     sourceTypeYear as a
Join     movesdb20151028.sourceUseType  as c on c.sourceTypeId = a.sourceTypeId
Join                     HpmsVTypeYear  as b on b.HPMSVtypeId  = c.HPMSVtypeId

where    a.yearId = b.yearId
Group by a.yearId,
         b.HPMSVtypeId;

Update tempA as a set aMatch='yes' where a.HPMSBaseYearVMT >0 and sourceTypePopulation2<>0;
Update tempA as a set aMatch='yes' where a.HPMSBaseYearVMT= 0 and sourceTypePopulation2= 0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count,
         HPMSVtypeId,
         yearId
 )
Select   "sourceTypeYear or HpmsVTypeYear"                as tableName,
         97,
        "sourceTypePopulation or HPMSBaseYearVMT is zero" as testDescription,
         HPMSBaseYearVMT                                  as testValue,
         n                                                as count,
         HPMSVTypeId,
         yearId

From     tempA
Where    aMatch <> 'yes';

--       check no. 098  -- no check

--       check no. 099
Drop     table tempA;
Create   table           tempA
Select   yearId       as yearId2,
         'no '        as aMatch,
         count(*)     as n
From     sourceTypeYear
Group by yearId2;


Update tempA as a set aMatch='yes' where (Select m.yearId
                                          From   movesdb20151028.year as m
                                          Where  a.yearId2 = m.yearId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "sourceTypeYear" as tableName,
         99,
        "yearId"          as testDescription,
         yearId2          as testValue,
         n                as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 100  -- no check

--       check no. 101  -- no check

Insert into   County_Database_Checks ( TableName,  TestDescription )
       values
                           ("state",    "Table Check:");

--       check no. 102
Drop     table if exists tempA;
Create   table           tempA
Select   stateId      as stateId2,
         'no '        as aMatch,
         count(*)     as n
From     state
Group by stateId2;

Update tempA as a set aMatch='yes' where (Select m.stateId
                                          From   movesdb20151028.state as m
                                          Where  a.stateId2 = m.stateId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "state"            as tableName,
         102,
         "statId not valid" as testDescription,
         stateId2           as testValue,
         n                  as count
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,  TestDescription )
       values
                           ("year",     "Table Check:");


--       check no. 103
Drop     table if exists tempA;
Create   table           tempA
Select   isBaseYear   as isBaseYear2,
         'no '        as aMatch,
         count(*)     as n
From     year
Group by isBaseYear2;


Update tempA as a set aMatch='yes' where isBaseYear2 in ('Y', 'N');

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "year"                  as tableName,
         103,
         "isBaseYear not Y or N" as testDescription,
         null                    as testValue,
         n                       as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 104
Drop     table if exists tempA;
Create   table           tempA
Select   fuelYearId   as fuelYearId2,
         yearId       as yearId2,
         'no '        as aMatch,
         count(*)     as n
From     year
Group by fuelYearId2,
         yearId2;


Update tempA set aMatch='yes' where fuelYearId2=yearId2;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count     )
Select   "year"                          as tableName,
         104                             as checkNumber,
        "fuelYearId not equal to yearId" as testDescription,
         fuelYearId2                     as testValue,
         n                               as count

From     tempA
Where    aMatch <> 'yes';


--       check no. 105
Drop     table tempA;
Create   table           tempA
Select   yearId       as yearId2,
         'no '        as aMatch,
         count(*)     as n
From     year
Group by yearId2;


Update tempA as a set aMatch='yes' where (Select m.yearId
                                          From   movesdb20151028.year as m
                                          Where  a.yearId2 = m.yearId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "year"   as tableName,
         105,
        "yearId"  as testDescription,
         yearId2  as testValue,
         n        as count
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,  TestDescription )
       values
                           ("zone",     "Table Check:");

--       check no. 106
Drop     table if exists tempA;
Create   table           tempA
Select   countyId        as countyId2,
         'no '           as aMatch,
         count(*)        as n
From     zone
Group by countyId2;

Update tempA as a set aMatch='yes' where (Select m.countyId
                                          From   movesdb20151028.county as m
                                          Where  a.countyId2 = m.countyId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zone"     as tableName,
         106,
        "countyId"  as testDescription,
         countyId2  as testValue,
         n          as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 107
Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue    )

Select  "zone"                           as tableName,
         107                             as checkNumber,
        "sum of startAllocFactor <> 1.0" as testDescription,
         sum(startAllocFactor)           as testValue
From     zone
Group by zoneId
Having   testValue <0.999 or testValue >1.001;

--       check no. 108
Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue    )

Select  "zone"                          as tableName,
         108                            as checkNumber,
        "sum of idleAllocFactor <> 1.0" as testDescription,
         sum(idleAllocFactor)           as testValue
From     zone
Group by zoneId
Having   testValue <0.999 or testValue >1.001;

--       check no. 109
Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue    )

Select  "zone"                         as tableName,
         109                           as checkNumber,
        "sum of SHPAllocFactor <> 1.0" as testDescription,
         sum(SHPAllocFactor)           as testValue
From     zone
Group by zoneId
Having   testValue <0.999 or testValue >1.001;


--       check no. 110
Drop     table if exists tempA;
Create   table           tempA
Select   zoneId          as zoneId2,
         'no '           as aMatch,
         count(*)        as n
From     zone
Group by zoneId2;


Update tempA as a set aMatch='yes' where (Select m.zoneId
                                          From   movesdb20151028.zone as m
                                          Where  a.zoneId2 = m.zoneId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zone"   as tableName,
         110,
        "zoneId"  as testDescription,
         zoneId2  as testValue,
         n        as count
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,       TestDescription )
       values
                           ("zoneMonthHour", "Table Check:");

--       check no. 111
Drop     table if exists tempA;
Create   table           tempA
Select   hourId    as hourId2,
         'no '     as aMatch,
         count(*)  as n
From     zoneMonthHour
Group by hourId2;

Update tempA as a set aMatch='yes' where (Select m.hourId
                                          From   movesdb20151028.hourOfAnyDay as m
                                          Where  a.hourId2 = m.hourId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zoneMonthHour" as tableName,
         111,
         "hourId"        as testDescription,
         hourId2         as testValue,
         n               as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 112
Drop     table if exists tempA;
Create   table           tempA
Select   monthId      as monthId2,
         'no '        as aMatch,
         count(*)     as n
From     zoneMonthHour
Group by monthId2;


Update tempA as a set aMatch='yes' where (Select m.monthId
                                          From   movesdb20151028.monthOfAnyYear as m
                                          Where  a.monthId2 = m.monthId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zoneMonthHour"    as tableName,
         112,
        "monthId"           as testDescription,
         monthId2           as testValue,
         n                  as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 113
Drop     table if exists tempA;
Create   table           tempA
Select   temperature  as temperature2,
         'no '        as aMatch,
         count(*)     as n
From     zoneMonthHour
Group by temperature2;


Update tempA as a set aMatch='yes' where temperature2>=-80.0 and temperature2<=150.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zoneMonthHour"    as tableName,
         113,
        "temperature"       as testDescription,
         temperature2       as testValue,
         n                  as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 114  -- no check

--       check no. 115
Drop     table if exists tempA;
Create   table           tempA
Select   relHumidity  as relHumidity2,
         'no '        as aMatch,
         count(*)     as n
From     zoneMonthHour
Group by relHumidity2;


Update tempA as a set aMatch='yes' where relHumidity2>=0.0 and relHumidity2<=100.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zoneMonthHour"    as tableName,
         115,
        "relHumidity"       as testDescription,
         relHumidity2       as testValue,
         n                  as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 116  -- no check
--       check no. 117  -- no check
--       check no. 118  -- no check

--       check no. 119
Drop     table if exists tempA;
Create   table           tempA
Select   zoneId          as zoneId2,
         'no '           as aMatch,
         count(*)        as n
From     zonemonthhour
Group by zoneId2;


Update tempA as a set aMatch='yes' where (Select m.zoneId
                                          From   movesdb20151028.zone as m
                                          Where  a.zoneId2 = m.zoneId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zoneMonthHour" as tableName,
         119,
        "zoneId"         as testDescription,
         zoneId2         as testValue,
         n               as count               --
From     tempA
Where    aMatch <> 'yes';


Insert into   County_Database_Checks ( TableName,      TestDescription )
       values
                           ("zoneRoadType", "Table Check:");

--       check no. 120
Drop     table if exists tempA;
Create   table           tempA
Select   roadTypeId as roadTypeId2,
         'no '      as aMatch,
         count(*)   as n
From     zoneRoadtype
Group by roadTypeId2;


Update tempA as a set aMatch='yes' where (Select m.roadTypeId
                                          From   movesdb20151028.roadType as m
                                          Where  a.roadTypeId2 = m.roadTypeId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zoneRoadType" as tableName,
         120,
        "roadTypeId"            as testDescription,
         roadTypeId2            as testValue,
         n                      as count
From     tempA
Where    aMatch <> 'yes';


--       check no. 121
Drop     table if exists tempA;
Create   table           tempA
Select   zoneId          as zoneId2,
         'no '           as aMatch,
         count(*)        as n
From     zoneroadtype
Group by zoneId2;


Update tempA as a set aMatch='yes' where (Select m.zoneId
                                          From   movesdb20151028.zone as m
                                          Where  a.zoneId2 = m.zoneId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zoneRoadType" as tableName,
         121,
        "zoneId"        as testDescription,
         zoneId2        as testValue,
         n              as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 122
Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )

Select  "zoneRoadType"       as tableName,
         122,
        "roadTypeId"         as testDescription,
         sum(SHOAllocFactor) as SHOAllocFactor2,
         count(*)
From     zoneroadtype
Group by roadTypeId
Having   SHOAllocFactor2 <0.999 or SHOAllocFactor2 >1.000000001;


Insert into   County_Database_Checks ( TableName,           TestDescription )
       values
                           ("emissionRateByAge", "Table Check:");


--       check no. 123  no check

--       check no. 124
Drop     table if exists tempA;
Create   table           tempA
Select   polProcessId    as polProcessId2,
         'no '           as aMatch,
         count(*)        as n
From     emissionRateByAge
Group by polProcessId2;


Update tempA as a set aMatch='yes' where (Select min(m.polProcessId)
                                          From   movesdb20151028.emissionRateByAge as m
                                          Where  a.polProcessId2 = m.polProcessId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "emissionRateByAge" as tableName,
         124,
        "polProcessId"       as testDescription,
         polProcessId2       as testValue,
         n                   as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 125
Drop     table if exists tempA;
Create   table           tempA
Select   opModeId        as opModeId2,
         'no '           as aMatch,
         count(*)        as n
From     emissionRateByAge
Group by opModeId2;

Update tempA as a, movesdb20151028.operatingmode as m
  set aMatch='yes' 
  where a.opModeId2 = m.opModeId;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "emissionRateByAge" as tableName,
         125,
        "opModeId"       as testDescription,
         opModeId2       as testValue,
         n               as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 126
Drop     table if exists tempA;
Create   table           tempA
Select   ageGroupId      as ageGroupId2,
         'no '           as aMatch,
         count(*)        as n
From     emissionRateByAge
Group by ageGroupId2;


Update tempA as a set aMatch='yes' where (Select min(m.ageGroupId)
                                          From   movesdb20151028.ageGroup as m
                                          Where  a.ageGroupId2 = m.ageGroupId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "emissionRateByAge" as tableName,
         126,
        "ageGroupId"         as testDescription,
         ageGroupId2         as testValue,
         n                   as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 127  -- no check
--       check no. 128  -- no check
--       check no. 129  -- no check
--       check no. 130  -- no check
--       check no. 131  -- no check


Insert into   County_Database_Checks ( TableName,    TestDescription )
       values
                           ("countyYear", "Table Check:");

--       check no. 132
Drop     table if exists tempA;
Create   table           tempA
Select   countyId        as countyId2,
         'no '           as aMatch,
         count(*)        as n
From     countyYear
Group by countyId2;

Update tempA as a set aMatch='yes' where (Select m.countyId
                                          From   movesdb20151028.county as m
                                          Where  a.countyId2 = m.countyId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "countyYear" as tableName,
         132,
        "countyId" as testDescription,
         countyId2 as testValue,
         n         as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 133
Drop     table if exists tempA;
Create   table           tempA
Select   yearId          as yearId2,
         'no '           as aMatch,
         count(*)        as n
From     countyYear
Group by yearId2;


Update tempA as a set aMatch='yes' where (Select min(m.yearId)
                                          From   movesdb20151028.year as m
                                          Where  a.yearId2 = m.yearId);

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "countyYear" as tableName,
         133,
        "yearId" as testDescription,
         yearId2 as testValue,
         n       as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 134
Drop     table if exists tempA;
Create   table           tempA
Select   refuelingVaporProgramAdjust as refuelingVaporProgramAdjust2,
         'no '                       as aMatch,
         count(*)                    as n
From     countyYear
Group by refuelingVaporProgramAdjust2;


Update tempA as a set aMatch='yes' where refuelingVaporProgramAdjust2 >= 0.0
                                     and refuelingVaporProgramAdjust2 <= 1.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "countyYear"                 as tableName,
         134,
        "refuelingVaporProgramAdjust" as testDescription,
         refuelingVaporProgramAdjust2 as testValue,
         n       as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 135
Drop     table if exists tempA;
Create   table           tempA
Select   refuelingSpillProgramAdjust as refuelingSpillProgramAdjust2,
         'no '                       as aMatch,
         count(*)                    as n
From     countyYear
Group by refuelingSpillProgramAdjust2;


Update tempA as a set aMatch='yes' where refuelingSpillProgramAdjust2 >= 0.0
                                     and refuelingSpillProgramAdjust2 <= 1.0;

Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "countyYear"                 as tableName,
         135,
        "refuelingSpillProgramAdjust" as testDescription,
         refuelingSpillProgramAdjust2 as testValue,
         n       as count               --
From     tempA
Where    aMatch <> 'yes';


--       check no. 136
Drop     table if exists tempA;
Create   table           tempA
Select   roadTypeId                  as roadType2,
         SHOAllocFactor              as SHOAllocFactor2,
         'no '                       as aMatch,
         count(*)                    as n
From     zoneRoadType
Group by roadType2;

-- Select * from tempA;

Update tempA as a set a.aMatch='yes' where a.SHOAllocFactor2 = 1.0
                                       and a.roadType2 in (2,3,4,5);

-- individual road type SHOAlloctFactor error
Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count,
         roadTypeId  )
Select   "zoneRoadType"               as tableName,
         136                          as checkNumber,
        "SHOAllocFactor <> 1.0"       as testDescription,
         SHOAllocFactor2              as testValue,
         n                            as count,
         roadType2                    as roadTypeId
From     tempA
Where    aMatch <> 'yes' and roadType2 in (2,3,4,5);




-- -- START March 2013 Checks------------------------------------------------

--       check no. 200
Insert into County_Database_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("roadType",
   200,
  "Number of Rows",
  (Select count(*) from roadType) );
Delete from County_Database_Checks where checkNumber=200 and testValue>0;

--       check no. 201
Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue)
Select   "roadType"                         as tableName,
         201,
        "restricted rampFraction not > 0.0" as testDescription,
         rampFraction                       as testValue
From     roadType
Where    roadTypeId in (2,4)
  and    rampFraction < .0001;

--       check no. 202
Insert into County_Database_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue)
Select   "roadType"                         as tableName,
         202,
        "restricted rampFraction not < 0.0" as testDescription,
         rampFraction                       as testValue
From     roadType
Where    roadTypeId not in (2,4)
  and    rampFraction > 0.0;


--       check no. 203
Insert into County_Database_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("HPMSVTypeYear",
   203,
  "Number of Rows",
  (Select count(*) from HPMSVTypeYear) );
Delete from County_Database_Checks where checkNumber=203 and testValue>0;

--       check no. 204
Insert into County_Database_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("sourceTypeYear",
   204,
  "Number of Rows",
  (Select count(*) from sourceTypeYear) );
Delete from County_Database_Checks where checkNumber=204 and testValue>0;

--       check no. 205
-- Insert into County_Database_Checks
--       ( tableName,
--         checkNumber,
--         testDescription,
--         testValue    )
-- Select   "sccRoadTypeDistribution",
--         205,
--         "sum(SCCroadTypeFraction) <> 1.0",
--         sum(SCCroadTypeFraction) as sumFraction
-- from     sccRoadTypeDistribution
-- Group by roadTypeId,
--         zoneId
-- Having   sumFraction<0.999 or sumFraction>1.001;

--       check no. 206
Insert into County_Database_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("county",
   206,
  "Number of Rows",
  (Select count(*) from county) );
Delete from County_Database_Checks where checkNumber=206 and testValue>0;

--       check no. 207
Insert into County_Database_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("state",
   207,
  "Number of Rows",
  (Select count(*) from state) );
Delete from County_Database_Checks where checkNumber=207 and testValue>0;

--       check no. 208
Insert into County_Database_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("zone",
   208,
  "Number of Rows",
  (Select count(*) from zone) );
Delete from County_Database_Checks where checkNumber=208 and testValue>0;

--       check no. 209
Insert into County_Database_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("zoneRoadType",
   209,
  "Number of Rows",
  (Select count(*) from zoneRoadType) );
Delete from County_Database_Checks where checkNumber=209 and testValue>0;


--       check no. 301   11/13
Insert into County_Database_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("fuelFormulation",
   301,
  "T50 Missing",
  (Select count(*)
   from   information_schema.columns
   where  table_name   = 'fuelformulation'
     and  column_name  = 't50'
     and  table_schema = database()) );
Delete from County_Database_Checks where checkNumber=301 and testValue>0;


call checkImCoverage();

Insert into County_Database_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("imcoverage",
   302,
  "See errImCoverage table",
  (Select count(*) from errImCoverage) );
  Delete from County_Database_Checks where checkNumber=302 and testValue=0;


Insert into County_Database_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("imcoverage",
   303,
  "seq, gap, overlap",
  (Select count(*) from errImCoverage) );
  Delete from County_Database_Checks where checkNumber=303 and testValue=0;


-- Start Feb 14, 2014 ----------------------------------------------------------------------

-- START (Feb 14 2014):

-- Checking distributions,
-- From D. B.'s 2-3-2014 Email
-- Allowing 'some' distributions to sum to zero.
-- Note: every distribution must sum to 0.0 or 1.0

-- For table Zone and table ZoneRoadType
-- The distributions must sum to 1.0 and
-- Previous checks remain valid and complete.

-- The following tables allow for zero distributions:

--                        (Error no,)  (Warning no.)
-- roadTypeDistribution      401           84
-- sourceTypeAgeDistribution 402           93
-- monthVMTFraction          403           78
-- hourVMTFraction           404           53
-- dayVMTFraction            405           23
-- avgSpeedDistribution      406           11

-- Note: the above Warning no.s refer to checks for respective non 1.0 distributions.
--       These were errors originally.

-- respective Error no.s are caused when additional checking found incorrect 0.0
-- distributions.

-- Such warnings occur Previous to this location,
-- Such errors   occur After       this location.


Drop table if exists Little;   -- starting check for table roadTypeDistribution
Create table Little
 ( tableName    char(100),
   checkNumber  int,
   source       int,
   distribution float,
   population   int,
   descr        char(40)
 );

Insert into Little     -- roadTypeDistribution, SEE check no. 84
     ( tableName,
       checkNumber,
       source,
       distribution,
       population
     )
Select "roadTypeDistribution"                as tableName,
        401                                  as checkNumber,
        a.sourceTypeId                       as source,

       (select sum(c.roadTypeVmtFraction)
        from roadTypeDistribution as c
        where a.sourceTypeId=c.sourceTypeId) as distribution,

        sourceTypePopulation                 as population

From     roadTypeDistribution as a
Join     sourceTypeYear       as b on a.sourceTypeId=b.sourceTypeId
group by a.sourceTypeId;

-- Select * from little;
update little set distribution = 1.0 where distribution>0.999
                                       and distribution<1.001;
-- Select * from little;

-- update Little set descr = 'OK  1' where distribution =  1.0;

   update Little set descr = 'Distribution must = 0 or 1'
                                  where distribution <> 0.0
                                    and distribution <> 1.0;

-- update Little set descr = 'OK  2' where distribution =  0.0
--                                    and population   =  0.0;

   update little set descr = 'Distribution = 0 and population > 0'
                                  where distribution =  0.0
                                    and population   >  0.0;


Insert into Little     -- sourceTypeAgeDistribution  SEE check no. 93
     ( tableName,
       checkNumber,
       source,
       distribution,
       population
     )
Select "sourceTypeAgeDistribution" as tableName,
        402                        as checkNumber,
        a.sourceTypeId             as source,

       (select sum(c.ageFraction)
        from sourceTypeAgeDistribution as c
        where a.sourceTypeId=c.sourceTypeId)  as distribution,

         sourceTypePopulation                 as population

From     sourceTypeAgeDistribution as a
Join     sourceTypeYear            as b on a.sourceTypeId=b.sourceTypeId
group by a.sourceTypeId;

update little set distribution = 1.0 where distribution>0.999
                                       and distribution<1.001;

update Little set descr = 'Distribution must = 0 or 1'
                                  where distribution <> 0.0
                                    and distribution <> 1.0;

update little set descr = 'Distribution = 0 and population > 0'
                                  where distribution =  0.0
                                    and population   >  0.0;



Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
         sourceTypeId
       )
Select   tableName    as tableName,
         checkNumber  as checkNumber,
         descr        as testDescription,
         distribution as testValue,
         source       as sourceType
From     little
Where    descr is not null;

Drop table if exists Little;

drop table if exists tempA;        --    SEE check no. 78
create table tempA
select   sourceTypeId,
--         isLeapYear,
         sum(monthVmtFraction)      as distribution,
        (select yearId   from year) as yr,
        (select yearId%4 from year) as m4
from     monthVMTFraction
-- group by sourceTypeId, isLeapYear;
group by sourceTypeId;

update tempA set distribution = 1.0 where distribution > 0.999 and distribution < 1.001;

alter table tempA add column LY char(1);
update tempA set LY = 'Y' where m4= 0;
update tempA set LY = 'N' where m4<>0;
alter table tempA drop column m4;

alter table tempA add column ERROR char(1);
update tempA set ERROR = 'Y';
-- update tempA set ERROR = 'N' where isLeapYear='Y' and distribution = 0 and LY='N';
-- update tempA set ERROR = 'N' where isLeapYear='N' and distribution = 0 and LY='Y';
update tempA set ERROR = 'N' where                    distribution = 1;
update tempA set ERROR = 'Y' where                    distribution<= 0.999
                                                   or distribution>= 1.001;

Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
         sourceTypeId
       )
Select  'monthVmtFraction'    as tableName,
         403                  as checkNumber,
         'distribution error' as testDescription,
         distribution         as testValue,
         sourceTypeId         as sourceType
From     tempA
Where    ERROR = 'Y';


drop   table if exists tempA;  -- hourVmtFraction;        SEE check no. 53
create table tempA
select   a.sourceTypeId,
         a.roadTypeId,
         a.dayId,
         e.monthId,

         sum(a.hourVMTFraction)               as hourVMT,

        (select b.sourceTypePopulation
         from sourceTypeYear as b
         where a.sourceTypeId=b.sourceTypeId) as sourcePop,

        (select sum(c.dayVmtFraction)
         from dayVmtFraction as c
         where a.sourceTypeId=c.sourceTypeId
           and a.roadTypeId  =c.roadTypeId
           and e.monthId     =c.monthId )     as dayVMT,

        (select sum(d.roadTypeVmtFraction)
         from roadTypeDistribution as d
         where a.sourceTypeId=d.sourceTypeId) as roadVMT

from     hourVmtFraction as a
join     dayVmtFraction  as e on a.sourceTypeId = e.sourceTypeId
                             and a.roadTypeId   = e.roadTypeId
                             and a.dayId        = e.dayId

group by a.sourceTypeId,
         a.roadTypeId,
         a.dayId,
         e.monthId;

update tempA set hourVMT=1.0 where hourVMT > 0.999 and hourVMT < 1.001;
update tempA set  dayVMT=1.0 where  dayVMT > 0.999 and  dayVMT < 1.001;
update tempA set roadVMT=1.0 where roadVMT > 0.999 and roadVMT < 1.001;

-- select 13*5*2*12;      -- 1560,   sourceTypes * roadTypes * dayIds * months;

-- select * from tempA;   -- 1560 rows

alter table tempA add column Error char(2);

update tempA set Error='OK' where hourVMT=1;
update tempA set Error=NULL where hourVMT<=0.999 or hourVMT>=1.001;
update tempA set Error='OK' where hourVMT=0 and sourcePop = 0;
update tempA set Error='OK' where hourVMT=0 and dayVMT    = 0;
update tempA set Error='OK' where hourVMT=0 and roadVMT   = 0;

-- Select * from tempA;

Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
         sourceTypeId
       )
Select  'hourVmtFraction'     as tableName,
         404                  as checkNumber,
         'distribution error' as testDescription,
         hourVMT              as testValue,
         sourceTypeId         as sourceType
From     tempA
Where    ERROR is NULL;



drop   table if exists tempA;  -- dayVmtFraction;       SEE check no. 23
create table tempA
select   a.sourceTypeId,
         a.monthId,
         a.roadTypeId,
         a.dayId,

        (select sum(b.dayVmtFraction)
         from dayVmtFraction as b
         where a.sourceTypeId=b.sourceTypeId
           and a.roadTypeId  =b.roadTypeId
           and a.monthId     =b.monthId )     as dayVMT,

        (select c.sourceTypePopulation
         from sourceTypeYear as c
         where a.sourceTypeId=c.sourceTypeId) as sourcePop,

        (select sum(d.roadTypeVmtFraction)
         from roadTypeDistribution as d
         where a.sourceTypeId=d.sourceTypeId) as roadVMT,


        (select sum(e.monthVmtFraction)
         from monthVmtFraction as e
         where a.sourceTypeId=e.sourceTypeId) as monthVMT

from     dayVmtFraction as a

group by a.sourceTypeId,
         a.monthId,
         a.roadTypeId,
         a.dayId;

update tempA set  dayVMT = 1.0 where  dayVMT > 0.999 and  dayVMT < 1.001;
update tempA set roadVMT = 1.0 where roadVMT > 0.999 and roadVMT < 1.001;

alter table tempA add column Error char(2);
update tempA set Error='OK' where dayVMT=1;
update tempA set Error=NULL where dayVMT<=0.999 or dayVMT>=1.001;
update tempA set Error='OK' where dayVMT=0 and sourcePop = 0;
update tempA set Error='OK' where dayVMT=0 and roadVMT   = 0;
update tempA set Error='OK' where dayVMT=0 and monthVMT  = 0;

-- select * from tempA;

Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
         sourceTypeId
       )
Select  'dayVmtFraction'      as tableName,
         405                  as checkNumber,
         'distribution error' as testDescription,
         dayVMT               as testValue,
         sourceTypeId         as sourceType
From     tempA
Where    ERROR is NULL;

drop   table if exists tempA;  -- avgSpeedDistribution;      SEE check no. 11
create table tempA
select   a.sourceTypeId,
         a.roadTypeId,
         a.hourDayId,

         sum(a.avgSpeedFraction)              as avgSpeed,

        (select b.sourceTypePopulation
         from sourceTypeYear as b
         where a.sourceTypeId=b.sourceTypeId) as sourcePop,

        (select sum(c.roadTypeVmtFraction)
         from roadTypeDistribution as c
         where a.sourceTypeId=c.sourceTypeId) as roadVMT,

        (select sum(d.hourVMTFraction)
         from hourVmtFraction as d
         where a.sourceTypeId=d.sourceTypeId
           and a.roadTypeId  =d.roadTypeId)   as dayVMT

from     avgSpeedDistribution as a

group by a.sourceTypeId,
         a.roadTypeId,
         a.hourDayId;

-- select * from tempA;

update tempA set avgSpeed=1.0 where avgSpeed>0.999 and avgSpeed<1.001;
update tempA set roadVMT =1.0 where roadVMT >0.999 and roadVMT <1.001;
update tempA set dayVMT  = round(dayVMT,3);

alter table tempA add column Error char(2);
update tempA set Error='OK' where avgSpeed=1;

update tempA set Error=NULL where avgSpeed<=0.999 or avgSpeed>=1.001;
update tempA set Error='OK' where avgSpeed=0 and sourcePop = 0;
update tempA set Error='OK' where avgSpeed=0 and roadVMT   = 0;
update tempA set Error='OK' where avgSpeed=0 and dayVMT    = 0;

Insert into County_Database_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
         sourceTypeId,
         roadTypeId,
         hourDayId
       )
Select  'avgSpeedDistribution' as tableName,
         406                   as checkNumber,
         'distribution error'  as testDescription,
         avgSpeed              as testValue,
         sourceTypeId,
         roadTypeId,
         hourDayId
From     tempA
Where    ERROR is NULL;

-- End   Feb 14, 2014 ----------------------------------------------------------------------

-- start Sept 23 2014:
-- check the fuelEngFracion distributions of AVFT table

create   table           tempA
select   sourceTypeId,
         modelYearId,
         sum(fuelEngFraction) as distribution
from     avft
group by sourceTypeId,
         modelYearId;

update tempA set distribution = 1.0 where distribution > 0.999 and distribution < 1.001;

  Insert into County_Database_Checks
       ( tableName,

         checkNumber,
         testDescription,
         testValue
       )
  Select 'avft'                         as tableName,
         407                            as checkNumber,
         'Sum <> 1.0 Errors:'           as testDescription,
         ( select count(*)
           from   tempA
           where  distribution <> 1.0 ) as testValue;

delete from County_Database_Checks where checkNumber = 407 and testValue = 0;



--
--       Final Message:
--

Delete from   County_Database_Checks where TableName = "ALL";

Insert into   County_Database_Checks ( TableName, TestDescription )
       values
                           ("ALL",     "Tables Checked.");


Update        County_Database_Checks set msgType = 'Progress'   where testDescription = 'Table Check:';

Update        County_Database_Checks set msgType = 'Data Error' where checkNumber is not null
                                                                  and msgType     is     null;

Update        County_Database_Checks set msgType = 'DB Checked' where tableName = 'ALL';
Update        County_Database_Checks set msgDate = curDate();
Update        County_Database_Checks set msgTime = curTime();

Update        County_Database_Checks set dataBaseName = (select database())
                                   where dataBaseName is null;

Update        County_Database_Checks set countyId = (select min(countyId)
                                                     from county);

Update        County_Database_Checks set status = 'Completed';
Update        County_Database_Checks set status = 'Error'   where checkNumber is not null;
Update        County_Database_Checks set status = 'Warning' where checkNumber in (84, 93, 78, 53, 23, 11);


Update        County_Database_checks set status  = 'Comment' where checkNumber = 302 and testValue=0;
Update        County_Database_checks set msgType = 'Comment' where checkNumber = 302 and testValue=0;



Drop     table if exists tempA;
Drop     table if exists tempB;


-- Version:
-- See version date at beginning of Script.
Update        County_Database_Checks set version = '20140924';


-- Select * from County_Database_Checks;

-- -------------------------------------------------------------------------------------------
-- -------------------------------------------------------------------------------------------

create Database if not exists All_CDB_Checks;

-- drop table All_CDB_Checks.All_County_Database_Checks;

create Table    if not exists All_CDB_Checks.All_County_Database_Checks
like County_Database_Checks;


Insert into All_CDB_Checks.All_County_Database_Checks
Select * from                  County_Database_Checks;

flush tables;

DROP   PROCEDURE IF EXISTS checkImCoverage;
