-- MOVES3 NEI QA Script for Onroad CDBs
-- Last Updated: MOVES3.0.3
-- ##############################################################################
-- Usage Notes:  This script is intended to be run via the ANT command 
--               "onroadNEIQA". See database\NEIQA\NEIQAHelp.pdf for more info.
--               If this script needs to be run directly against MariaDB (i.e.,
--               using mysql.exe or through Workbench/Heidi:
--               1. Find & Replace "BeginBlock" with "Delimiter $$"
--               2. Find & Replace "EndBlock" with "$$\nDelimiter ;" (converting
--                     the \n to a new line)
--               3. Find & Replace "##defaultdb##" with the name of the default 
--                     database, as found in MOVESConfiguration.txt
-- ##############################################################################

set @version = CONCAT('db', MID("##defaultdb##", 8, 8), '-rev20220104');

-- Coordinate this message with the Done statement at the end of this file.
select '  .. M3onroadCDBchecks.sql',curTime(), database();

-- ##############################################################################
-- define stored procedures
-- ##############################################################################


-- ##############################################################################
-- Check the IMCoverage table for overlaps and gaps
-- ##############################################################################
DROP   PROCEDURE IF EXISTS checkImCoverage;
BeginBlock
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;
    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
        then
          Set reason = 'CbegModelYearId>CendModelYearId,';
          INSERT INTO qa_checks_im 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 qa_checks_im 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 qa_checks_im 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;

END
EndBlock
-- ##############################################################################


-- ##############################################################################
-- Check the HotellingActivityDistribution table for overlaps and gaps in MYs
-- ##############################################################################
DROP   PROCEDURE IF EXISTS checkHotellingActivityDistribution;
BeginBlock
CREATE PROCEDURE checkHotellingActivityDistribution()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  declare Czone, CBEGMY, CENDMY int;  -- C is for current (row of data)
  declare Lzone, LBEGMY, LENDMY int;  -- L is for last     "   "  "
  declare rows_processed,
          rows_skipped   int default 0;
  declare reason char(40);
  declare sameSet int;
  DECLARE curHAD CURSOR FOR SELECT distinct zoneID,
                                     beginModelYearId,
                                     endModelYearId
                                FROM hotellingactivitydistribution
                            ORDER BY zoneID,
                                     beginModelYearId,
                                     endModelYearId;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN curHAD;
  SET Lzone   = -1;
  SET LENDMY = -1;

  read_loop: LOOP
    FETCH curHAD INTO Czone, CBEGMY, CENDMY;
    IF done THEN LEAVE read_loop; END IF;

	set rows_processed = rows_processed + 1;
	
	-- Order Error:  (regardless of sameSet or not)
	if CBEGMY > CENDMY
	then
	  Set reason = 'CbegModelYearId>CendModelYearId,';
	  INSERT INTO qa_checks_had values ( Czone, LENDMY, CBEGMY, CENDMY, reason );
	else
	  if Czone=Lzone
	  then  -- in set:

		-- overlap error from consecutive rows
		IF CBEGMY<=LENDMY      then  -- overlap error:
		Set reason = 'CBEGMY<=LENDMY (Overlap)';
		INSERT INTO qa_checks_had values ( Czone, LENDMY, CBEGMY, CENDMY, reason );

		-- Gap from BEGMY to LENDMY > 1
		elseif CBEGMY>LENDMY+1
		then  -- gap error:
		   Set Reason = 'CBEGMY>LENDMY+1 (GAP)';
		   INSERT INTO qa_checks_had values ( Czone, LENDMY, CBEGMY, CENDMY, reason );
		end if;  -- end of overlap & gap checking

	  else  -- not in set:
		SET Lzone   =   Czone;
	  end if;  -- end of set

	  set LENDMY = CENDMY;
	  
	end if;  -- end of order check
	
  END LOOP;
  CLOSE curHAD;

END
EndBlock
-- ##############################################################################


-- ##############################################################################
-- Check the idleModelYearGrouping table for overlaps and gaps in MYs
-- ##############################################################################
DROP PROCEDURE IF EXISTS checkIdleModelYearGrouping;
BeginBlock
CREATE PROCEDURE checkIdleModelYearGrouping()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  declare Cst, CBEGMY, CENDMY int;  -- C is for current (row of data)
  declare Lst, LBEGMY, LENDMY int;  -- L is for last     "   "  "
  declare rows_processed,
          rows_skipped   int default 0;
  declare reason char(40);
  declare sameSet int;
  DECLARE curIMYG CURSOR FOR SELECT distinct sourceTypeID,
											 minModelYearID,
											 maxModelYearID
										FROM idlemodelyeargrouping
									ORDER BY sourceTypeID,
											 minModelYearId,
											 maxModelYearId;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN curIMYG;
  SET Lst    = -1;
  SET LENDMY = -1;

  read_loop: LOOP
    FETCH curIMYG INTO Cst, CBEGMY, CENDMY;
    IF done THEN LEAVE read_loop; END IF;

	set rows_processed = rows_processed + 1;
	
	-- Order Error:  (regardless of sameSet or not)
	if CBEGMY > CENDMY
	then
	  Set reason = 'CminModelYearId>CmaxModelYearId,';
	  INSERT INTO qa_checks_imyg values ( Cst, LENDMY, CBEGMY, CENDMY, reason );
	else
	  if Cst=Lst
	  then  -- in set:

		-- overlap error from consecutive rows
		IF CBEGMY<=LENDMY      then  -- overlap error:
		Set reason = 'CMinMY<=LMaxMY (Overlap)';
		INSERT INTO qa_checks_imyg values ( Cst, LENDMY, CBEGMY, CENDMY, reason );

		-- Gap from BEGMY to LENDMY > 1
		elseif CBEGMY>LENDMY+1
		then  -- gap error:
		   Set Reason = 'CMinMY>LMaxMY+1 (GAP)';
		   INSERT INTO qa_checks_imyg values ( Cst, LENDMY, CBEGMY, CENDMY, reason );
		end if;  -- end of overlap & gap checking

	  else  -- not in set:
		SET Lst   =   Cst;
	  end if;  -- end of set

	  set LENDMY = CENDMY;
	  
	end if;  -- end of order check
	
  END LOOP;
  CLOSE curIMYG;

END
EndBlock
-- ##############################################################################


-- ##############################################################################
-- Check the totalidlefraction table for overlaps and gaps
-- ##############################################################################
DROP   PROCEDURE IF EXISTS checkTotalIdleFraction;
BeginBlock
CREATE PROCEDURE checkTotalIdleFraction()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  declare Cst, Cmonth, Cday, CBEGMY, CENDMY int;  -- C is for current (row of data)
  declare Lst, Lmonth, Lday, LBEGMY, LENDMY int;  -- L is for last     "   "  "
  declare rows_processed,
          rows_skipped   int default 0;
  declare reason char(40);
  declare sameSet int;
  DECLARE curTIF CURSOR FOR SELECT sourceTypeID,
                                     monthID,
                                     dayID,
                                     minModelYearId,
                                     maxModelYearId
                                FROM totalidlefraction
                            ORDER BY sourceTypeID,
                                     monthID,
                                     dayID,
                                     minModelYearId,
                                     maxModelYearId;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN curTIF;
  SET Lst    = -1;
  SET Lmonth = -1;
  SET Lday   = -1;
  SET LENDMY = -1;

  read_loop: LOOP
    FETCH curTIF INTO Cst, Cmonth, Cday, CBEGMY, CENDMY;
    IF done THEN LEAVE read_loop; END IF;

	set rows_processed = rows_processed + 1;
	
	-- Order Error:  (regardless of sameSet or not)
	if CBEGMY > CENDMY
	then
	  Set reason = 'CminModelYearId>CmaxModelYearId,';
	  INSERT INTO qa_checks_tif values ( Cst, Cmonth, Cday, LENDMY, CBEGMY, CENDMY, reason );
	else
	  if Cst=Lst and Cmonth=Lmonth and Cday=Lday
	  then  -- in set:

		-- overlap error from consecutive rows
		IF CBEGMY<=LENDMY      then  -- overlap error:
		Set reason = 'CMaxMY<=LMinMY (Overlap)';
		INSERT INTO qa_checks_tif values ( Cst, Cmonth, Cday, LENDMY, CBEGMY, CENDMY, reason );

		-- Gap from BEGMY to LENDMY > 1
		elseif CBEGMY>LENDMY+1
		then  -- gap error:
		   Set Reason = 'CMinMY>LMaxMY+1 (GAP)';
		   INSERT INTO qa_checks_tif values ( Cst, Cmonth, Cday, LENDMY, CBEGMY, CENDMY, reason );
		end if;  -- end of overlap & gap checking

	  else  -- not in set:
		SET Lst    =   Cst;
		SET Lmonth =   Cmonth;
		SET Lday   =   Cday;
	  end if;  -- end of set

	  set LENDMY = CENDMY;
	  
	end if;  -- end of order check

  END LOOP;
  CLOSE curTIF;

END
EndBlock
-- ##############################################################################


-- ##############################################################################
-- The overlaps and gaps checking store procedures create temporary tables to
-- store the detailed results of their checks; drop these tables if they are
-- empty, but keep them if they have useful data showing where the errors are
--
-- Perform other cleanup
-- ##############################################################################
DROP   PROCEDURE IF EXISTS emptyTableCleanUp;
BeginBlock
CREATE PROCEDURE emptyTableCleanUp()
BEGIN
  -- holds rows of the qa_checks_x tables that exist and are empty
  drop table if exists tempC;
  create table tempC
	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 ( 'qa_checks_im', 'qa_checks_had', 'qa_checks_imyg', 'qa_checks_tif');

  -- drop qa_checks_x tables if they appear in tempC; otherwise, save them
  if (select count(*) from tempC where TableName = 'qa_checks_im') = 1 then
	drop table qa_checks_im;
  end if;
  if (select count(*) from tempC where TableName = 'qa_checks_had') = 1 then
	drop table qa_checks_had;
  end if;
  if (select count(*) from tempC where TableName = 'qa_checks_imyg') = 1 then
	drop table qa_checks_imyg;
  end if;
  if (select count(*) from tempC where TableName = 'qa_checks_tif') = 1 then
	drop table qa_checks_tif;
  end if;
  
  -- drop tables that get added to the input database if they are missing (so the script doesn't exit early)
  -- but we don't actually want them after the script runs if they are not necessary
  if (select count(*) from CDB_Checks where status = 'todo' and testDescription='table added by QA script and should be removed' and tableName = 'countyyear') = 1 then
	delete from CDB_Checks where status = 'todo' and testDescription='table added by QA script and should be removed' and tableName = 'countyyear';
    drop table if exists countyyear;
  end if;
  if (select count(*) from CDB_Checks where status = 'todo' and testDescription='table added by QA script and should be removed' and tableName = 'emissionratebyage') = 1 then
	delete from CDB_Checks where status = 'todo' and testDescription='table added by QA script and should be removed' and tableName = 'emissionratebyage';
    drop table if exists emissionratebyage;
  end if;
  if (select count(*) from CDB_Checks where status = 'todo' and testDescription='table added by QA script and should be removed' and tableName = 'hotellinghours') = 1 then
	delete from CDB_Checks where status = 'todo' and testDescription='table added by QA script and should be removed' and tableName = 'hotellinghours';
    drop table if exists hotellinghours;
  end if;
  
  -- drop temporary table
  drop table if exists tempC;

END
EndBlock
-- ##############################################################################


-- ##############################################################################
-- Check for missing tables or warn if certain tables are present but unexpected
-- ##############################################################################
-- Create a table to contain the results of the table checks.
drop table if exists CDB_Checks;
CREATE TABLE CDB_Checks (
   countyID          int(11),
   `status`          char(20),
   tableName         char(100),
   checkNumber       smallint(6),
   testDescription   char(250),
   testValue         text,
   `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(22),
   sumKeyID          int(11),
   sumKeyDescription char(50)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- This table contains entries for every QA check, so if this script fails, looking at the last row
-- should help determine where the error occurred.
drop table if exists QA_Checks_Log;
create table QA_Checks_Log (
    checkNo    int(11),
    status     char(20),
    version    char(8),
    msgDate    date,
    msgTime    time
  ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- The first set of rows in the the CDB_Checks simply list the tables we are checking along with 
-- how many rows are in each table. This chunk creates the entries for each table; the number of
-- rows are added to these rows below. The reason why this is done in a two-step process is because
-- we don't want the script to crash if a table is missing (so get the information about them from
-- table schema instead of directly)
Insert into CDB_Checks set tableName = 'auditlog';
Insert into CDB_Checks set tableName = 'county';
Insert into CDB_Checks set tableName = 'countyyear';
Insert into CDB_Checks set tableName = 'state';
Insert into CDB_Checks set tableName = 'zone';
Insert into CDB_Checks set tableName = 'zonemonthhour';
Insert into CDB_Checks set tableName = 'zoneroadtype';
Insert into CDB_Checks set tableName = 'year';
Insert into CDB_Checks set tableName = 'avft';
Insert into CDB_Checks set tableName = 'fuelformulation';
Insert into CDB_Checks set tableName = 'fuelsupply';
Insert into CDB_Checks set tableName = 'fuelusagefraction';
Insert into CDB_Checks set tableName = 'hourvmtfraction';
Insert into CDB_Checks set tableName = 'dayvmtfraction';
Insert into CDB_Checks set tableName = 'monthvmtfraction';
Insert into CDB_Checks set tableName = 'hpmsvtypeday';
Insert into CDB_Checks set tableName = 'hpmsvtypeyear';
Insert into CDB_Checks set tableName = 'sourcetypedayvmt';
Insert into CDB_Checks set tableName = 'sourcetypeyearvmt';
Insert into CDB_Checks set tableName = 'hotellingactivitydistribution';
Insert into CDB_Checks set tableName = 'hotellingagefraction';
Insert into CDB_Checks set tableName = 'hotellinghoursperday';
Insert into CDB_Checks set tableName = 'hotellinghourfraction';
Insert into CDB_Checks set tableName = 'hotellingmonthadjust';
Insert into CDB_Checks set tableName = 'starts';
Insert into CDB_Checks set tableName = 'startsageadjustment';
Insert into CDB_Checks set tableName = 'startshourfraction';
Insert into CDB_Checks set tableName = 'startsmonthadjust';
Insert into CDB_Checks set tableName = 'startsperday';
Insert into CDB_Checks set tableName = 'startsperdaypervehicle';
Insert into CDB_Checks set tableName = 'startsopmodedistribution';
Insert into CDB_Checks set tableName = 'idledayadjust';
Insert into CDB_Checks set tableName = 'idlemodelyeargrouping';
Insert into CDB_Checks set tableName = 'idlemonthadjust';
Insert into CDB_Checks set tableName = 'totalidlefraction';
Insert into CDB_Checks set tableName = 'avgspeeddistribution';
Insert into CDB_Checks set tableName = 'imcoverage';
Insert into CDB_Checks set tableName = 'onroadretrofit';
Insert into CDB_Checks set tableName = 'roadtypedistribution';
Insert into CDB_Checks set tableName = 'sourcetypeagedistribution';
Insert into CDB_Checks set tableName = 'sourcetypeyear';
Insert into CDB_Checks set tableName = 'emissionratebyage';
Insert into CDB_Checks set tableName = 'hotellinghours';

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

-- tempB holds the number of rows for each table that we are checking
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',
					   'county',
					   'countyyear',
					   'state',
					   'zone',
					   'zonemonthhour',
					   'zoneroadtype',
					   'year',
					   'avft',
					   'fuelformulation',
					   'fuelsupply',
					   'fuelusagefraction',
					   'hourvmtfraction',
					   'dayvmtfraction',
					   'monthvmtfraction',
					   'hpmsvtypeday',
					   'hpmsvtypeyear',
					   'sourcetypedayvmt',
					   'sourcetypeyearvmt',
					   'hotellingactivitydistribution',
					   'hotellingagefraction',
					   'hotellinghoursperday',
					   'hotellinghourfraction',
					   'hotellingmonthadjust',
					   'starts',
					   'startsageadjustment',
					   'startshourfraction',
					   'startsmonthadjust',
					   'startsperday',
					   'startsperdaypervehicle',
					   'startsopmodedistribution',
					   'idledayadjust',
					   'idlemodelyeargrouping',
					   'idlemonthadjust',
					   'totalidlefraction',
					   'avgspeeddistribution',
					   'imcoverage',
					   'onroadretrofit',
					   'roadtypedistribution',
					   'sourcetypeagedistribution',
					   'sourcetypeyear',
					   'emissionratebyage',
					   'hotellinghours'
                     );

-- This chunk updates the first set of entries in CDB_Checks to contain the number of rows in each table
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'auditlog' and  b.TableName = 'auditlog'; 
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'county' and  b.TableName = 'county';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'countyyear' and  b.TableName = 'countyyear';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'state' and  b.TableName = 'state';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'zone' and  b.TableName = 'zone';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'zonemonthhour' and  b.TableName = 'zonemonthhour';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'zoneroadtype' and  b.TableName = 'zoneroadtype';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'year' and  b.TableName = 'year';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'avft' and  b.TableName = 'avft';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'fuelformulation' and  b.TableName = 'fuelformulation';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'fuelsupply' and  b.TableName = 'fuelsupply';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'fuelusagefraction' and  b.TableName = 'fuelusagefraction';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'hourvmtfraction' and  b.TableName = 'hourvmtfraction';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'dayvmtfraction' and  b.TableName = 'dayvmtfraction';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'monthvmtfraction' and  b.TableName = 'monthvmtfraction';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'hpmsvtypeday' and  b.TableName = 'hpmsvtypeday';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'hpmsvtypeyear' and  b.TableName = 'hpmsvtypeyear';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'sourcetypedayvmt' and  b.TableName = 'sourcetypedayvmt';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'sourcetypeyearvmt' and  b.TableName = 'sourcetypeyearvmt';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'hotellingactivitydistribution' and  b.TableName = 'hotellingactivitydistribution';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'hotellingagefraction' and  b.TableName = 'hotellingagefraction';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'hotellinghoursperday' and  b.TableName = 'hotellinghoursperday';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'hotellinghourfraction' and  b.TableName = 'hotellinghourfraction';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'hotellingmonthadjust' and  b.TableName = 'hotellingmonthadjust';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'starts' and  b.TableName = 'starts';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'startsageadjustment' and  b.TableName = 'startsageadjustment';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'startshourfraction' and  b.TableName = 'startshourfraction';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'startsmonthadjust' and  b.TableName = 'startsmonthadjust';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'startsperday' and  b.TableName = 'startsperday';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'startsperdaypervehicle' and  b.TableName = 'startsperdaypervehicle';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'startsopmodedistribution' and  b.TableName = 'startsopmodedistribution';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'idledayadjust' and  b.TableName = 'idledayadjust';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'idlemodelyeargrouping' and  b.TableName = 'idlemodelyeargrouping';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'idlemonthadjust' and  b.TableName = 'idlemonthadjust';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'totalidlefraction' and  b.TableName = 'totalidlefraction';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'avgspeeddistribution' and  b.TableName = 'avgspeeddistribution';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'imcoverage' and  b.TableName = 'imcoverage';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'onroadretrofit' and  b.TableName = 'onroadretrofit';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'roadtypedistribution' and  b.TableName = 'roadtypedistribution';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'sourcetypeagedistribution' and  b.TableName = 'sourcetypeagedistribution';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'sourcetypeyear' and  b.TableName = 'sourcetypeyear';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'emissionratebyage' and  b.TableName = 'emissionratebyage';
Update CDB_Checks as a, tempb as b set a.count = b.table_rows Where a.tableName = 'hotellinghours' and  b.TableName = 'hotellinghours';

-- Present tables
Update CDB_Checks set testDescription = 'Present'       where `count` is not null;
																	
Update CDB_Checks set testDescription = 'Table likely to be overwritten',
					  `status` = 'Warning'
														where `count` > 0
														and tableName in ('emissionratebyage', 'fuelsupply', 'fuelformulation', 'zonemonthhour');
                                                        
Update CDB_Checks set testDescription = 'Table no longer used as user input',
					  `status` = 'Error'
														where `count` > 0
														and tableName in ('hotellinghours');
                                                        
-- Missing tables
Update CDB_Checks set testDescription = 'Table Missing' where count is null;
Update CDB_Checks set status          = 'Error'         where count is null and tableName not in ('countyyear');
Update CDB_Checks set status          = 'Warning'       where count is null and tableName in ('countyyear');

-- These tables are okay if they are missing
Update CDB_Checks set status = 'todo', testDescription='table added by QA script and should be removed' WHERE count is null and tableName in ('countyyear', 'emissionratebyage', 'hotellinghours');

Delete from CDB_Checks where testDescription = 'Present';


-- ----------------------------------------------------------------------------------------
-- Create missing tables to keep error-checking code from crashing-------------------------
-- Get table schemas from the default database to make sure the schemas are up to date ----
-- ----------------------------------------------------------------------------------------

-- AuditLog is the only input database table that is not in the default database
-- Ideally, we'd like to get this via SOURCE "database/CreateAuditLogTables.sql" but that isn't working at the moment
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;

-- geography
CREATE TABLE IF NOT EXISTS `county` LIKE ##defaultdb##.county;
CREATE TABLE IF NOT EXISTS `countyyear` LIKE ##defaultdb##.countyyear;
CREATE TABLE IF NOT EXISTS `state` LIKE ##defaultdb##.state;
CREATE TABLE IF NOT EXISTS `zone` LIKE ##defaultdb##.zone;
CREATE TABLE IF NOT EXISTS `zonemonthhour` LIKE ##defaultdb##.zonemonthhour;
CREATE TABLE IF NOT EXISTS `zoneroadtype` LIKE ##defaultdb##.zoneroadtype;
CREATE TABLE IF NOT EXISTS `year` LIKE ##defaultdb##.year;

-- fuels
CREATE TABLE IF NOT EXISTS `avft` LIKE ##defaultdb##.avft;
CREATE TABLE IF NOT EXISTS `fuelformulation` LIKE ##defaultdb##.fuelformulation;
CREATE TABLE IF NOT EXISTS `fuelsupply` LIKE ##defaultdb##.fuelsupply;
CREATE TABLE IF NOT EXISTS `fuelusagefraction` LIKE ##defaultdb##.fuelusagefraction;

-- vmt
CREATE TABLE IF NOT EXISTS `hourvmtfraction` LIKE ##defaultdb##.hourvmtfraction;
CREATE TABLE IF NOT EXISTS `dayvmtfraction` LIKE ##defaultdb##.dayvmtfraction;
CREATE TABLE IF NOT EXISTS `monthvmtfraction` LIKE ##defaultdb##.monthvmtfraction;
CREATE TABLE IF NOT EXISTS `hpmsvtypeday` LIKE ##defaultdb##.hpmsvtypeday;
CREATE TABLE IF NOT EXISTS `hpmsvtypeyear` LIKE ##defaultdb##.hpmsvtypeyear;
CREATE TABLE IF NOT EXISTS `sourcetypedayvmt` LIKE ##defaultdb##.sourcetypedayvmt;
CREATE TABLE IF NOT EXISTS `sourcetypeyearvmt` LIKE ##defaultdb##.sourcetypeyearvmt;

-- hotelling
CREATE TABLE IF NOT EXISTS `hotellingactivitydistribution` LIKE ##defaultdb##.hotellingactivitydistribution;
CREATE TABLE IF NOT EXISTS `hotellingagefraction` LIKE ##defaultdb##.hotellingagefraction;
CREATE TABLE IF NOT EXISTS `hotellinghoursperday` LIKE ##defaultdb##.hotellinghoursperday;
CREATE TABLE IF NOT EXISTS `hotellinghourfraction` LIKE ##defaultdb##.hotellinghourfraction;
CREATE TABLE IF NOT EXISTS `hotellingmonthadjust` LIKE ##defaultdb##.hotellingmonthadjust;

-- starts
CREATE TABLE IF NOT EXISTS `starts` LIKE ##defaultdb##.`starts`;
CREATE TABLE IF NOT EXISTS `startsageadjustment` LIKE ##defaultdb##.startsageadjustment;
CREATE TABLE IF NOT EXISTS `startshourfraction` LIKE ##defaultdb##.startshourfraction;
CREATE TABLE IF NOT EXISTS `startsmonthadjust` LIKE ##defaultdb##.startsmonthadjust;
CREATE TABLE IF NOT EXISTS `startsperday` LIKE ##defaultdb##.startsperday;
CREATE TABLE IF NOT EXISTS `startsperdaypervehicle` LIKE ##defaultdb##.startsperdaypervehicle;
CREATE TABLE IF NOT EXISTS `startsopmodedistribution` LIKE ##defaultdb##.startsopmodedistribution;

-- idle
CREATE TABLE IF NOT EXISTS `idledayadjust` LIKE ##defaultdb##.idledayadjust;
CREATE TABLE IF NOT EXISTS `idlemodelyeargrouping` LIKE ##defaultdb##.idlemodelyeargrouping;
CREATE TABLE IF NOT EXISTS `idlemonthadjust` LIKE ##defaultdb##.idlemonthadjust;
CREATE TABLE IF NOT EXISTS `totalidlefraction` LIKE ##defaultdb##.totalidlefraction;

-- other input tables
CREATE TABLE IF NOT EXISTS `avgspeeddistribution` LIKE ##defaultdb##.avgspeeddistribution;
CREATE TABLE IF NOT EXISTS `imcoverage` LIKE ##defaultdb##.imcoverage;
CREATE TABLE IF NOT EXISTS `onroadretrofit` LIKE ##defaultdb##.onroadretrofit;
CREATE TABLE IF NOT EXISTS `roadtypedistribution` LIKE ##defaultdb##.roadtypedistribution;
CREATE TABLE IF NOT EXISTS `sourcetypeagedistribution` LIKE ##defaultdb##.sourcetypeagedistribution;
CREATE TABLE IF NOT EXISTS `sourcetypeyear` LIKE ##defaultdb##.sourcetypeyear;
CREATE TABLE IF NOT EXISTS `emissionratebyage` LIKE ##defaultdb##.emissionratebyage;


-- ##############################################################################
-- Start data QA checks
-- ##############################################################################

--       check no. 1001 -- check that one and only one of HPMSVtypeYear, HPMSVtypeDay, SourceTypeYearVMT, SourceTypeDayVMT has at least 1 row
INSERT INTO QA_Checks_Log values ( 1001, 'OK', @hVersion, curDate(), curTime() );   
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue,
   `count`   )
 SELECT
  "VMT Tables" as TableName,
   1001											  as checkNumber,
  "# VMT tables with data <> 1"                       as TestDescription,
  GROUP_CONCAT(DISTINCT tableName SEPARATOR ', ') as testValue,
  sum(tableIsUsed)                                as `count`
from (
	select distinct 'hpmsvtypeday' as tableName, 1 as tableIsUsed from `hpmsvtypeday`
	UNION
	select distinct 'hpmsvtypeyear' as tableName, 1 as tableIsUsed from `hpmsvtypeyear`
	UNION
	select distinct 'sourcetypedayvmt' as tableName, 1 as tableIsUsed from `sourcetypedayvmt`
	UNION
	select distinct 'sourcetypeyearvmt' as tableName, 1 as tableIsUsed from `sourcetypeyearvmt`
) as t1
having `count` <> 1;

--       check no. 1002 -- Record number of rows in HPMSVTypeDay
INSERT INTO QA_Checks_Log values ( 1002, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue,
   msgtype   )
 values
 ("hpmsVTypeDay",
   1002,
  "Number of Rows",
  (Select count(*) from hpmsVTypeDay),
  "Info" );

--       check no. 1003 -- Record number of rows in HPMSVtypeYear
INSERT INTO QA_Checks_Log values ( 1003, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue,
   msgtype   )
 values
 ("hpmsVTypeYear",
   1003,
  "Number of Rows",
  (Select count(*) from hpmsVTypeYear),
  "Info" );

--       check no. 1004 -- Record number of rows in SourceTypeDayVMT
INSERT INTO QA_Checks_Log values ( 1004, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue,
   msgtype   )
 values
 ("sourceTypeDayVmt",
   1004,
  "Number of Rows",
  (Select count(*) from sourceTypeDayVmt), 
  "Info" );

--       check no. 1005 -- Record number of rows in SourceTypeYearVMT
INSERT INTO QA_Checks_Log values ( 1005, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue,
   msgtype   )
 values
 ("sourceTypeYearVmt",
   1005,
  "Number of Rows",
  (Select count(*) from sourceTypeYearVmt),
  "Info" );

--       check no. 1006 -- Check that 0 or 1 total of startsPerDay or startsPerDayPerVehicle or starts are included
INSERT INTO QA_Checks_Log values ( 1006, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue,
   `count`   )
 SELECT
  "starts OR startsPerDay OR startsPerDayPerVehicle" as TableName,
   1006												 as checkNumber,
  "Number of starts tables used"                     as TestDescription,
  GROUP_CONCAT(DISTINCT tableName SEPARATOR ', ')    as testValue,
  sum(tableIsUsed)                                   as `count`
from (
	select distinct 'starts' as tableName, 1 as tableIsUsed from `starts`
	UNION
	select distinct 'StartsPerDay' as tableName, 1 as tableIsUsed from `StartsPerDay`
	UNION
	select distinct 'StartsPerDayPerVehicle' as tableName, 1 as tableIsUsed from `StartsPerDayPerVehicle`
) as t1
having `count` > 1;

--       check no. 1007 -- Record number of rows in starts
INSERT INTO QA_Checks_Log values ( 1007, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue,
   msgtype   )
 values
 ("starts",
   1007,
  "Number of Rows",
  (Select count(*) from `starts`),
  "Info" );

--       check no. 1008 -- Record number of rows in StartsPerDay
INSERT INTO QA_Checks_Log values ( 1008, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue,
   msgtype   )
 values
 ("StartsPerDay",
   1008,
  "Number of Rows",
  (Select count(*) from `StartsPerDay`),
  "Info" );

--       check no. 1009 -- Record number of rows in StartsPerDayPerVehicle
INSERT INTO QA_Checks_Log values ( 1009, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue,
   msgtype   )
 values
 ("StartsPerDayPerVehicle",
   1009,
  "Number of Rows",
  (Select count(*) from `StartsPerDayPerVehicle`),
  "Info" );


-- year
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (1100, "year", "Table Check:");

--       check no. 1101 -- check that isBaseYear is either Y, N, y, n
INSERT INTO QA_Checks_Log values ( 1101, 'OK', @hVersion, curDate(), curTime() );
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', 'y', 'n');

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

--       check no. 1102 -- check that fuelYearID is the same as yearID in this table
INSERT INTO QA_Checks_Log values ( 1102, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count     )
Select   "year"                          as tableName,
         1102                             as checkNumber,
        "fuelYearId not equal to yearId" as testDescription,
         fuelYearId2                     as testValue,
         n                               as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 1103 -- check for unknown yearIDs
INSERT INTO QA_Checks_Log values ( 1103, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   yearId       as yearId2,
         'no '        as aMatch,
         count(*)     as n
From     year
Group by yearId2;
							  
Update tempA as a 
inner join ##defaultdb##.year as m on a.yearId2 = m.yearId
set aMatch='yes';

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

--       check no. 1104 -- check that yearID matches the name of the database (cXXXXXyYYYY)
INSERT INTO QA_Checks_Log values ( 1104, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists 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 mid((select DATABASE() from dual), 7, 5) = CONCAT('y', yearID2);

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "year"   as tableName,
         1104,
        "yearID doesn't match database name"  as testDescription,
         yearId2  as testValue,
         n        as count
From     tempA
Where    aMatch <> 'yes';

-- state
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (1200, "state", "Table Check:");

--       check no. 1201 -- check for unknown stateID
INSERT INTO QA_Checks_Log values ( 1201, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.state as m on a.stateId2 = m.stateId
set aMatch='yes';

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

--       check no. 1202 -- check for unknown idleRegionID
INSERT INTO QA_Checks_Log values ( 1202, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   idleRegionID as idleRegionID2,
         'no '        as aMatch,
         count(*)     as n
From     state
Group by idleRegionID2;

Update tempA as a 
inner join ##defaultdb##.idleregion as m on a.idleRegionID2 = m.idleRegionID
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "state"             as tableName,
         1202,
         "idleRegionID not valid" as testDescription,
         idleRegionID2       as testValue,
         n                   as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 1203 -- check that state has at least 1 row
INSERT INTO QA_Checks_Log values ( 1203, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("state",
   1203,
  "Number of Rows",
  (Select count(*) from state) );
Delete from CDB_Checks where checkNumber=1203 and testValue>0;


-- Check for the county table
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (1300, "county", "Table Check:");

--       check no. 1301: check for unknown countyIDs
INSERT INTO QA_Checks_Log values ( 1301, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.county as m on a.countyId2 = m.countyId
set aMatch='yes';

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

--       check no. 1302 -- check that countyID matches the name of the database (cXXXXXyYYYY)
INSERT INTO QA_Checks_Log values ( 1302, 'OK', @hVersion, curDate(), curTime() );
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 convert(mid((select DATABASE() from dual), 2, 5), UNSIGNED) = countyID2;

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "county"   as tableName,
         1302,
        "countyID doesn't match database name"  as testDescription,
         countyID2  as testValue,
         n        as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 1303: check to make sure the altitude field is L or H
INSERT INTO QA_Checks_Log values ( 1303, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         -- testValue,
         count  )
Select   "county"              as tableName,
         1303,
         "altitude not L or H" as testDescription,
         -- altitude2          as testValue,
         n                     as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 1304: make sure GPAFract is between 0 and 1
INSERT INTO QA_Checks_Log values ( 1304, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "county"       as tableName,
         1304,
         "GPACFract"    as testDescription,
         GPAFract2      as testValue,
         n              as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 1305: make sure the barometricPressure field is between 20 and 33
INSERT INTO QA_Checks_Log values ( 1305, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "county"             as tableName,
         1305,
         "barometricPressure" as testDescription,
         BarPre               as testValue,
         n                    as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 1306: check for unknown stateIDs
INSERT INTO QA_Checks_Log values ( 1306, 'OK', @hVersion, curDate(), curTime() );
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 
inner join state as c on a.stateId2 = c.stateId
set aMatch='yes';

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

--       check no. 1307 -- check that county has at least 1 row
INSERT INTO QA_Checks_Log values ( 1307, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("county",
   1307,
  "Number of Rows",
  (Select count(*) from county) );
Delete from CDB_Checks where checkNumber=1307 and testValue>0;

--       check no. 1308 -- check for unknown countyTypeID
INSERT INTO QA_Checks_Log values ( 1308, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   countyTypeID as countyTypeID2,
         'no '        as aMatch,
         count(*)     as n
From     county
Group by countyTypeID2;

Update tempA as a 
inner join ##defaultdb##.countytype as m on a.countyTypeID2 = m.countyTypeID
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "county"             as tableName,
         1308,
         "countyTypeID not valid" as testDescription,
         countyTypeID2       as testValue,
         n                   as count
From     tempA
Where    aMatch <> 'yes';


-- Zone
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (1400, "zone", "Table Check:");

--       check no. 1401 -- check for unknown countyIDs
INSERT INTO QA_Checks_Log values ( 1401, 'OK', @hVersion, curDate(), curTime() );
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 
inner join county as c on a.countyId2 = c.countyId
set aMatch='yes';

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

--       check no. 1402 -- check that the startAllocFactor sums to 1
INSERT INTO QA_Checks_Log values ( 1402, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue    )
Select  "zone"                           as tableName,
         1402                            as checkNumber,
        "sum of startAllocFactor <> 1.0" as testDescription,
         sum(startAllocFactor)           as testValue
From     zone
Having   testValue <0.99999 or testValue >1.00001;

--       check no. 1403 -- checks that the idleAllocFactor sums to 1
INSERT INTO QA_Checks_Log values ( 1403, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue    )
Select  "zone"                          as tableName,
         1403                            as checkNumber,
        "sum of idleAllocFactor <> 1.0" as testDescription,
         sum(idleAllocFactor)           as testValue
From     zone
Having   testValue <0.99999 or testValue >1.00001;

--       check no. 1404 -- checks that the SHPAllocFactor sums to 1
INSERT INTO QA_Checks_Log values ( 1404, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue    )
Select  "zone"                         as tableName,
         1404                           as checkNumber,
        "sum of SHPAllocFactor <> 1.0" as testDescription,
         sum(SHPAllocFactor)           as testValue
From     zone
Having   testValue <0.99999 or testValue >1.00001;

--       check no. 1405 -- check that zoneID is consistent with countyID
INSERT INTO QA_Checks_Log values ( 1405, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zone"   as tableName,
         1405	  as CheckNumber,
        "zoneId does not match countyID*10"  as testDescription,
         zoneId   as testValue,
         count(*) as count
From     zone
Group By zoneID, countyID
Having   zoneID <> countyID * 10;

--       check no. 1406 -- check that zone has at least 1 row
INSERT INTO QA_Checks_Log values ( 1406, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("zone",
   1406,
  "Number of Rows",
  (Select count(*) from zone) );
Delete from CDB_Checks where checkNumber=1406 and testValue>0;


--       Table  Check: avft
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (1500, "avft", "Table Check:");

--       check no. 1501: check for unknown sourceTypeIDs (e.g., 22)
INSERT INTO QA_Checks_Log values ( 1501, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';										  
										  
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "avft"        as tableName,
         1501,
        "sourceTypeId" as testDescription,
         sourceTypeId2 as testValue,
         n             as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 1502: Check for unknown modelYearIDs (e.g., 2061)
INSERT INTO QA_Checks_Log values ( 1502, 'OK', @hversion, curDate(), curTime() );
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 
inner join ##defaultdb##.modelYear as m on a.modelYearId2 = m.modelYearId
set aMatch='yes';										  

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

--       check no. 1503: check for unknown fuelTypeIDs (e.g., 10)
INSERT INTO QA_Checks_Log values ( 1503, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.fuelType as m on a.fuelTypeId2 = m.fuelTypeId
set aMatch='yes';										  

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

--       check no. 1504: check for unknown engTechIDs
INSERT INTO QA_Checks_Log values ( 1504, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.engineTech as m on a.engTechId2 = m.engTechId
set aMatch='yes';		

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

--       check no. 1505: check for bad fuelEngFractions in avft
--                       allow fractions up to 1.00001 because if you export defaults, MOVES is doing joins
--                       in the background, and it is plausible that it would end up with floating point noise
--                       above 1 for an individual row.
INSERT INTO QA_Checks_Log values ( 1505, 'OK', @hVersion, curDate(), curTime() );
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 between 0.0 and 1.00001;

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

--       check no. 1506 -- check that the fuelEngFracion distributions of AVFT table sums to 1
Drop table if exists tempA;
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.99999 and distribution < 1.00001;

INSERT INTO QA_Checks_Log values ( 1506, 'OK', @hVersion, curDate(), curTime() );
  Insert into CDB_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue
       )
  Select 'avft'                 as tableName,
          1506                   as checkNumber,
         'Sum <> 1.0'           as testDescription,
         ( select count(*)
           from   tempA
           where  distribution <> 1.0 ) as testValue;
delete from CDB_Checks where checkNumber = 1506 and testValue = 0;
Drop table if exists tempA;

--       check no. 1507: check for missing source type, fuel type, and model year combinations
-- 		 Note: this completeness check is different from others in that not all combinations of 
-- 		 source type, fuel type, and model year are valid (i.e., no diesel motorcycles).
-- 		 So this table checks for completeness vs. samplevehiclepopulation, which contains this definition
--       Also, only check for the existence of modelyearids that will appear in the run (according to the year table)
INSERT INTO QA_Checks_Log values ( 1507, 'OK', @hVersion, curDate(), curTime() );
  Insert into CDB_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue
       )
  Select 'avft'                 as tableName,
          1507                  as checkNumber,
         'Missing combination of valid sourceTypeID, fuelTypeID, and modelYearID'           as testDescription,
		 concat('ST: ', sourceTypeID, ', FT: ', fuelTypeID, ', MY: ', modelyearID) as testValue
  from (select distinct sourceTypeID, fuelTypeID, modelYearID
		from ##defaultdb##.samplevehiclepopulation
        join `year`
        where modelYearID between yearID-30 and yearID) as t1
  left join avft using (sourceTypeID, fuelTypeID, modelYearID)
	where fuelEngFraction is NULL 
	ORDER BY sourceTypeID, fuelTypeID, modelYearID LIMIT 1;


--       Table  Check: avgspeeddistribution
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (1600, "avgspeeddistribution", "Table Check:");

--       check no. 1601: checks for unknown avgSpeedBinIDs (e.g., 17)
INSERT INTO QA_Checks_Log values ( 1601, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   avgSpeedBinId   as avgSpeedBinId2,
         'no '           as aMatch,
         count(*)        as n
From     avgSpeedDistribution
Group by avgSpeedBinId2;

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

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

--       check no. 1602: make sure the avgSpeedFraction sums to 1 for each sourceTypeID, roadTypeID, and hourDayID
INSERT INTO QA_Checks_Log values ( 1602, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
           sourceTypeId,
           roadTypeId,
           hourDayId )
Select  "avgspeeddistribution"           as tableName,
         1602                              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.99999 or testValue >1.00001;

--       check no. 1603: checks for unknown hourDayIDs
INSERT INTO QA_Checks_Log values ( 1603, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.hourDay as m on a.hourDayId2 = m.hourDayId
set aMatch='yes';	

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

--       check no. 1604: check for unknown roadTypeIDs
INSERT INTO QA_Checks_Log values ( 1604, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.roadType as m on a.roadTypeId2 = m.roadTypeId
set aMatch='yes';	

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

--       check no. 1605: check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 1605, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';	

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

--       check no. 1606: check for missing sourceTypeID, roadTypeID, hourDayID, avgSpeedBinID combinations
INSERT INTO QA_Checks_Log values ( 1606, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue)
Select   "avgSpeedDistribution" as tableName,
         1606,
         'Missing combination of valid sourceTypeID, roadTypeID, hourDayID, avgSpeedBinID' as testDescription,
		 concat('ST: ', sourceTypeID, ', RT: ', roadTypeID, ', HD: ', hourDayID, ', SB: ', avgSpeedBinID) as testValue
from (
	SELECT sourceTypeID, roadTypeID, hourDayID, avgSpeedBinID
	FROM  ##defaultdb##.sourceusetype
	CROSS JOIN ##defaultdb##.roadtype on roadTypeID in (2, 3, 4, 5)
	CROSS JOIN ##defaultdb##.hourday
	CROSS JOIN ##defaultdb##.avgspeedbin
) as t1 left join avgspeeddistribution using (sourceTypeID, roadTypeID, hourDayID, avgSpeedBinID)
where avgSpeedFraction is NULL 
ORDER BY sourceTypeID, roadTypeID, hourDayID, avgSpeedBinID LIMIT 1;

--       check no. 1607: make sure no fractions are 1
INSERT INTO QA_Checks_Log values ( 1607, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue)
Select   "avgSpeedDistribution" as tableName,
         1607,
         'avgSpeedFraction >= 1' as testDescription,
		 concat('ST: ', sourceTypeID, ', RT: ', roadTypeID, ', HD: ', hourDayID,
                ', SB: ', avgSpeedBinID, ', avgSpeedFraction = ', avgSpeedFraction) as testValue
FROM  avgspeeddistribution
where avgSpeedFraction >= 1.0
ORDER BY sourceTypeID, roadTypeID, hourDayID, avgSpeedBinID LIMIT 1;

--       check no. 1608: make sure no profiles are flat
INSERT INTO QA_Checks_Log values ( 1608, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         sourceTypeID,
         roadTypeID,
         hourDayID)
Select   "avgSpeedDistribution" as tableName,
         1608 as checkNumber,
         'avgSpeedFraction is a flat profile' as testDescription,
         concat('all are ', avgSpeedFraction) as testValue,
		 sourceTypeID,
         roadTypeID,
         hourDayID
from avgspeeddistribution
where roadTypeID not in (1, 100)
group by sourceTypeID, roadTypeID, hourDayID, avgSpeedFraction
having count(*) = (select count(*) from ##defaultdb##.avgspeedbin)
order by sourceTypeID, roadTypeID, hourDayID, avgSpeedBinID LIMIT 1;

--       check no. 1609: make sure weekend and weekday profiles are different
INSERT INTO QA_Checks_Log values ( 1609, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         sourceTypeID,
         roadTypeID,
         hourID)
Select   "avgSpeedDistribution" as tableName,
         1609 as checkNumber,
         'avgSpeedFraction is the same between weekend and weekday' as testDescription,
		 sourceTypeID,
         roadTypeID,
         hourID
from (select sourceTypeID, roadTypeID, hourID, avgSpeedBinID, avgSpeedFraction as weekendFraction
	  from avgspeeddistribution
	  join ##defaultdb##.hourday using (hourDayID)
	  where dayID = 2 and roadTypeID not in (1, 100)) as we
join (select sourceTypeID, roadTypeID, hourID, avgSpeedBinID, avgSpeedFraction as weekdayFraction
	  from avgspeeddistribution
	  join ##defaultdb##.hourday using (hourDayID)
	  where dayID = 5 and roadTypeID not in (1, 100)) as wd using (sourceTypeID, roadTypeID, hourID, avgSpeedBinID)
group by sourceTypeID, roadTypeID, hourID
having sum(abs(weekendFraction - weekdayFraction)) < 0.00001
order by sourceTypeID, roadTypeID, hourID LIMIT 1;

--       check no. 1610: make sure weekend and weekday profiles are different
--  	 compare distributions by sourceTypeID and hourDayID for each road type pair 
--  	 by summing the absolute differences by avgSpeedBinID, and anywhere the sum
--       is 0, the distributions are identical
INSERT INTO QA_Checks_Log values ( 1610, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         sourceTypeID,
         hourDayID)
Select   "avgSpeedDistribution" as tableName,
         1610 as checkNumber,
         'avgSpeedFraction is the same between at least two road types' as testDescription,
		 sourceTypeID,
         hourDayID
from (select sourceTypeID, hourDayID, avgSpeedBinID, avgSpeedFraction as rt2Fraction
	  from avgspeeddistribution
	  where roadTypeID = 2) as rt2
join (select sourceTypeID, hourDayID, avgSpeedBinID, avgSpeedFraction as rt3Fraction
	  from avgspeeddistribution
	  where roadTypeID = 3) as rt3 using (sourceTypeID, hourDayID, avgSpeedBinID)
join (select sourceTypeID, hourDayID, avgSpeedBinID, avgSpeedFraction as rt4Fraction
	  from avgspeeddistribution
	  where roadTypeID = 4) as rt4 using (sourceTypeID, hourDayID, avgSpeedBinID)
join (select sourceTypeID, hourDayID, avgSpeedBinID, avgSpeedFraction as rt5Fraction
	  from avgspeeddistribution
	  where roadTypeID = 5) as rt5 using (sourceTypeID, hourDayID, avgSpeedBinID)
group by sourceTypeID, hourDayID
HAVING sum(abs(rt2Fraction - rt3Fraction)) < 0.00001 or
	   sum(abs(rt2Fraction - rt4Fraction)) < 0.00001 or 
	   sum(abs(rt2Fraction - rt5Fraction)) < 0.00001 or 
	   sum(abs(rt3Fraction - rt4Fraction)) < 0.00001 or 
	   sum(abs(rt3Fraction - rt5Fraction)) < 0.00001 or 
	   sum(abs(rt4Fraction - rt5Fraction)) < 0.00001
LIMIT 1;

--       check no. 1611: check for 0% speed distributions in speed bin 1
INSERT INTO QA_Checks_Log values ( 1611, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         sourceTypeID,
         roadTypeID,
         hourDayID)
Select   "avgSpeedDistribution" as tableName,
         1611 as checkNumber,
         'avgSpeedFraction is 0 in avgSpeedBinID 1' as testDescription,
		 sourceTypeID,
         roadTypeID,
         hourDayID
from avgspeeddistribution
where avgSpeedFraction = 0 and avgSpeedBinID = 1 and roadTypeID not in (1, 100)
LIMIT 1;


-- countyYear
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (1700, "countyyear", "Table Check:");

--       check no. 1701 -- check for unknown countyIDs
INSERT INTO QA_Checks_Log values ( 1701, 'OK', @hVersion, curDate(), curTime() );
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 
inner join county as c on a.countyId2 = c.countyId
set aMatch='yes';	

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

--       check no. 1702 -- check for unknown yearIDs
INSERT INTO QA_Checks_Log values ( 1702, 'OK', @hVersion, curDate(), curTime() );
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 
inner join year as c on a.yearId2 = c.yearId
set aMatch='yes';	

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

--       check no. 1703 -- check that the refuelingVaporProgramAdjust value is between 0 and 1
INSERT INTO QA_Checks_Log values ( 1703, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "countyYear"                 as tableName,
         1703,
        "refuelingVaporProgramAdjust" as testDescription,
         refuelingVaporProgramAdjust2 as testValue,
         n       as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 1704 -- check that the refuelingSpillProgramAdjust value is between 0 and 1
INSERT INTO QA_Checks_Log values ( 1704, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "countyYear"                 as tableName,
         1704,
        "refuelingSpillProgramAdjust" as testDescription,
         refuelingSpillProgramAdjust2 as testValue,
         n       as count               --
From     tempA
Where    aMatch <> 'yes';


-- dayVMTFraction checks
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (1800, "dayVmtFraction", "Table Check:");

--       check no. 1801: check for unknown dayIDs
INSERT INTO QA_Checks_Log values ( 1801, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.dayOfAnyWeek as m on a.dayId2 = m.dayId
set aMatch='yes';

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

--       check no. 1802: check that dayVMTFraction sums to 1 for each source type, road type, and month for the onroad road types
INSERT INTO QA_Checks_Log values ( 1802, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
           sourceTypeId,
           monthId,
           roadTypeId )
Select  "dayVmtFraction"               as tableName,
         1802                          as checkNumber,
        "sum of dayVMTFraction <> 1.0" as testDescription,
         sum(dayVMTFraction)           as testValue,
           sourceTypeId,
           monthId,
           roadTypeId
From     dayVmtFraction
Where    roadTypeId in (2,3,4,5)
Group by sourceTypeId,
         monthId,
         roadTypeId
Having   testValue <0.99999 or testValue >1.00001;

--       check no. 1803: check for unknown monthIDs
INSERT INTO QA_Checks_Log values ( 1803, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.monthOfAnyYear as m on a.monthId2 = m.monthId
set aMatch='yes';

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

--       check no. 1804: check for unknown roadTypeIDs
INSERT INTO QA_Checks_Log values ( 1804, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   roadTypeId   as roadTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     dayVmtFraction
Group by roadTypeId2;

Update tempA as a 
inner join ##defaultdb##.roadType as m on a.roadTypeId2 = m.roadTypeId
set aMatch='yes';

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

--       check no. 1805: check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 1805, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';	

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

--       check no. 1806: make sure no fractions are 1
INSERT INTO QA_Checks_Log values ( 1806, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue)
Select   "dayVMTFraction" as tableName,
         1806,
         'dayVMTFraction >= 1' as testDescription,
		 concat('ST: ', sourceTypeID, ', Month: ', monthID, ', RT: ', roadTypeID,
                ', Day: ', dayID, ', dayVMTFraction = ', dayVMTFraction) as testValue
FROM  dayvmtfraction
where dayVMTFraction >= 1.0
ORDER BY sourceTypeID, monthID, roadTypeID, dayID LIMIT 1;

--       check no. 1807: make sure no profiles are flat
INSERT INTO QA_Checks_Log values ( 1807, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
          sourceTypeID, monthID, roadTypeID)
Select   "dayVMTFraction" as tableName,
         1807 as checkNumber,
         'dayVMTFraction is a flat profile' as testDescription,
         'dayID 2 is 2/7 and dayID 5 is 5/7' as testValue,
         sourceTypeID, monthID, roadTypeID
from dayvmtfraction
where abs(dayVMTFraction - dayID/7) < 0.00001 
  and roadTypeID not in (1, 100)
order by sourceTypeID, monthID, roadTypeID LIMIT 1;

--       check no. 1808: check for missing sourceTypeID, monthID, roadTypeID, dayID combinations
--                       only if dayVMTFraction is used
INSERT INTO QA_Checks_Log values ( 1808, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         sourceTypeID, monthID, roadTypeID, dayID)
Select   "dayvmtfraction" as tableName,
         1808,
         'Missing combination of valid sourceTypeID, monthID, roadTypeID, dayID' as testDescription,
		 sourceTypeID, monthID, roadTypeID, dayID
from (
	SELECT sourceTypeID, monthID, roadTypeID, dayID
	FROM  ##defaultdb##.sourceusetype
	CROSS JOIN ##defaultdb##.monthOfAnyYear
	CROSS JOIN ##defaultdb##.roadtype
	CROSS JOIN ##defaultdb##.dayOfAnyWeek
    where roadTypeID in (2, 3, 4, 5)
) as t1 
left join dayvmtfraction using (sourceTypeID, monthID, roadTypeID, dayID)
join (select count(*) as n from dayvmtfraction) as t2
where dayVMTFraction is NULL and n > 0
ORDER BY sourceTypeID, monthID, roadTypeID, dayID LIMIT 1;


-- emissionRateByAge
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (1900, "emissionRateByAge", "Table Check:");

--       check no. 1901 -- check for unknown polProcessIDs
INSERT INTO QA_Checks_Log values ( 1901, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.emissionRateByAge as m on a.polProcessId2 = m.polProcessId
set aMatch='yes';	

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

--       check no. 1902 -- check for unknown opModeIDs
INSERT INTO QA_Checks_Log values ( 1902, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.operatingmode as m on a.opModeId2 = m.opModeId
set aMatch='yes';	

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

--       check no. 1903 -- check for unknown ageGroupIDs
INSERT INTO QA_Checks_Log values ( 1903, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.ageGroup as m on a.ageGroupId2 = m.ageGroupId
set aMatch='yes';	

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

-- Checks for fuelformulation
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (2000, "fuelFormulation", "Table Check:");

--       check no. 2001: check for unknown fuelSubTypeIDs
INSERT INTO QA_Checks_Log values ( 2001, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.fuelSubType as m on a.fuelSubTypeId2 = m.fuelSubTypeId
set aMatch='yes';

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

--       check no. 2002: checks for RVP between 5 and 20 for gasoline subtypes (not including E85)
INSERT INTO QA_Checks_Log values ( 2002, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         2002,
         "RVP"             as testDescription,
         RVP2              as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 2003: check for valid sulfur levels between 0 and 5000
INSERT INTO QA_Checks_Log values ( 2003, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         2003,
         "sulfurLevel"     as testDescription,
         sulfurLevel2      as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 2004: check for an ETOHVolume between 0 and 100 for all fuels
INSERT INTO QA_Checks_Log values ( 2004, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         2004,
         "ETOHVolume"      as testDescription,
         ETOHVolume2       as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 2005: checks that MTBEVolume is 0 or NULL
INSERT INTO QA_Checks_Log values ( 2005, 'OK', @hVersion, curDate(), curTime() );
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 is NULL OR MTBEVolume2=0;

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

--       check no. 2006: Check that ETBEVolume is 0 or NULL
INSERT INTO QA_Checks_Log values ( 2006, 'OK', @hVersion, curDate(), curTime() );
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 is NULL or ETBEVolume2=0;

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

--       check no. 2007: check that TAMEVolume is 0 or NULL
INSERT INTO QA_Checks_Log values ( 2007, 'OK', @hVersion, curDate(), curTime() );
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 is NULL or TAMEVolume2=0;

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

--       check no. 2008: check for aromaticContent between 0 and 55 for gasoline subtypes
INSERT INTO QA_Checks_Log values ( 2008, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         2008,
         "aromaticContent" as testDescription,
         aromaticContent2  as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 2009: check for olefinContent between 0 and 25 for gasoline subtypes
INSERT INTO QA_Checks_Log values ( 2009, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         2009,
         "olefinContent"   as testDescription,
         olefinContent2    as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 2010: check for benzeneContent between 0 and 5 for gasoline subtypes
INSERT INTO QA_Checks_Log values ( 2010, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         2010,
         "benzeneContent"  as testDescription,
         benzeneContent2   as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 2011: check for e200 between 0 and 70 for gasoline subtypes
INSERT INTO QA_Checks_Log values ( 2011, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         2011,
         "e200"            as testDescription,
         e2002             as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 2012: check for e300 between 0 and 100 for gasoline subtypes
INSERT INTO QA_Checks_Log values ( 2012, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelFormulation" as tableName,
         2012,
         "e300"            as testDescription,
         e3002             as testValue,
         n                 as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 2013 -- check that the T50/T90 columns exist
INSERT INTO QA_Checks_Log values ( 2013, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
 ( TableName,
   checkNumber,
   TestDescription,
   testValue )
 values
 ("fuelFormulation",
   2013,
  "T50 and/or T90 Missing",
  (Select count(*)
   from   information_schema.columns
   where  table_name   = 'fuelformulation'
     and  column_name in ('t50', 't90')
     and  table_schema = database()) );
Delete from CDB_Checks where checkNumber=2013 and testValue=2;

-- fuelsupply checks
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (2100, "fuelSupply", "Table Check:");

--       check no. 2101: check for unknown fuelFormulationIDs
INSERT INTO QA_Checks_Log values ( 2101, 'OK', @hVersion, curDate(), curTime() );
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 
inner join fuelformulation as c on a.fuelFormulationID2 = c.fuelFormulationID
set aMatch='yes';

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

--       check no. 2102: check for unknown fuelYearIDs
INSERT INTO QA_Checks_Log values ( 2102, 'OK', @hVersion, curDate(), curTime() );
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 
inner join year as c on a.fuelYearId2 = c.fuelYearId
set aMatch='yes';	

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

--       check no. 2103 -- check for unknown monthGroupIDs
INSERT INTO QA_Checks_Log values ( 2103, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.monthGroupOfAnyYear as m on a.monthGroupId2 = m.monthGroupId
set aMatch='yes';

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

--       check no. 2104 -- check for multiple fuelRegionIDs
INSERT INTO QA_Checks_Log values ( 2104, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         `count`  )
Select   "fuelSupply"   as tableName,
         2104,
        "Multiple fuelRegionIDs"  as testDescription,
         count(distinct fuelRegionID)              as `count`
from fuelsupply
having `count` > 1;


-- fuelusagefraction
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (2200, "fuelUsageFraction", "Table Check:");

--       check no. 2201 -- check for unknown fuelYearIDs
INSERT INTO QA_Checks_Log values ( 2201, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA ;
Create table tempA
select  fuelYearId,
       'no '    as aMatch,
       count(*) as n
from   fuelUsageFraction
group by fuelYearId;

Update tempA as a 
inner join year as c on a.fuelYearId = c.fuelYearId
set aMatch='yes';	


Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "fuelUsageFraction" as tableName,
         2201                 as checkNumber,
        "fuelYearId"         as testDescription,
         fuelYearId          as testValue,
         count(*)            as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=2201 and count=0;

--       check no. 2202 -- checks for unknown countyIDs
INSERT INTO QA_Checks_Log values ( 2202, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   countyId,
         'no '           as aMatch,
         count(*)        as n
From     fuelUsageFraction
group by countyId;

Update tempA as a 
inner join county as c on a.countyId = c.countyId
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count )
Select   "fuelUsageFraction"  as tableName,
         2202                  as checkNumber,
        'countyId'            as testDescription,
         countyId             as testValue,
         count(*)             as count
from     tempA
where    aMatch <> 'yes';
Delete from CDB_Checks where checkNumber=2202 and count=0;

--       check no. 2203 -- check for unknown modelYearGroupIDs
INSERT INTO QA_Checks_Log values ( 2203, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   modelYearGroupId,
         'no '           as aMatch,
         count(*)        as n
From     fuelUsageFraction
group by modelYearGroupId;

Update tempA as a 
inner join ##defaultdb##.modelYearGroup as m on a.modelYearGroupId = m.modelYearGroupId
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count )
Select   "fuelUsageFraction"  as tableName,
         2203                  as checkNumber,
        'modelYearGroupId'    as testDescription,
         modelYearGroupId     as testValue,
         count(*)             as count
from     tempA
where    aMatch <> 'yes';
Delete from CDB_Checks where checkNumber=2203 and count=0;

--       check no. 2204 -- check for unknown SourceBinFuelTypeIDs
INSERT INTO QA_Checks_Log values ( 2204, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sourceBinFuelTypeId,
         'no '           as aMatch,
         count(*)        as n
From     fuelUsageFraction
group by sourceBinFuelTypeId;

Update tempA as a 
inner join ##defaultdb##.fuelType as m on a.sourceBinFuelTypeId = m.fuelTypeId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count )
Select   "fuelUsageFraction"  as tableName,
         2204                  as checkNumber,
        'sourceBinFuelTypeId' as testDescription,
         sourceBinFuelTypeId  as testValue,
         count(*)             as count
from     tempA
where    aMatch <> 'yes';
Delete from CDB_Checks where checkNumber=2204 and count=0;

--       check no. 2205 -- checks for unknown fuelSupplyFuelTypeIDs
INSERT INTO QA_Checks_Log values ( 2205, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   fuelSupplyFuelTypeId,
         'no '           as aMatch,
         count(*)        as n
From     fuelUsageFraction
group by fuelSupplyFuelTypeId;

Update tempA as a 
inner join ##defaultdb##.fuelType as m on a.fuelSupplyFuelTypeId = m.fuelTypeId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count )
Select   "fuelUsageFraction"   as tableName,
         2205                   as checkNumber,
        'fuelSupplyFuelTypeId' as testDescription,
         fuelSupplyFuelTypeId  as testValue,
         count(*)              as count
from     tempA
where    aMatch <> 'yes';
Delete from CDB_Checks where checkNumber=2205 and count=0;

--       check no. 2206 -- check that fuelSupplyFuelTypeId must match sourceBinFuelTypeId for non-FFV
INSERT INTO QA_Checks_Log values ( 2206, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   countyId,
         fuelYearId,
         sourceBinFuelTypeId,
         fuelSupplyFuelTypeId,
         count(*) as cou
From     fuelUsageFraction
where    sourceBinFuelTypeId<>5
  and    sourceBinFuelTypeId<>fuelSupplyFuelTypeId
group by countyId,
         fuelYearId,
         sourceBinFuelTypeId;

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         countyId,
         TestDescription,
         testValue,
         fuelYearId,
         count  )
Select  'fuelUsageFraction'  as tableName,
         2206                 as checkNumber,
         countyId            as countyId,
        'fuelTypes Mismatch for non-FFV' as testDescription,
         sourceBinFuelTypeId as testValue,
         fuelYearId          as fuelYearId,
         cou                 as count
from     tempA;
Delete from CDB_Checks where checkNumber=2206 and count=0;

--       check no. 2207 -- check that sourceBinFuelTypeId must = 1 or 5 when fuelSupplyFuelTypeId =5 
INSERT INTO QA_Checks_Log values (2207, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select *,
       count(*) as cou
from (select countyId,
             fuelYearId,
             sourceBinFuelTypeId,
             fuelSupplyFuelTypeId
      from   fuelUsageFraction
      where  sourceBinFuelTypeId=5
        and  fuelSupplyFuelTypeId<>1) as a
where        a.fuelSupplyFuelTypeId<>5
group by     countyId,
             fuelYearId,
             fuelSupplyFuelTypeId;

Insert into CDB_Checks
           ( TableName,
             CheckNumber,
             countyId,
             fuelYearId,
             TestDescription,
             testValue,
             count  )
Select      'fuelUsageFraction'               as tableName,
             2207                              as checkNumber,
             countyId                         as countyId,
             fuelYearId                       as fuelYearId,
            'FFV assigned a fuel other than FT 1 or 5' as testDescription,
             sourceBinFuelTypeId              as testValue,
             cou                              as count
from         tempA;
Delete from CDB_Checks where checkNumber=2207 and count=0;

--       check no. 2208 -- make sure there is a row for sourceBinFuelTypeID = 5 and each of fuelSupplyFuelTypeID = 1 and 5
Insert into CDB_Checks
           ( TableName,
             CheckNumber,
             countyId,
             fuelYearId,
             TestDescription,
             testValue,
             count  )
Select      'fuelUsageFraction'               as tableName,
             2208                              as checkNumber,
             countyId                         as countyId,
             fuelYearId                       as fuelYearId,
            'FFV are missing values for FT 1 or 5' as testDescription,
             CONCAT('modelYearGroupID: ', modelYearGroupID) as testValue,
             count(distinct fuelSupplyFuelTypeID) as count
from fuelusagefraction
where sourceBinFuelTypeID = 5 and fuelSupplyFuelTypeID in (1, 5)
group by countyID, fuelYearID, modelYearGroupID, sourceBinFuelTypeID
having `count` <> 2;

--       check no. 2209 -- checks that usageFraction sums to 1 for all sourceBinFuelTypeIds
INSERT INTO QA_Checks_Log values ( 2209, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   countyId,
	   fuelYearId,
	   modelYearGroupID,
	   sourceBinFuelTypeId,
	   sum(usageFraction) as s
from     fuelUsageFraction
group by countyId,
	   fuelYearId,
	   modelYearGroupID,
	   sourceBinFuelTypeId;
       
Insert into CDB_Checks
           ( TableName,
             CheckNumber,
             TestDescription,
             testValue,
             countyId,
             fuelYearId)
Select      'fuelUsageFraction'   as tableName,
             2209                 as checkNumber,
            'distribution <> 1.0' as testDescription,
             CONCAT('MYG: ', modelYearGroupID, ', sourceBinFuelTypeID: ', sourceBinFuelTypeID, ' sums to ', s) as testValue,
             countyId             as countyId,
             fuelYearId           as fuelYearId
from         tempA
where    s < 0.99999
   or    s > 1.00001;
 

-- hotellingActivityDistribution
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (2300, "hotellingActivityDistribution", "Table Check:");

--       check no. 2301 -- check for unknown opModeID
INSERT INTO QA_Checks_Log values ( 2301, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   opModeId,
         'no '           as aMatch,
         count(*)        as n
From     hotellingActivityDistribution
group by opModeId;

Update tempA as a 
inner join ##defaultdb##.operatingMode as m on a.opModeId = m.opModeId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count )
Select   "hotellingActivityDistribution" as tableName,
         2301                             as checkNumber,
        'opModeId'                       as testDescription,
         opModeId                        as testValue,
         count(*)                        as count
from     tempA
where    aMatch <> 'yes';
Delete from CDB_Checks where checkNumber=2301 and count=0;

--       check no. 2302 -- check that the opModeFraction sums to 1 by model year range
INSERT INTO QA_Checks_Log values ( 2302, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   beginmodelyearId,endmodelyearid,
         sum(opModeFraction) as s,
         count(*)            as cou
From     hotellingActivityDistribution
group by beginmodelyearId,endmodelyearid;

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count    )
Select   "hotellingActivityDistribution" as tableName,
         2302                             as checkNumber,
        'distribution<>1.0'              as testDescription,
         CONCAT(beginmodelyearId, '-', endmodelyearid, ' sums to ', s)               as testValue,
         cou                             as count
from     tempA
where    s<0.99999
   or    s>1.00001;

--       check no. 2303 -- check for unknown zoneID
INSERT INTO QA_Checks_Log values ( 2303, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   zoneID,
         'no '           as aMatch,
         count(*)        as n
From     hotellingActivityDistribution
group by zoneID;

Update tempA as a 
inner join zone as c on a.zoneID = c.zoneID
set aMatch='yes';								

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count )
Select   "hotellingActivityDistribution" as tableName,
         2303                             as checkNumber,
        'zoneID'                       as testDescription,
         zoneID                        as testValue,
         count(*)                        as count
from     tempA
where    aMatch <> 'yes';
Delete from CDB_Checks where checkNumber=2303 and count=0;

--       check no. 2304 -- Check for gaps and overlaps in the model years columns in the hotellingActivityDistribution  table.
INSERT INTO QA_Checks_Log values ( 2304, 'OK', @hVersion, curDate(), curTime() );

-- Add a table to contain the results of the gaps/overlaps check.
Drop   table if exists     qa_checks_had;
Create Table if Not Exists qa_checks_had (
  Czone   int,          -- zoneID
  LENDMY int,          -- last    row's end model year
  CBEGMY int,          -- Current row's beg model year
  CENDMY int,          -- Current row's end model year
  Reason varchar(40) );

-- Call the procedure to check for gaps and overlaps.
call checkHotellingActivityDistribution();

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         count  )
Select  "hotellingActivityDistribution" as tableName,
         2304,
        "gaps and overlaps" as testDescription,
         (Select count(*) from qa_checks_had) as count
From     qa_checks_had
Where    (Select count(*) from qa_checks_had) > 0;


-- hotellingagefraction
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (2400, "hotellingagefraction", "Table Check:");

--       check no. 2401 -- check for unknown zoneID
INSERT INTO QA_Checks_Log values ( 2401, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   zoneID,
         'no '           as aMatch,
         count(*)        as n
From     hotellingagefraction
group by zoneID;

Update tempA as a 
inner join zone as c on a.zoneID = c.zoneID
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count )
Select   "hotellingagefraction" as tableName,
         2401                   as checkNumber,
        'zoneID'                as testDescription,
         zoneID                 as testValue,
         count(*)               as count
from     tempA
where    aMatch <> 'yes';
Delete from CDB_Checks where checkNumber=2401 and count=0;

--       check no. 2402 -- check for unknown ageID
INSERT INTO QA_Checks_Log values ( 2402, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   ageId         as ageId2,
         'no '         as aMatch,
         count(*)      as n
From     hotellingagefraction
Group by ageId2;

Update tempA as a 
inner join ##defaultdb##.ageCategory as m on a.ageId2 = m.ageId
set aMatch='yes';

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

--       check no. 2403 -- check for missing zoneID and ageID combinations (as long as this table has contents)
INSERT INTO QA_Checks_Log values ( 2403, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue)
Select   "hotellingagefraction" as tableName,
         2403,
         'Missing combination of valid zoneID and ageID' as testDescription,
		 concat('Z: ', zoneID, ', age: ', ageID) as testValue
from (
	SELECT zoneID, ageID
	FROM  zone
	CROSS JOIN ##defaultdb##.ageCategory
) as t1 
left join hotellingagefraction using (zoneID, ageID)
join (select count(*) as c from hotellingagefraction) as t2
where ageFraction is NULL and c > 0
ORDER BY zoneID, ageID LIMIT 1;

--       check no. 2404 -- check for ageFractions >= 1
INSERT INTO QA_Checks_Log values ( 2404, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue)
Select   "hotellingagefraction" as tableName,
         2404,
         'ageFraction >= 1' as testDescription,
		 ageFraction as testValue
from hotellingagefraction
where ageFraction >= 1
ORDER BY zoneID, ageID LIMIT 1;

--       check no. 2405 -- check that the distribution sums to 1
INSERT INTO QA_Checks_Log values ( 2405, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         zoneID)
Select   "hotellingagefraction" as tableName,
         2405,
         'distribution <> 1' as testDescription,
		 sum(ageFraction) as testValue,
         zoneID
from hotellingagefraction
GROUP BY zoneID
having testValue < .99999 or testvalue > 1.00001;

--       check no. 2406: make sure age distributions aren't flat
INSERT INTO QA_Checks_Log values ( 2406, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         zoneID)
Select   "hotellingagefraction" as tableName,
         2406 as checkNumber,
         'ageFraction is a flat profile' as testDescription,
         concat('all are ', ageFraction) as testValue,
		 zoneID
from hotellingagefraction
group by zoneID, ageFraction
having count(*) = (select count(*) from ##defaultdb##.agecategory)
order by zoneID LIMIT 1;


-- hotellinghourfraction
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (2500, "hotellinghourfraction", "Table Check:");

--       check no. 2501 -- check for unknown zoneID
INSERT INTO QA_Checks_Log values ( 2501, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   zoneID,
         'no '           as aMatch,
         count(*)        as n
From     hotellinghourfraction
group by zoneID;

Update tempA as a 
inner join zone as c on a.zoneID = c.zoneID
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count )
Select   "hotellinghourfraction" as tableName,
         2501                   as checkNumber,
        'zoneID'                as testDescription,
         zoneID                 as testValue,
         count(*)               as count
from     tempA
where    aMatch <> 'yes';
Delete from CDB_Checks where checkNumber=2501 and count=0;

--       check no. 2502 -- check for unknown dayID
INSERT INTO QA_Checks_Log values ( 2502, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   dayID         as dayId2,
         'no '         as aMatch,
         count(*)      as n
From     hotellinghourfraction
Group by dayId2;

Update tempA as a 
inner join ##defaultdb##.dayOfAnyWeek as m on a.dayId2 = m.dayId
set aMatch='yes';

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

--       check no. 2503 -- check for unknown hourID
INSERT INTO QA_Checks_Log values ( 2503, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   hourID         as hourId2,
         'no '         as aMatch,
         count(*)      as n
From     hotellinghourfraction
Group by hourID2;

Update tempA as a 
inner join ##defaultdb##.hourOfAnyDay as m on a.hourId2 = m.hourId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "hotellinghourfraction" as tableName,
         2503,
        "hourId"                      as testDescription,
         hourId2                      as testValue,
         n                           as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 2504 -- check for missing zoneID, dayID, and hourID combinations (as long as this table has contents)
INSERT INTO QA_Checks_Log values ( 2504, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue)
Select   "hotellinghourfraction" as tableName,
         2504,
         'Missing combination of valid zoneID, dayID, hourID' as testDescription,
		 concat('Z: ', zoneID, ', dayID: ', dayID, ', hourID: ', hourID) as testValue
from (
	SELECT zoneID, dayID, hourID
	FROM  zone
	CROSS JOIN ##defaultdb##.dayOfAnyWeek
	CROSS JOIN ##defaultdb##.hourOfAnyDay
) as t1 
left join hotellinghourfraction using (zoneID, dayID, hourID)
join (select count(*) as c from hotellinghourfraction) as t2
where hourFraction is NULL and c > 0
ORDER BY zoneID, dayID, hourID LIMIT 1;

--       check no. 2505 -- check for hourFractions >= 1
INSERT INTO QA_Checks_Log values ( 2505, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         zoneID, dayID, hourID)
Select   "hotellinghourfraction" as tableName,
         2505,
         'hourFraction >= 1' as testDescription,
		 hourFraction as testValue,
         zoneID, dayID, hourID
from hotellinghourfraction
where hourFraction >= 1
ORDER BY zoneID, dayID, hourID LIMIT 1;

--       check no. 2506 -- check that the distribution sums to 1
INSERT INTO QA_Checks_Log values ( 2506, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         zoneID, dayID)
Select   "hotellinghourfraction" as tableName,
         2506,
         'distribution <> 1' as testDescription,
		 sum(hourFraction) as testValue,
         zoneID, dayID
from hotellinghourfraction
GROUP BY zoneID, dayID
having testValue < .99999 or testvalue > 1.00001;

--       check no. 2507: make sure hour distributions aren't flat
INSERT INTO QA_Checks_Log values ( 2507, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         zoneID,
         dayID)
Select   "hotellinghourfraction" as tableName,
         2507 as checkNumber,
         'hourFraction is a flat profile' as testDescription,
         concat('all are ', hourFraction) as testValue,
		 zoneID, dayID
from hotellinghourfraction
group by zoneID, dayID, hourFraction
having count(*) = (select count(*) from ##defaultdb##.hourOfAnyDay)
order by zoneID, dayID LIMIT 1;

--       check no. 2508: make sure weekend and weekday profiles are different
INSERT INTO QA_Checks_Log values ( 2508, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         zoneID)
Select   "hotellinghourfraction" as tableName,
         2508 as checkNumber,
         'hourFraction is the same between weekend and weekday' as testDescription,
		 zoneID
from (select zoneID, hourID, hourFraction as weekendFraction
	  from hotellinghourfraction
	  where dayID = 2) as we
join (select zoneID, hourID, hourFraction as weekdayFraction
	  from hotellinghourfraction
	  where dayID = 5) as wd using (zoneID, hourID)
group by zoneID
having sum(abs(weekendFraction - weekdayFraction)) < 0.00001
order by zoneID LIMIT 1;


-- hotellinghoursperday
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (2600, "hotellinghoursperday", "Table Check:");

--       check no. 2601 -- check for unknown yearID
INSERT INTO QA_Checks_Log values ( 2601, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   yearID         as yearId2,
         'no '         as aMatch,
         count(*)      as n
From     hotellinghoursperday
Group by yearID2;

Update tempA as a 
inner join year as c on a.yearId2 = c.yearId
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "hotellinghoursperday" as tableName,
         2601,
        "yearID"                      as testDescription,
         yearID2                      as testValue,
         n                           as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 2602 -- check for unknown zoneID
INSERT INTO QA_Checks_Log values ( 2602, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   zoneID,
         'no '           as aMatch,
         count(*)        as n
From     hotellinghoursperday
group by zoneID;

Update tempA as a 
inner join zone as c on a.zoneID = c.zoneID
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count )
Select   "hotellinghoursperday" as tableName,
         2602                   as checkNumber,
        'zoneID'                as testDescription,
         zoneID                 as testValue,
         count(*)               as count
from     tempA
where    aMatch <> 'yes';
Delete from CDB_Checks where checkNumber=2602 and count=0;

--       check no. 2603 -- check for unknown dayID
INSERT INTO QA_Checks_Log values ( 2603, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   dayID         as dayId2,
         'no '         as aMatch,
         count(*)      as n
From     hotellinghoursperday
Group by dayId2;

Update tempA as a 
inner join ##defaultdb##.dayOfAnyWeek as m on a.dayId2 = m.dayId
set aMatch='yes';

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

--       check no. 2604 -- check for missing yearID, zoneID, and dayID combinations (as long as this table has contents)
INSERT INTO QA_Checks_Log values ( 2604, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue)
Select   "hotellinghoursperday" as tableName,
         2604,
         'Missing combination of valid yearID, zoneID, dayID' as testDescription,
		 concat('Year: ', yearID, ', Z: ', zoneID, ', dayID: ', dayID) as testValue
from (
	SELECT yearID, zoneID, dayID
	FROM  year
	CROSS JOIN zone
	CROSS JOIN ##defaultdb##.dayOfAnyWeek
) as t1 
left join hotellinghoursperday using (yearID, zoneID, dayID)
join (select count(*) as c from hotellinghoursperday) as t2
where hotellingHoursPerDay is NULL and c > 0
ORDER BY yearID, zoneID, dayID LIMIT 1;


-- hotellingmonthadjust
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (2700, "hotellingmonthadjust", "Table Check:");

--       check no. 2701 -- check for unknown zoneID
INSERT INTO QA_Checks_Log values ( 2701, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   zoneID,
         'no '           as aMatch,
         count(*)        as n
From     hotellingmonthadjust
group by zoneID;

Update tempA as a 
inner join zone as c on a.zoneID = c.zoneID
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count )
Select   "hotellingmonthadjust" as tableName,
         2701                   as checkNumber,
        'zoneID'                as testDescription,
         zoneID                 as testValue,
         count(*)               as count
from     tempA
where    aMatch <> 'yes';
Delete from CDB_Checks where checkNumber=2701 and count=0;

--       check no. 2702 -- check for unknown monthID
INSERT INTO QA_Checks_Log values ( 2702, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   monthID,
         'no '           as aMatch,
         count(*)        as n
From     hotellingmonthadjust
group by monthID;

Update tempA as a 
inner join ##defaultdb##.monthOfAnyYear as m on a.monthId = m.monthId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count )
Select   "hotellingmonthadjust" as tableName,
         2702                   as checkNumber,
        'monthID'                as testDescription,
         monthID                 as testValue,
         count(*)               as count
from     tempA
where    aMatch <> 'yes';
Delete from CDB_Checks where checkNumber=2702 and count=0;

--       check no. 2703 -- check for missing zoneID and monthID combinations (as long as this table has contents)
INSERT INTO QA_Checks_Log values ( 2703, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue)
Select   "hotellingmonthadjust" as tableName,
         2703,
         'Missing combination of valid zoneID and monthID' as testDescription,
		 concat('Z: ', zoneID, ', monthID: ', monthID) as testValue
from (
	SELECT zoneID, monthID
	FROM  zone
	CROSS JOIN ##defaultdb##.monthOfAnyYear
) as t1 
left join hotellingmonthadjust using (zoneID, monthID)
join (select count(*) as c from hotellingmonthadjust) as t2
where monthAdjustment is NULL and c > 0
ORDER BY zoneID, monthID LIMIT 1;


-- hourVMTFraction checks
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (2800, "hourVmtFraction", "Table Check:");

--       check no. 2801 -- checks unknown dayIDs
INSERT INTO QA_Checks_Log values ( 2801, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.dayOfAnyWeek as m on a.dayId2 = m.dayId
set aMatch='yes';

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

--       check no. 2802 -- check for unknown hourIDs
INSERT INTO QA_Checks_Log values ( 2802, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.hourOfAnyDay as m on a.hourId2 = m.hourId
set aMatch='yes';

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

--       check no. 2803 -- check for unknown roadTypeIDs
INSERT INTO QA_Checks_Log values (2803, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.roadType as m on a.roadTypeId2 = m.roadTypeId
set aMatch='yes';

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

--       check no. 2804 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 2804, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';	

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

--       check no. 2805 -- check that hourVMTFraction sums to 1 over sourceTypeID, roadTypeID, and dayID
INSERT INTO QA_Checks_Log values ( 2805, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;

Insert into CDB_Checks
       (  tableName,
          checkNumber,
          testDescription,
          testValue,
            sourceTypeId,
            roadTypeId,
            dayId  )
Select   "hourVmtFraction"               as tableName,
          2805                             as checkNumber,
         "sum of hourVmtFraction <> 1.0" as tesDescription,
          sum(hourVmtFraction)           as testValue,
            sourceTypeId,
            roadTypeId,
            dayId
From      hourVmtFraction
Where     roadTypeId in (2,3,4,5)
Group by  sourceTypeId,
          roadTypeId,
          dayId
Having    testValue <0.99999 or testValue >1.00001;

--       check no. 2806: check for missing sourceTypeID, roadTypeID, dayID, hourID combinations
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         sourceTypeID, roadTypeID, dayID, hourID)
Select   "hourVMTFraction" as tableName,
         2806,
         'Missing combination of valid sourceTypeID, roadTypeID, dayID, hourID' as testDescription,
         sourceTypeID, roadTypeID, dayID, hourID
from (
	SELECT sourceTypeID, roadTypeID, dayID, hourID
	FROM  ##defaultdb##.sourceusetype
	CROSS JOIN ##defaultdb##.roadtype
	CROSS JOIN ##defaultdb##.dayofanyweek
	CROSS JOIN ##defaultdb##.hourofanyday
	WHERE roadTypeID BETWEEN 2 and 5
) as t1 left join hourvmtfraction using (sourceTypeID, roadTypeID, dayID, hourID)
where hourVMTFraction is NULL 
ORDER BY sourceTypeID, roadTypeID, dayID, hourID LIMIT 1;

--       check no. 2807 -- check for hourVMTFractions >= 1
INSERT INTO QA_Checks_Log values ( 2807, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         sourceTypeID, roadTypeID, dayID, hourID)
Select   "hourVMTFraction" as tableName,
         2807,
         'hourVMTFraction >= 1' as testDescription,
		 hourVMTFraction as testValue,
         sourceTypeID, roadTypeID, dayID, hourID
from hourvmtfraction
where hourVMTFraction >= 1
ORDER BY sourceTypeID, roadTypeID, dayID, hourID LIMIT 1;

--       check no. 2808: make sure hour VMT distributions aren't flat
INSERT INTO QA_Checks_Log values ( 2808, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         sourceTypeID, roadTypeID, dayID)
Select   "hourVMTFraction" as tableName,
         2808 as checkNumber,
         'hourVMTFraction is a flat profile' as testDescription,
         concat('all are ', hourVMTFraction) as testValue,
		 sourceTypeID, roadTypeID, dayID
from hourvmtfraction
where roadTypeID not in (1, 100)
group by sourceTypeID, roadTypeID, dayID, hourVMTFraction
having count(*) = (select count(*) from ##defaultdb##.hourOfAnyDay)
order by sourceTypeID, roadTypeID, dayID LIMIT 1;

--       check no. 2809: make sure weekend and weekday profiles are different
INSERT INTO QA_Checks_Log values ( 2809, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         sourceTypeID,
         roadTypeID)
Select   "hourVMTFraction" as tableName,
         2809 as checkNumber,
         'hourVMTFraction is the same between weekend and weekday' as testDescription,
		 sourceTypeID,
         roadTypeID
from (select sourceTypeID, roadTypeID, hourID, hourVMTFraction as weekendFraction
	  from hourVMTFraction
	  where dayID = 2 and roadTypeID not in (1, 100)) as we
join (select sourceTypeID, roadTypeID, hourID, hourVMTFraction as weekdayFraction
	  from hourVMTFraction
	  where dayID = 5 and roadTypeID not in (1, 100)) as wd using (sourceTypeID, roadTypeID, hourID)
group by sourceTypeID, roadTypeID
having sum(abs(weekendFraction - weekdayFraction)) < 0.00001
order by sourceTypeID, roadTypeID LIMIT 1;


-- HPMSVTypeDay
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (2900, "HPMSVTypeDay", "Table Check:");

--       check no. 2901 -- check for unknown yearIDs
INSERT INTO QA_Checks_Log values ( 2901, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   yearId       as yearId2,
         'no '        as aMatch,
         count(*)     as n
From     hpmsVTypeDay
Group by yearId2;

Update tempA as a 
inner join year as c on a.yearId2 = c.yearId
set aMatch='yes';	

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

--       check no. 2902 -- check for unknown monthIDs
INSERT INTO QA_Checks_Log values ( 2902, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   monthId      as monthId2,
         'no '        as aMatch,
         count(*)     as n
From     hpmsVTypeDay
Group by monthId2;

Update tempA as a 
inner join ##defaultdb##.monthOfAnyYear as m on a.monthId2 = m.monthId
set aMatch='yes';

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

--       check no. 2903 -- check for unknown dayIDs
INSERT INTO QA_Checks_Log values ( 2903, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   dayId        as dayId2,
         'no '        as aMatch,
         count(*)     as n
From     hpmsVTypeDay
Group by dayId2;

Update tempA as a 
inner join ##defaultdb##.dayOfAnyWeek as m on a.dayId2 = m.dayId
set aMatch='yes';

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

--       check no. 2904 -- check for unknown hpmsVTypeIDs
INSERT INTO QA_Checks_Log values ( 2904, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   HPMSVtypeID  as HPMSVtypeID2,
         'no '        as aMatch,
         count(*)     as n
From     hpmsVTypeDay
Group by hpmsVTypeID2;

Update tempA as a 
inner join ##defaultdb##.hpmsvtype as m on a.HPMSVtypeID2 = m.HPMSVtypeID
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count)
Select   "hpmsVTypeDay" as tableName,
         2904,
         "HPMSVtypeID"        as testDescription,
         HPMSVtypeID2         as testValue,
         n              as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 2905: check for missing yearID, monthID, dayID, HPMSVtypeID combinations
--                       only when hpmsvtypeday is used
INSERT INTO QA_Checks_Log values ( 2905, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         yearID, monthID, dayID, HPMSVtypeID)
Select   "hpmsvtypeday" as tableName,
         2905,
         'Missing combination of valid yearID, monthID, dayID, HPMSVtypeID' as testDescription,
		 yearID, monthID, dayID, HPMSVtypeID
from (
	SELECT yearID, monthID, dayID, HPMSVtypeID
	FROM  `year`
	CROSS JOIN ##defaultdb##.monthOfAnyYear
	CROSS JOIN ##defaultdb##.dayOfAnyWeek
	CROSS JOIN ##defaultdb##.hpmsvtype
) as t1
left join hpmsvtypeday using (yearID, monthID, dayID, HPMSVtypeID)
join (select count(*) as n from hpmsvtypeday) as t2
where VMT is NULL and n > 0
ORDER BY yearID, monthID, dayID, HPMSVtypeID LIMIT 1;


-- checks for HPMSVTypeYear
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (3000, "hpmsVTypeYear", "Table Check:");

--       check no. 3001 -- check for unknown HPMSVTypeIDs
INSERT INTO QA_Checks_Log values ( 3001, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.hpmsvtype as m on a.HPMSVtypeID2 = m.HPMSVtypeID
set aMatch='yes';

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

--       check no. 3002 -- check for unknown yearIDs
INSERT INTO QA_Checks_Log values ( 3002, 'OK', @hVersion, curDate(), curTime() );
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 
inner join year as c on a.yearId2 = c.yearId
set aMatch='yes';

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

--       check no. 3003: check for missing yearID, HPMSVtypeID combinations
INSERT INTO QA_Checks_Log values ( 3003, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         yearID, HPMSVtypeID)
Select   "hpmsvtypeyear" as tableName,
         3003,
         'Missing combination of valid yearID, HPMSVtypeID' as testDescription,
		 yearID, HPMSVtypeID
from (
	SELECT yearID, HPMSVtypeID
	FROM  `year`
	CROSS JOIN ##defaultdb##.hpmsvtype
) as t1
left join hpmsvtypeyear using (yearID, HPMSVtypeID)
join (select count(*) as n from hpmsvtypeyear) as t2
where HPMSBaseYearVMT is NULL and n > 0
ORDER BY yearID, HPMSVtypeID LIMIT 1;


-- idleDayAdjust
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (3100, "idleDayAdjust", "Table Check:");

--       check no. 3101 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 3101, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     idledayadjust
Group by sourceTypeId2;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';

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

--       check no. 3102 -- checks unknown dayIDs
INSERT INTO QA_Checks_Log values ( 3102, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   dayId    as dayId2,
         'no '    as aMatch,
         count(*) as n
From     idledayadjust
Group by dayId2;

Update tempA as a 
inner join ##defaultdb##.dayOfAnyWeek as m on a.dayId2 = m.dayId
set aMatch='yes';

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

--       check no. 3103: check for missing sourceTypeID and dayID combinations
INSERT INTO QA_Checks_Log values ( 3103, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         sourceTypeID, dayID)
Select   "idledayadjust" as tableName,
         3103,
         'Missing combination of valid sourceTypeID, dayID' as testDescription,
		 sourceTypeID, dayID
from (
	SELECT sourceTypeID, dayID
	FROM  ##defaultdb##.sourceusetype
	CROSS JOIN ##defaultdb##.dayofanyweek
) as t1 left join idledayadjust using (sourceTypeID, dayID)
join (select count(*) as n from idledayadjust) as t2
where idleDayAdjust is NULL and n > 0
ORDER BY sourceTypeID, dayID LIMIT 1;


-- idlemodelyeargrouping
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (3200, "idlemodelyeargrouping", "Table Check:");

--       check no. 3201 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 3201, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     idlemodelyeargrouping
Group by sourceTypeId2;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';

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

--       check no. 3202: check for missing source types if this table is populated
INSERT INTO QA_Checks_Log values ( 3202, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         sourceTypeID)
Select   "idlemodelyeargrouping" as tableName,
         3202,
         'Missing sourceTypeID' as testDescription,
		 sourceTypeID
from ##defaultdb##.sourceusetype
left join idlemodelyeargrouping using (sourceTypeID)
join (select count(*) as n from idlemodelyeargrouping) as t1
where totalIdleFraction is NULL and n > 0
ORDER BY sourceTypeID LIMIT 1;

--       check no. 3203 -- Check for gaps and overlaps in the model years columns in the idlemodelyeargrouping table.
INSERT INTO QA_Checks_Log values ( 3203, 'OK', @hVersion, curDate(), curTime() );

-- Add a table to contain the results of the gaps/overlaps check.
Drop   table if exists     qa_checks_imyg;
Create Table if Not Exists qa_checks_imyg (
  CsourceType   int,   -- sourceTypeID
  LMaxMY int,          -- last    row's max model year
  CMinMY int,          -- Current row's min model year
  CMaxMY int,          -- Current row's max model year
  Reason varchar(40) );

-- Call the procedure to check for gaps and overlaps.
call checkIdleModelYearGrouping();

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         count  )
Select  "idlemodelyeargrouping" as tableName,
         3203,
        "gaps and overlaps" as testDescription,
         (Select count(*) from qa_checks_imyg) as count
From     qa_checks_imyg
Where    (Select count(*) from qa_checks_imyg) > 0;


-- idlemonthadjust
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (3300, "idlemonthadjust", "Table Check:");

--       check no. 3301 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 3301, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     idlemonthadjust
Group by sourceTypeId2;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';

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

--       check no. 3302 -- checks unknown monthIDs
INSERT INTO QA_Checks_Log values ( 3302, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   monthID    as monthID2,
         'no '    as aMatch,
         count(*) as n
From     idlemonthadjust
Group by monthID2;

Update tempA as a 
inner join ##defaultdb##.monthOfAnyYear as m on a.monthId2 = m.monthId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "idlemonthadjust" as tableName,
         3302,
         "monthID"           as testDescription,
         monthID2            as testValue,
         n                 as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 3303: check for missing sourceTypeID and monthID combinations
INSERT INTO QA_Checks_Log values ( 3303, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         sourceTypeID, monthID)
Select   "idlemonthadjust" as tableName,
         3303,
         'Missing combination of valid sourceTypeID, monthID' as testDescription,
		 sourceTypeID, monthID
from (
	SELECT sourceTypeID, monthID
	FROM  ##defaultdb##.sourceusetype
	CROSS JOIN ##defaultdb##.monthOfAnyYear
) as t1 left join idlemonthadjust using (sourceTypeID, monthID)
join (select count(*) as n from idlemonthadjust) as t2
where idlemonthadjust is NULL and n > 0
ORDER BY sourceTypeID, monthID LIMIT 1;


-- totalidlefraction
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (3400, "totalidlefraction", "Table Check:");

--       check no. 3401 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 3401, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     totalidlefraction
Group by sourceTypeId2;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';	

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

--       check no. 3402 -- checks unknown monthIDs
INSERT INTO QA_Checks_Log values ( 3402, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   monthID    as monthID2,
         'no '    as aMatch,
         count(*) as n
From     totalidlefraction
Group by monthID2;

Update tempA as a 
inner join ##defaultdb##.monthOfAnyYear as m on a.monthId2 = m.monthId
set aMatch='yes';
	
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "totalidlefraction" as tableName,
         3402,
         "monthID"           as testDescription,
         monthID2            as testValue,
         n                 as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 3403 -- checks unknown dayIDs
INSERT INTO QA_Checks_Log values ( 3403, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   dayId    as dayId2,
         'no '    as aMatch,
         count(*) as n
From     totalidlefraction
Group by dayId2;

Update tempA as a 
inner join ##defaultdb##.dayOfAnyWeek as m on a.dayId2 = m.dayId
set aMatch='yes';

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

--       check no. 3404 -- checks unknown idleRegionID
INSERT INTO QA_Checks_Log values ( 3404, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   idleRegionID    as idleRegionID2,
         'no '    as aMatch,
         count(*) as n
From     totalidlefraction
Group by idleRegionID2;

Update tempA as a 
inner join ##defaultdb##.idleregion as m on a.idleRegionID2 = m.idleRegionID
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "totalidlefraction" as tableName,
         3404,
         "idleRegionID"      as testDescription,
         idleRegionID2       as testValue,
         n                   as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 3405 -- check for multiple idleRegionIDs
INSERT INTO QA_Checks_Log values ( 3405, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         `count`  )
Select   "totalidlefraction"           as tableName,
         3405,
        "Multiple idleRegionIDs"       as testDescription,
         count(distinct idleRegionID) as `count`
from totalidlefraction
having `count` > 1;

--       check no. 3406 -- checks unknown countyTypeID
INSERT INTO QA_Checks_Log values ( 3406, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   countyTypeID    as countyTypeID2,
         'no '    as aMatch,
         count(*) as n
From     totalidlefraction
Group by countyTypeID2;

Update tempA as a 
inner join ##defaultdb##.countytype as m on a.countyTypeID2 = m.countyTypeID
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "totalidlefraction" as tableName,
         3406,
         "countyTypeID"      as testDescription,
         countyTypeID2       as testValue,
         n                   as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 3407 -- check for multiple countyTypeIDs
INSERT INTO QA_Checks_Log values ( 3407, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         `count`  )
Select   "totalidlefraction"          as tableName,
         3407,
        "Multiple countyTypeIDs"      as testDescription,
         count(distinct countyTypeID) as `count`
from totalidlefraction
having `count` > 1;

--       check no. 3408 -- Check for gaps and overlaps in the model years columns in the TIF table.
INSERT INTO QA_Checks_Log values ( 3408, 'OK', @hVersion, curDate(), curTime() );

-- Add a table to contain the results of the gaps/overlaps check.
Drop   table if exists     qa_checks_tif;
Create Table if Not Exists qa_checks_tif (
  CsourceType   int,   -- sourceTypeID
  Cmonth		int,   -- monthID
  Cday          int,   -- dayID
  LMaxMY int,          -- last    row's max model year
  CMinMY int,          -- Current row's min model year
  CMaxMY int,          -- Current row's max model year
  Reason varchar(40) );

-- Call the procedure to check for gaps and overlaps.
call checkTotalIdleFraction();

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         count  )
Select  "totalidlefraction" as tableName,
         3408,
        "gaps and overlaps" as testDescription,
         (Select count(*) from qa_checks_tif) as count
From     qa_checks_tif
Where    (Select count(*) from qa_checks_tif) > 0;

--       check no. 3409: check for missing sourceTypeID, monthID, dayID combinations
INSERT INTO QA_Checks_Log values ( 3409, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         sourceTypeID, monthID, dayID)
Select   "totalidlefraction" as tableName,
         3409,
         'Missing combination of valid sourceTypeID, monthID, dayID' as testDescription,
		 sourceTypeID, monthID, dayID
from (
	SELECT sourceTypeID, monthID, dayID
	FROM  ##defaultdb##.sourceusetype
	CROSS JOIN ##defaultdb##.monthOfAnyYear
	CROSS JOIN ##defaultdb##.dayOfAnyWeek
) as t1 left join totalidlefraction using (sourceTypeID, monthID, dayID)
join (select count(*) as n from totalidlefraction) as t2
where totalIdleFraction is NULL and n > 0
ORDER BY sourceTypeID, monthID, dayID LIMIT 1;


-- IMCoverage checks
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (3500, "imCoverage", "Table Check:");

--       check no. 3501 -- check for unknown countyIDs
INSERT INTO QA_Checks_Log values ( 3501, 'OK', @hVersion, curDate(), curTime() );
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 
inner join county as c on a.countyId2 = c.countyId
set aMatch='yes';

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

--       check no. 3502 -- check for unknown fuelTypeIDs
INSERT INTO QA_Checks_Log values ( 3502, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.fuelType as m on a.FuelTypeId2 = m.fuelTypeId
set aMatch='yes';

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

--       check no. 3503 -- check for unknown polProcessIDs
INSERT INTO QA_Checks_Log values ( 3503, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.pollutantProcessAssoc as m on a.polProcessId2 = m.polProcessId
set aMatch='yes';										 

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

--       check no. 3504 -- check that inspectFreq is either 1 or 2
INSERT INTO QA_Checks_Log values ( 3504, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select  "imCoverage"  as tableName,
         3504,
        "inspectFreq" as testDescription,
         inspectFreq2 as testValue,
         n            as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 3505 -- check that useIMyn is either "y" or "n"
INSERT INTO QA_Checks_Log values ( 3505, 'OK', @hVersion, curDate(), curTime() );
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', 'Y', 'N');

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

--       check no. 3506 -- check that the complianceFactor is between 0 and 100
INSERT INTO QA_Checks_Log values ( 3506, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "imCoverage" as tableName,
         3506,
        "complianceFactor range" as testDescription,
         complianceFactor2       as testValue,
         n                       as count
From     tempA
Where    aMatch <> 'yes';

--       check no. 3507 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 3507, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     imCoverage
Group by sourceTypeId2;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';	

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

--       check no. 3508 -- check for unknown stateIDs
INSERT INTO QA_Checks_Log values ( 3508, 'OK', @hVersion, curDate(), curTime() );
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 
inner join state as c on a.stateId2 = c.stateId
set aMatch='yes';	

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

--       check no. 3509 -- check for unknown yearIDs
INSERT INTO QA_Checks_Log values ( 3509, 'OK', @hVersion, curDate(), curTime() );
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 
inner join year as c on a.yearId2 = c.yearId
set aMatch='yes';	

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

--       check no. 3510 -- check to make sure there are the same number of entries (by state, county, year, and source type) for gasoline and FFV.
INSERT INTO QA_Checks_Log values ( 3510, 'OK', @hVersion, curDate(), curTime() );

Drop table if exists tempA;
Create table tempA
select stateId,
       countyId,
       yearId,
       sourceTypeId,
       fuelTypeId
from   imCoverage;

alter table tempA add numFtype1 int default 0;
alter table tempA add numFtype5 int default 0;

drop   table if exists tempB;
create table           tempB
select * from tempA;

create INDEX idxA on tempA (stateId, countyId, yearId, sourceTypeId);
create INDEX idxB on tempB (stateId, countyId, yearId, sourceTypeId);

update tempA as a set a.numFtype1 = (select count(*)
                                     from tempB as b
                                     where b.fuelTypeId=1
                                       and a.stateId      = b.stateId
                                       and a.countyId     = b.countyId
                                       and a.yearId       = b.yearId
                                       and a.sourceTypeId = b.sourceTypeId
                                     group by               b.stateId,
                                                            b.countyId,
                                                            b.yearId,
                                                            b.sourceTypeId);

update tempA as a set a.numFtype5 = (select count(*)
                                     from tempB as b
                                     where b.fuelTypeId=5
                                       and a.stateId      = b.stateId
                                       and a.countyId     = b.countyId
                                       and a.yearId       = b.yearId
                                       and a.sourceTypeId = b.sourceTypeId
                                     group by               b.stateId,
                                                            b.countyId,
                                                            b.yearId,
                                                            b.sourceTypeId);

update tempa set numFtype1=0 where isnull(numFtype1);
update tempa set numFtype5=0 where isnull(numFtype5);

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         count,
         stateId,
         countyId,
         yearId,
         sourceTypeId )
Select  'imCoverage'               as tableName,
         3510                       as checkNumber,
        'counts fuelTypesIds 1<>5' as testDescription,
         count(*)                  as count,
         stateId,
         countyId,
         yearId,
         sourceTypeId
from     tempA
where numFtype1<>numFtype5
group by stateId,
         countyId,
         yearId,
         sourceTypeId;

--       check no. 3511 -- Check for gaps and overlaps in the IMCoverage table.
-- Add a table to contain the results of the IMCoverage gaps/overlaps check.
Drop   table if exists     qa_checks_im;
Create Table if Not Exists qa_checks_im (
  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) );

-- Call the procedure to check for gaps and overlaps.
call checkImCoverage();

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         count  )
Select  "imCoverage" as tableName,
         3511,
        "gaps and overlaps" as testDescription,
         (Select count(*) from qa_checks_im) as count
From     qa_checks_im
Where    (Select count(*) from qa_checks_im) > 0;


-- monthVMTFraction checks
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (3600, "monthVmtFraction", "Table Check:");

--       check no. 3601 -- check for unknown monthIDs
INSERT INTO QA_Checks_Log values ( 3601, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.monthOfAnyYear as m on a.monthId2 = m.monthId
set aMatch='yes';

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

--       check no. 3602 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 3602, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     monthVmtFraction
Group by sourceTypeId2;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';

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

--       check no. 3603 -- check that monthVMTFraction sums to 1 for each sourceTypeID
INSERT INTO QA_Checks_Log values ( 3603, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   sourceTypeId,
         sum(monthVmtfraction) as s,
         'n' as Ok
from     monthVMTfraction
group by sourceTypeId;

update tempA set Ok = 'y' where s > 0.99999 and s < 1.00001 and s is not null;

Insert into CDB_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
         sourceTypeId
       )
Select  'monthVmtFraction'    as tableName,
         3603                  as checkNumber,
         'sum of monthVMTFraction <> 1' as testDescription,
         s                    as testValue,
         sourceTypeId         as sourceType
From     tempA
Where    Ok = 'n';

--       check no. 3604 -- check for monthVMTFractions >= 1
INSERT INTO QA_Checks_Log values ( 3604, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         sourceTypeID)
Select   "monthvmtfraction" as tableName,
         3604,
         'monthVMTFraction >= 1' as testDescription,
		 monthVMTFraction as testValue,
         sourceTypeID
from monthvmtfraction
where monthVMTFraction >= 1
ORDER BY sourceTypeID LIMIT 1;

--       check no. 3605: make sure month distributions aren't flat
INSERT INTO QA_Checks_Log values ( 3605, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         sourceTypeID)
Select   "monthvmtfraction" as tableName,
         3605 as checkNumber,
         'monthVMTFraction is a flat profile' as testDescription,
         concat('all are ', monthVMTFraction) as testValue,
		 sourceTypeID
from monthvmtfraction
group by sourceTypeID, monthVMTFraction
having count(*) = (select count(*) from ##defaultdb##.monthOfAnyYear)
order by sourceTypeID LIMIT 1;

--       check no. 3606: check for missing sourceTypeID, monthID combinations
--                       only if monthVMTFraction is used
INSERT INTO QA_Checks_Log values ( 3606, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         sourceTypeID, monthID)
Select   "monthVMTFraction" as tableName,
         3606,
         'Missing combination of valid sourceTypeID, monthID' as testDescription,
		 sourceTypeID, monthID
from (
	SELECT sourceTypeID, monthID
	FROM  ##defaultdb##.sourceusetype
	CROSS JOIN ##defaultdb##.monthOfAnyYear
) as t1 
left join monthVMTFraction using (sourceTypeID, monthID)
join (select count(*) as n from monthVMTFraction) as t2
where monthVMTFraction.monthVMTFraction is NULL and n > 0
ORDER BY sourceTypeID, monthID LIMIT 1;


-- onroadretrofit
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (3700, "onRoadRetroFit", "Table Check:");

--       check no. 3701 -- check for unknown pollutantIDs
INSERT INTO QA_Checks_Log values ( 3701, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select  pollutantId,
       'no '    as aMatch,
       count(*) as n
from   onRoadRetroFit
group by pollutantId;

Update tempA as a 
inner join ##defaultdb##.pollutant as m on a.pollutantId = m.pollutantId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "onRoadRetroFit" as tableName,
         3701              as checkNumber,
        "pollutantId"     as testDescription,
         pollutantId      as testValue,
         count(*)         as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=3701 and count=0;

--       check no. 3702 -- check for unknown processIDs
INSERT INTO QA_Checks_Log values ( 3702, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select  processId,
       'no '    as aMatch,
       count(*) as n
from   onRoadRetroFit
group by processId;
									  
Update tempA as a 
inner join ##defaultdb##.pollutantProcessAssoc as m on a.processId = m.processId
set aMatch='yes';	
									  
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "onRoadRetroFit" as tableName,
         3702              as checkNumber,
        "processId"       as testDescription,
         processId        as testValue,
         count(*)         as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=3702 and count=0;

--       check no. 3703 -- check for unknown fuelTypeIDs
INSERT INTO QA_Checks_Log values ( 3703, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select  fuelTypeId ,
       'no '    as aMatch,
       count(*) as n
from   onRoadRetroFit
group by fuelTypeId;

Update tempA as a 
inner join ##defaultdb##.fuelType as m on a.FuelTypeId = m.fuelTypeId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "onRoadRetroFit" as tableName,
         3703              as checkNumber,
        "fuelTypeId "     as testDescription,
         fuelTypeId       as testValue,
         count(*)         as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=3703 and count=0;

--       check no. 3704 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 3704, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select  sourceTypeId,
       'no '    as aMatch,
       count(*) as n
from   onRoadRetroFit
group by sourceTypeId;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId = m.sourceTypeId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "onRoadRetroFit" as tableName,
         3704              as checkNumber,
        "sourceTypeId"    as testDescription,
         sourceTypeId     as testValue,
         count(*)         as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=3704 and count=0;

--       check no. 3705 -- check that the retrofitYearID <= the analysis year
INSERT INTO QA_Checks_Log values ( 3705, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "onRoadRetroFit" as tableName,
         3705              as checkNumber,
        "retrofitYearID > yearID"  as testDescription,
         retrofitYearId   as testValue,
         count(*)         as cou
from     onroadretrofit
join     `year` on yearID
where    retrofitYearID > yearID
group by retrofitYearID;

--       check no. 3706 -- check that endModelYearID <= retrofitYearID
INSERT INTO QA_Checks_Log values ( 3706, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "onRoadRetroFit" as tableName,
         3706              as checkNumber,
        "endModelYearId > retrofitYearID"  as testDescription,
         CONCAT(endModelYearId, ' > ', retrofitYearID) as testValue,
         count(*)         as cou
from     onroadretrofit
where    endModelYearID > retrofitYearID
group by endModelYearID, retrofitYearID;

--       check no. 3707 -- check that beginModelYearID <= endModelYearID
INSERT INTO QA_Checks_Log values ( 3707, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "onRoadRetroFit"  as tableName,
         3707               as checkNumber,
        "beginModelYearId > endModelYearID" as testDescription,
         CONCAT(beginModelYearId, ' > ', endModelYearID)  as testValue,
         count(*)          as cou
from     onroadretrofit
where    beginModelYearId > endModelYearID
group by beginModelYearId, endModelYearID;

--       check no. 3708 -- check that cumFractionRetrofit between 0 and 1
INSERT INTO QA_Checks_Log values ( 3708, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "onRoadRetroFit"  as tableName,
         3708               as checkNumber,
        "cumFractionRetrofit range" as testDescription,
         cumFractionRetrofit as testValue,
         count(*)          as cou
from     onroadretrofit
where    cumFractionRetrofit NOT BETWEEN 0 and 1
group by cumFractionRetrofit;

--       check no. 3709 -- check that retrofitEffectiveFraction <= 1
INSERT INTO QA_Checks_Log values ( 3709, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "onRoadRetroFit"  as tableName,
         3709               as checkNumber,
        "retrofitEffectiveFraction range" as testDescription,
         retrofitEffectiveFraction as testValue,
         count(*)          as cou
from     onroadretrofit
where    retrofitEffectiveFraction > 1
group by retrofitEffectiveFraction;


-- roadTypeDistribution checks
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (3800, "roadTypeDistribution", "Table Check:");

--       check no. 3801 -- check for unknown roadTypeID
INSERT INTO QA_Checks_Log values ( 3801, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.roadType as m on a.roadTypeId2 = m.roadTypeId
set aMatch='yes';

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

--       check no. 3802 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 3802, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     roadTypeDistribution
Group by sourceTypeId2;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';	

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

--       check no. 3803 -- check that the roadTypeVMTFraction sums to 1
INSERT INTO QA_Checks_Log values ( 3803, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
           sourceTypeId  )
Select  "roadTypeDistribution"              as tableName,
         3803                                 as checkNumber,
        "sum of roadTypeVmtFraction <> 1.0" as testDescription,
         sum(roadTypeVmtFraction)           as testValue,
           sourceTypeId
From     roadTypeDistribution
Group by sourceTypeId
Having   testValue <0.99999 or testValue >1.00001;

--       check no. 3804 -- check for roadTypeVMTFraction >= 1
INSERT INTO QA_Checks_Log values ( 3804, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         sourceTypeID)
Select   "roadtypedistribution" as tableName,
         3804,
         'roadTypeVMTFraction >= 1' as testDescription,
		 roadTypeVMTFraction as testValue,
         sourceTypeID
from roadtypedistribution
where roadTypeVMTFraction >= 1
ORDER BY sourceTypeID LIMIT 1;

--       check no. 3805: make sure road type distributions aren't flat
INSERT INTO QA_Checks_Log values ( 3805, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         sourceTypeID)
Select   "roadtypedistribution" as tableName,
         3805 as checkNumber,
         'roadTypeVMTFraction is a flat profile' as testDescription,
         concat('all are ', roadTypeVMTFraction) as testValue,
		 sourceTypeID
from roadtypedistribution
where roadTypeID not in (1, 100)
group by sourceTypeID, roadTypeVMTFraction
having count(*) = (select count(*) from ##defaultdb##.roadtype where roadTypeID not in (1, 100))
order by sourceTypeID LIMIT 1;

--       check no. 3806: check for missing sourceTypeID, roadTypeID combinations
INSERT INTO QA_Checks_Log values ( 3806, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         sourceTypeID, roadTypeID)
Select   "roadtypedistribution" as tableName,
         3806,
         'Missing combination of valid sourceTypeID, roadTypeID' as testDescription,
		 sourceTypeID, roadTypeID
from (
	SELECT sourceTypeID, roadTypeID
	FROM  ##defaultdb##.sourceusetype
	CROSS JOIN ##defaultdb##.roadtype where roadTypeID in (2, 3, 4, 5)
) as t1 left join roadtypedistribution using (sourceTypeID, roadTypeID)
where roadTypeVMTFraction is NULL 
ORDER BY sourceTypeID, roadTypeID LIMIT 1;


-- SourceTypeAgeDistribution checks
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (3900, "sourceTypeAgeDistribution", "Table Check:");

--       check no. 3901  -- check for unknown ageIDs
INSERT INTO QA_Checks_Log values ( 3901, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.ageCategory as m on a.ageId2 = m.ageId
set aMatch='yes';

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

--       check no. 3902 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 3902, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sourceTypeId as sourceTypeId2,
         'no '        as aMatch,
         count(*)     as n
From     sourceTypeAgeDistribution
Group by sourceTypeId2;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';

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

--       check no. 3903 -- check for unknown yearIDs
INSERT INTO QA_Checks_Log values ( 3903, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   yearId       as yearId2,
         'no '        as aMatch,
         count(*)     as n
From     sourceTypeAgeDistribution
Group by yearId2;

Update tempA as a 
inner join year as c on a.yearId2 = c.yearId
set aMatch='yes';	

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

--       check no. 3904 -- check that ageFraction sums to 1
INSERT INTO QA_Checks_Log values ( 3904, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
           sourceTypeId,
           yearId)
Select  "sourceTypeAgeDistribution"   as tableName,
         3904                           as checkNumber,
        "sum of AgeFraction <> 1.0"   as testDescription,
         sum(ageFraction)             as testValue,
           sourceTypeId,
           yearId
From     sourceTypeAgeDistribution
Group by sourceTypeId,
         yearId
Having   testValue <0.99999 or testValue >1.00001;

--       check no. 3905: check for missing sourceTypeID, yearID, ageID combinations
INSERT INTO QA_Checks_Log values ( 3905, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue)
Select   "sourcetypeagedistribution" as tableName,
         3905,
         'Missing combination of valid sourceTypeID, yearID, ageID' as testDescription,
		 concat('ST: ', sourceTypeID, ', year: ', yearID, ', age: ', ageID) as testValue
from (
	SELECT sourceTypeID, yearID, ageID
	FROM  ##defaultdb##.sourceusetype
	CROSS JOIN year
	CROSS JOIN ##defaultdb##.agecategory
) as t1 left join sourcetypeagedistribution using (sourceTypeID, yearID, ageID)
where ageFraction is NULL 
ORDER BY sourceTypeID, yearID, ageID LIMIT 1;

--       check no. 3906: make sure age distributions aren't flat
INSERT INTO QA_Checks_Log values ( 3906, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         sourceTypeID, yearID)
Select   "sourcetypeagedistribution" as tableName,
         3906 as checkNumber,
         'ageFraction is a flat profile' as testDescription,
         concat('all are ', ageFraction) as testValue,
		 sourceTypeID, yearID
from sourcetypeagedistribution
group by sourceTypeID, yearID, ageFraction
having count(*) = (select count(*) from ##defaultdb##.ageCategory)
order by sourceTypeID, yearID LIMIT 1;


-- sourceTypeDayVMT checks
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (4000, "sourceTypeDayVMT", "Table Check:");

--       check no. 4001 -- check for unknown yearIDs
INSERT INTO QA_Checks_Log values ( 4001, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   yearId       as yearId2,
         'no '        as aMatch,
         count(*)     as n
From     sourceTypeDayVmt
Group by yearId2;

Update tempA as a 
inner join year as c on a.yearId2 = c.yearId
set aMatch='yes';	

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

--       check no. 4002 -- check for unknown monthIDs
INSERT INTO QA_Checks_Log values ( 4002, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   monthId      as monthId2,
         'no '        as aMatch,
         count(*)     as n
From     sourceTypeDayVmt
Group by monthId2;

Update tempA as a 
inner join ##defaultdb##.monthOfAnyYear as m on a.monthId2 = m.monthId
set aMatch='yes';

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

--       check no. 4003 -- check for unknown dayIDs
INSERT INTO QA_Checks_Log values ( 4003, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   dayId        as dayId2,
         'no '        as aMatch,
         count(*)     as n
From     sourceTypeDayVmt
Group by dayId2;

Update tempA as a 
inner join ##defaultdb##.dayOfAnyWeek as m on a.dayId2 = m.dayId
set aMatch='yes';

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

--       check no. 4004 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 4004, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sourceTypeId   as sourceTypeId2,
         'no '          as aMatch,
         count(*)       as n
From     sourceTypeDayVmt
Group by sourceTypeId2;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';	

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

--       check no. 4005: check for missing yearID, monthID, dayID, sourceTypeID combinations
--                       only when sourcetypedayvmt is used
INSERT INTO QA_Checks_Log values ( 4005, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         yearID, monthID, dayID, sourceTypeID)
Select   "sourcetypedayvmt" as tableName,
         4005,
         'Missing combination of valid yearID, monthID, dayID, sourceTypeID' as testDescription,
		 yearID, monthID, dayID, sourceTypeID
from (
	SELECT yearID, monthID, dayID, sourceTypeID
	FROM  `year`
	CROSS JOIN ##defaultdb##.monthOfAnyYear
	CROSS JOIN ##defaultdb##.dayOfAnyWeek
	CROSS JOIN ##defaultdb##.sourceusetype
) as t1
left join sourcetypedayvmt using (yearID, monthID, dayID, sourceTypeID)
join (select count(*) as n from sourcetypedayvmt) as t2
where VMT is NULL and n > 0
ORDER BY yearID, monthID, dayID, sourceTypeID LIMIT 1;


-- sourceTypeYearVMT
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (4100, "sourceTypeYearVMT", "Table Check:");

--       check no. 4101 -- check for unknown yearIDs
INSERT INTO QA_Checks_Log values ( 4101, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   yearId       as yearId2,
         'no '        as aMatch,
         count(*)     as n
From     sourceTypeYearVmt
Group by yearId2;

Update tempA as a 
inner join year as c on a.yearId2 = c.yearId
set aMatch='yes';

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

--       check no. 4102 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 4102, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sourceTypeId   as sourceTypeId2,
         'no '          as aMatch,
         count(*)       as n
From     sourceTypeYearVMT
Group by sourceTypeId2;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';	

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

--       check no. 4103: check for missing yearID, sourceTypeID combinations
--                       only when sourcetypeyearvmt is used
INSERT INTO QA_Checks_Log values ( 4103, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         yearID, sourceTypeID)
Select   "sourcetypeyearvmt" as tableName,
         4103,
         'Missing combination of valid yearID, sourceTypeID' as testDescription,
		 yearID, sourceTypeID
from (
	SELECT yearID, sourceTypeID
	FROM  `year`
	CROSS JOIN ##defaultdb##.sourceusetype
) as t1
left join sourcetypeyearvmt using (yearID, sourceTypeID)
join (select count(*) as n from sourcetypeyearvmt) as t2
where VMT is NULL and n > 0
ORDER BY yearID, sourceTypeID LIMIT 1;


-- SourceTypeYear
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (4200, "sourceTypeYear", "Table Check:");

--       check no. 4201 -- check for unknown yearIDs
INSERT INTO QA_Checks_Log values ( 4201, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   yearId       as yearId2,
         'no '        as aMatch,
         count(*)     as n
From     sourceTypeYear
Group by yearId2;

Update tempA as a 
inner join year as c on a.yearId2 = c.yearId
set aMatch='yes';

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

--       check no. 4202 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 4202, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sourceTypeId   as sourceTypeId2,
         'no '          as aMatch,
         count(*)       as n
From     sourceTypeYear
Group by sourceTypeId2;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId2 = m.sourceTypeId
set aMatch='yes';

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

--       check no. 4203: check for missing yearID and sourceTypeID combinations
INSERT INTO QA_Checks_Log values ( 4203, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         yearID, sourceTypeID)
Select   "sourcetypeyear" as tableName,
         4203,
         'Missing combination of valid yearID and sourceTypeID' as testDescription,
		 yearID, sourceTypeID
from (
	SELECT yearID, sourceTypeID
	FROM  `year`
	CROSS JOIN ##defaultdb##.sourceusetype
) as t1 left join sourcetypeyear using (yearID, sourceTypeID)
where sourceTypePopulation is NULL 
ORDER BY yearID, sourceTypeID LIMIT 1;

--       check no. 4204 -- if using the HPMSVtypeDay input, check that population summed by HPMS type is <>0 if the VMT is also >0
INSERT INTO QA_Checks_Log values ( 4204, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sum(sourceTypePopulation) as sourceTypePopulation2,
         a.yearId,
         b.HPMSVtypeId,
         b.VMT,
         'no '                as aMatch,
         count(*)             as n
From     sourceTypeYear as a
Join     ##defaultdb##.sourceUseType  as c on c.sourceTypeId = a.sourceTypeId
Join     HPMSVtypeDay   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.VMT > 0 and sourceTypePopulation2 > 0;
Update tempA as a set aMatch='yes' where a.VMT = 0 and sourceTypePopulation2 = 0;

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count,
         HPMSVtypeId,
         yearId)
Select   "sourceTypeYear or HPMSVtypeDay"     as tableName,
         4204,
        "sourceTypePopulation or VMT is zero" as testDescription,
         VMT                                  as testValue,
         n                                    as count,
         HPMSVTypeId,
         yearId
From     tempA
Where    aMatch <> 'yes';

--       check no. 4205 -- if using the HPMSVtypeYear input, check that population summed by HPMS type is <>0 if the VMT is also >0
INSERT INTO QA_Checks_Log values ( 4205, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   sum(sourceTypePopulation) as sourceTypePopulation2,
         a.yearId,
         b.HPMSVtypeId,
         b.HPMSBaseYearVMT,
         'no '                as aMatch,
         count(*)             as n

From                     sourceTypeYear as a
Join     ##defaultdb##.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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count,
         HPMSVtypeId,
         yearId)
Select   "sourceTypeYear or hpmsVTypeYear"                as tableName,
         4205,
        "sourceTypePopulation or HPMSBaseYearVMT is zero" as testDescription,
         HPMSBaseYearVMT                                  as testValue,
         n                                                as count,
         HPMSVTypeId,
         yearId
From     tempA
Where    aMatch <> 'yes';

--       check no. 4206 -- if using the SourceTypeDayVMT input, check that population summed by HPMS type is <>0 if the VMT is also >0
INSERT INTO QA_Checks_Log values ( 4206, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count,
         sourceTypeID,
         yearId)
Select   "sourceTypeYear or sourceTypeDayVMT" as tableName,
         4206,
        "sourceTypePopulation or VMT is zero" as testDescription,
         VMT                                  as testValue,
         count(*)                             as count,
         sourceTypeID,
         yearId
From     sourcetypedayvmt
JOIN	 sourcetypeyear using (sourceTypeID, yearID)
Where    (VMT > 0 and sourceTypePopulation = 0) OR
         (VMT = 0 and sourceTypePopulation > 0)
group by sourceTypeID, yearID;
         
--       check no. 4207 -- if using the SourceTypeYearVMT input, check that population summed by HPMS type is <>0 if the VMT is also >0
INSERT INTO QA_Checks_Log values ( 4207, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count,
         sourceTypeID,
         yearId)
Select   "sourceTypeYear or sourceTypeYearVMT" as tableName,
         4207,
        "sourceTypePopulation or VMT is zero"  as testDescription,
         VMT                                   as testValue,
         count(*)                              as count,
         sourceTypeID,
         yearId
From     SourceTypeYearVMT
JOIN	 sourcetypeyear using (sourceTypeID, yearID)
Where    (VMT > 0 and sourceTypePopulation = 0) OR
         (VMT = 0 and sourceTypePopulation > 0)
group by sourceTypeID, yearID;


-- starts checks
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (4300, "starts", "Table Check:");

--       check no. 4301 -- check for unknown hourDayIDs
INSERT INTO QA_Checks_Log values ( 4301, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   hourDayId,
        'no '    as aMatch,
         count(*) as n
from     starts
group by hourDayId;

Update tempA as a 
inner join ##defaultdb##.hourDay as m on a.hourDayId = m.hourDayId
set aMatch='yes';									

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "starts"    as tableName,
         4301         as checkNumber,
        "hourDayId"  as testDescription,
         hourDayId   as testValue,
         count(*)    as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4301 and count=0;

--       check no. 4302 -- check for unknown monthIDs
INSERT INTO QA_Checks_Log values ( 4302, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   monthId,
        'no '    as aMatch,
         count(*) as n
from     starts
group by monthId;

Update tempA as a 
inner join ##defaultdb##.monthOfAnyYear as m on a.monthId = m.monthId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "starts"       as tableName,
         4302            as checkNumber,
        "monthId"       as testDescription,
         monthId        as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4302 and count=0;

--       check no. 4303 -- check for unknown yearIDs
INSERT INTO QA_Checks_Log values ( 4303, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   yearId,
        'no '     as aMatch,
         count(*) as n
from     starts
group by yearId;

Update tempA as a 
inner join year as c on a.yearId = c.yearId
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "starts" as tableName,
         4303      as checkNumber,
        "yearId"  as testDescription,
         yearId   as testValue,
         count(*) as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4303 and count=0;

--       check no. 4304 -- check for unknown ageIDs
INSERT INTO QA_Checks_Log values ( 4304, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   ageId,
        'no '    as aMatch,
         count(*) as n
from     starts
group by ageId;

Update tempA as a 
inner join ##defaultdb##.ageCategory as m on a.ageId = m.ageId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "starts"       as tableName,
         4304            as checkNumber,
        "ageId"         as testDescription,
         ageId          as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4304 and count=0;

--       check no. 4305 -- check for unknown zoneIDs
INSERT INTO QA_Checks_Log values ( 4305, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   zoneId,
        'no '    as aMatch,
         count(*) as n
from     starts
group by zoneId;

Update tempA as a 
inner join zone as c on a.zoneID = c.zoneID
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "starts" as tableName,
         4305      as checkNumber,
        "zoneId"  as testDescription,
         zoneId   as testValue,
         count(*) as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4305 and count=0;

--       check no. 4306 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 4306, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   sourceTypeId,
        'no '    as aMatch,
         count(*) as n
from     starts
group by sourceTypeId;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId = m.sourceTypeId
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "starts"       as tableName,
         4306            as checkNumber,
        "sourceTypeId"  as testDescription,
         sourceTypeId   as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4306 and count=0;

--       check no. 4307 -- check that isUserInput is either "y" or "n"
INSERT INTO QA_Checks_Log values ( 4307, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   isUserInput     as isUserInput2,
         'no '           as aMatch,
         count(*)        as n
From     starts
Group by isUserInput2;

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

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "starts" as tableName,
         4307,
        "isUserInput not Y or N" as testDescription,
         isUserInput2      as testValue,
         n         as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 4308: check for missing hourDayID, monthID, yearID, ageID, zoneID, sourceTypeID combinations
--                       only when starts table is provided
INSERT INTO QA_Checks_Log values ( 4308, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         hourDayID, monthID, yearID, zoneID, sourceTypeID)
Select   "starts"   as tableName,
         4308                     as checkNumber,
         'Missing combination of valid hourDayID, monthID, yearID, ageID, zoneID, sourceTypeID' as testDescription,
		 concat('ageID: ', ageID) as testValue,
         hourDayID, monthID, yearID, zoneID, sourceTypeID
from (
	SELECT hourDayID, monthID, yearID, ageID, zoneID, sourceTypeID
	FROM  ##defaultdb##.hourday
	CROSS JOIN ##defaultdb##.monthOfAnyYear
	CROSS JOIN `year`
	CROSS JOIN ##defaultdb##.ageCategory
	CROSS JOIN zone
	CROSS JOIN ##defaultdb##.sourceusetype
) as t1 
left join `starts` using (hourDayID, monthID, yearID, ageID, zoneID, sourceTypeID)
join (select count(*) as n from `starts`) as t2
where `starts`.`starts` is NULL and n > 0
ORDER BY hourDayID, monthID, yearID, ageID, zoneID, sourceTypeID LIMIT 1;


-- startsAgeAdjustment checks
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (4400, "startsAgeAdjustment", "Table Check:");

--       check no. 4401 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 4401, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   sourceTypeId,
        'no '    as aMatch,
         count(*) as n
from     startsageadjustment
group by sourceTypeId;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId = m.sourceTypeId
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsageadjustment"       as tableName,
         4401            as checkNumber,
        "sourceTypeId"  as testDescription,
         sourceTypeId   as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4401 and count=0;

--       check no. 4402 -- check for unknown ageIDs
INSERT INTO QA_Checks_Log values ( 4402, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   ageId,
        'no '    as aMatch,
         count(*) as n
from     startsageadjustment
group by ageId;

Update tempA as a 
inner join ##defaultdb##.ageCategory as m on a.ageId = m.ageId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsageadjustment"       as tableName,
         4402            as checkNumber,
        "ageId"         as testDescription,
         ageId          as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4402 and count=0;

--       check no. 4403: check for missing sourceTypeID, ageID combinations
--                       only when startsageadjustment table is provided
INSERT INTO QA_Checks_Log values ( 4403, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         sourceTypeID)
Select   "startsageadjustment"   as tableName,
         4403                     as checkNumber,
         'Missing combination of valid sourceTypeID, ageID' as testDescription,
		 concat('ageID: ', ageID) as testValue,
         sourceTypeID
from (
	SELECT sourceTypeID, ageID
	FROM  ##defaultdb##.sourceusetype
	CROSS JOIN ##defaultdb##.ageCategory
) as t1 
left join startsageadjustment using (sourceTypeID, ageID)
join (select count(*) as n from startsageadjustment) as t2
where ageAdjustment is NULL and n > 0
ORDER BY sourceTypeID, ageID LIMIT 1;


-- startsHourFraction
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (4500, "startsHourFraction", "Table Check:");

--       check no. 4501 -- check for unknown dayIDs
INSERT INTO QA_Checks_Log values ( 4501, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   dayId,
        'no '     as aMatch,
         count(*) as n
from     startsHourFraction
group by dayId;

Update tempA as a 
inner join ##defaultdb##.dayOfAnyWeek as m on a.dayId = m.dayId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsHourFraction" as tableName,
         4501                  as checkNumber,
        "dayId"               as testDescription,
         dayId                as testValue,
         count(*)             as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4501 and count=0;

--       check no. 4502 -- check for unknown hourIDs
INSERT INTO QA_Checks_Log values ( 4502, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   hourId,
        'no '     as aMatch,
         count(*) as n
from     startsHourFraction
group by hourId;

Update tempA as a 
inner join ##defaultdb##.hourOfAnyDay as m on a.hourId = m.hourId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsHourFraction" as tableName,
         4502                  as checkNumber,
        "hourId"              as testDescription,
         hourId               as testValue,
         count(*)             as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4502 and count=0;

--       check no. 4503 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 4503, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   sourceTypeId,
        'no '    as aMatch,
         count(*) as n
from     startshourfraction
group by sourceTypeId;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId = m.sourceTypeId
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startshourfraction"       as tableName,
         4503            as checkNumber,
        "sourceTypeId"  as testDescription,
         sourceTypeId   as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4503 and count=0;

--       check no. 4504 -- check that allocationFraction sums to 1 for each sourceTypeID and dayID
INSERT INTO QA_Checks_Log values ( 4504, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
           sourceTypeId,
           dayID )
Select  "startsHourFraction"               as tableName,
         4504                              as checkNumber,
        "sum of allocationFraction <> 1.0" as testDescription,
         sum(allocationFraction)           as testValue,
           sourceTypeId,
           dayID
From     startshourfraction
Group by sourceTypeId,
         dayID
Having   testValue <0.99999 or testValue >1.00001;

--       check no. 4505 -- check for allocationFraction >= 1
INSERT INTO QA_Checks_Log values ( 4505, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         dayID, hourID, sourceTypeID)
Select   "startshourfraction" as tableName,
         4505,
         'allocationFraction >= 1' as testDescription,
		 allocationFraction as testValue,
         dayID, hourID, sourceTypeID
from startshourfraction
where allocationFraction >= 1
ORDER BY dayID, hourID, sourceTypeID LIMIT 1;

--       check no. 4506: make sure allocationFraction distributions aren't flat
INSERT INTO QA_Checks_Log values ( 4506, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         dayID, sourceTypeID)
Select   "startshourfraction" as tableName,
         4506 as checkNumber,
         'allocationFraction is a flat profile' as testDescription,
         concat('all are ', allocationFraction) as testValue,
		 dayID, sourceTypeID
from startshourfraction
group by dayID, sourceTypeID, allocationFraction
having count(*) = (select count(*) from ##defaultdb##.hourOfAnyDay)
order by dayID, sourceTypeID LIMIT 1;

--       check no. 4507: check for missing dayID, hourID, sourceTypeID combinations
--                       only when startshourfraction table is provided
INSERT INTO QA_Checks_Log values ( 4507, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         dayID, hourID, sourceTypeID)
Select   "startshourfraction"   as tableName,
         4507                     as checkNumber,
         'Missing combination of valid dayID, hourID, sourceTypeID' as testDescription,
         dayID, hourID, sourceTypeID
from (
	SELECT dayID, hourID, sourceTypeID
	FROM  ##defaultdb##.dayOfAnyWeek
	CROSS JOIN ##defaultdb##.hourOfAnyDay
	CROSS JOIN ##defaultdb##.sourceusetype
) as t1 
left join startshourfraction using (dayID, hourID, sourceTypeID)
join (select count(*) as n from startshourfraction) as t2
where allocationFraction is NULL and n > 0
ORDER BY dayID, hourID, sourceTypeID LIMIT 1;


-- startsmonthadjust
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (4600, "startsMonthAdjust", "Table Check:");

--       check no. 4601 -- check for unknown monthIDs
INSERT INTO QA_Checks_Log values ( 4601, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   monthId,
        'no '     as aMatch,
         count(*) as n
from     startsMonthAdjust
group by monthId;

Update tempA as a 
inner join ##defaultdb##.monthOfAnyYear as m on a.monthId = m.monthId
set aMatch='yes';
	
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsMonthAdjust" as tableName,
         4601                 as checkNumber,
        "monthId"            as testDescription,
         monthId             as testValue,
         count(*)            as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4601 and count=0;

--       check no. 4602 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 4602, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   sourceTypeId,
        'no '    as aMatch,
         count(*) as n
from     startsmonthadjust
group by sourceTypeId;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId = m.sourceTypeId
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsmonthadjust"       as tableName,
         4602            as checkNumber,
        "sourceTypeId"  as testDescription,
         sourceTypeId   as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4602 and count=0;

--       check no. 4603: check for missing monthID, sourceTypeID combinations
--                       only when startsmonthadjust table is provided
INSERT INTO QA_Checks_Log values ( 4603, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         monthID, sourceTypeID)
Select   "startsmonthadjust"   as tableName,
         4603                  as checkNumber,
         'Missing combination of valid monthID, sourceTypeID' as testDescription,
         monthID, sourceTypeID
from (
	SELECT monthID, sourceTypeID
	FROM  ##defaultdb##.monthOfAnyYear
	CROSS JOIN ##defaultdb##.sourceusetype
) as t1 
left join startsmonthadjust using (monthID, sourceTypeID)
join (select count(*) as n from startsmonthadjust) as t2
where monthAdjustment is NULL and n > 0
ORDER BY monthID, sourceTypeID LIMIT 1;


-- startsopmodedistribution
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (4700, "startsopmodedistribution", "Table Check:");

--       check no. 4701 -- check for unknown dayIDs
INSERT INTO QA_Checks_Log values ( 4701, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   dayID as dayID,
        'no '    as aMatch,
         count(*) as n
from     startsopmodedistribution
group by dayID;

Update tempA as a 
inner join ##defaultdb##.dayOfAnyWeek as m on a.dayId = m.dayId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsopmodedistribution"    as tableName,
         4701         as checkNumber,
        "dayID"  as testDescription,
         dayID   as testValue,
         count(*)    as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4701 and count=0;

--       check no. 4702 -- check for unknown hourIDs
INSERT INTO QA_Checks_Log values ( 4302, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   hourID,
        'no '    as aMatch,
         count(*) as n
from     startsopmodedistribution
group by hourID;

Update tempA as a 
inner join ##defaultdb##.hourOfAnyDay as m on a.hourId = m.hourId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsopmodedistribution"       as tableName,
         4702            as checkNumber,
        "hourID"       as testDescription,
         hourID        as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4702 and count=0;

--       check no. 4703 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 4703, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   sourceTypeId,
        'no '    as aMatch,
         count(*) as n
from     startsopmodedistribution
group by sourceTypeId;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId = m.sourceTypeId
set aMatch='yes';	

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsopmodedistribution"       as tableName,
         4703            as checkNumber,
        "sourceTypeId"  as testDescription,
         sourceTypeId   as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4703 and count=0;

--       check no. 4704 -- check for unknown ageIDs
INSERT INTO QA_Checks_Log values ( 4704, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   ageId,
        'no '    as aMatch,
         count(*) as n
from     startsopmodedistribution
group by ageId;

Update tempA as a 
inner join ##defaultdb##.ageCategory as m on a.ageId = m.ageId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsopmodedistribution"       as tableName,
         4704            as checkNumber,
        "ageId"         as testDescription,
         ageId          as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4704 and count=0;

--       check no. 4705 -- check for unknown opModeIDs
INSERT INTO QA_Checks_Log values ( 4705, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   opModeID,
        'no '    as aMatch,
         count(*) as n
from     startsopmodedistribution
group by opModeID;

Update tempA as a 
inner join ##defaultdb##.operatingmode as m on a.opModeID = m.opModeID
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsopmodedistribution"       as tableName,
         4705            as checkNumber,
        "opModeID"  as testDescription,
         opModeID   as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4705 and count=0;

--       check no. 4706 -- check that isUserInput is either "y" or "n"
INSERT INTO QA_Checks_Log values ( 4706, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
Select   isUserInput     as isUserInput2,
         'no '           as aMatch,
         count(*)        as n
From     startsopmodedistribution
Group by isUserInput2;

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

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsopmodedistribution" as tableName,
         4706,
        "isUserInput not Y or N" as testDescription,
         isUserInput2      as testValue,
         n         as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 4707 -- check that opModeFraction sums to 1 for each dayID, hourID, sourceTypeID, ageID
INSERT INTO QA_Checks_Log values ( 4707, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( tableName,
         checkNumber,
         testDescription,
         testValue,
		 dayID, hourID, sourceTypeID )
Select  "startsopmodedistribution"               as tableName,
         4707                              as checkNumber,
        "sum of opModeFraction <> 1.0" as testDescription,
         CONCAT('age ', ageID, ' sums to ', sum(opModeFraction))           as testValue,
		 dayID, hourID, sourceTypeID
From     startsopmodedistribution
Group by dayID, hourID, sourceTypeID, ageID
Having   sum(opModeFraction) <0.99999 or sum(opModeFraction) >1.00001;

--       check no. 4708: check for missing dayID, hourID, sourceTypeID, ageID, opModeID combinations
--                       only when startsopmodedistribution table is provided
INSERT INTO QA_Checks_Log values ( 4708, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         dayID, hourID, sourceTypeID)
Select   "startsopmodedistribution"   as tableName,
         4708                     as checkNumber,
         'Missing combination of valid dayID, hourID, sourceTypeID, ageID, opModeID' as testDescription,
		 concat('ageID: ', ageID, ', opModeID: ', opModeID) as testValue,
         dayID, hourID, sourceTypeID
from (
	SELECT dayID, hourID, sourceTypeID, ageID, opModeID
	FROM  ##defaultdb##.dayOfAnyWeek
	CROSS JOIN ##defaultdb##.hourOfAnyDay
	CROSS JOIN ##defaultdb##.sourceusetype
	CROSS JOIN ##defaultdb##.agecategory
	CROSS JOIN ##defaultdb##.operatingmode
    where opModeID between 101 and 108
) as t1 
left join startsopmodedistribution using (dayID, hourID, sourceTypeID, ageID, opModeID)
join (select count(*) as n from startsopmodedistribution) as t2
where opModeFraction is NULL and n > 0
ORDER BY dayID, hourID, sourceTypeID, ageID, opModeID LIMIT 1;


-- startsPerDay
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (4800, "startsPerDay", "Table Check:");

--       check no. 4801 -- check for unknown dayIDs
INSERT INTO QA_Checks_Log values ( 4801, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   dayId,
        'no '     as aMatch,
         count(*) as n
from     startsPerDay
group by dayId;

Update tempA as a 
inner join ##defaultdb##.dayOfAnyWeek as m on a.dayId = m.dayId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsPerDay" as tableName,
         4801            as checkNumber,
        "dayId"         as testDescription,
         dayId          as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4801 and count=0;

--       check no. 4802 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 4802, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   sourceTypeId,
        'no '    as aMatch,
         count(*) as n
from     startsperday
group by sourceTypeId;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId = m.sourceTypeId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsperday"       as tableName,
         4802            as checkNumber,
        "sourceTypeId"  as testDescription,
         sourceTypeId   as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4802 and count=0;

--       check no. 4803: check for missing dayID, sourceTypeID combinations
--                       only when startsperday table is provided
INSERT INTO QA_Checks_Log values ( 4803, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         dayID, sourceTypeID)
Select   "startsperday"   as tableName,
         4803                  as checkNumber,
         'Missing combination of valid dayID, sourceTypeID' as testDescription,
         dayID, sourceTypeID
from (
	SELECT dayID, sourceTypeID
	FROM  ##defaultdb##.dayOfAnyWeek
	CROSS JOIN ##defaultdb##.sourceusetype
) as t1 
left join startsperday using (dayID, sourceTypeID)
join (select count(*) as n from startsperday) as t2
where startsPerDay.startsPerDay is NULL and n > 0
ORDER BY dayID, sourceTypeID LIMIT 1;


-- startsPerDayPerVehicle
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (4900, "startsPerDayPerVehicle", "Table Check:");

--       check no. 4901 -- check for unknown dayIDs
INSERT INTO QA_Checks_Log values ( 4901, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   dayId,
        'no '     as aMatch,
         count(*) as n
from     startsPerDayPerVehicle
group by dayId;

Update tempA as a 
inner join ##defaultdb##.dayOfAnyWeek as m on a.dayId = m.dayId
set aMatch='yes';


Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsPerDayPerVehicle" as tableName,
         4901            as checkNumber,
        "dayId"         as testDescription,
         dayId          as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4901 and count=0;

--       check no. 4902 -- check for unknown sourceTypeIDs
INSERT INTO QA_Checks_Log values ( 4902, 'OK', @hVersion, curDate(), curTime() );
Drop table if exists tempA;
Create table tempA
select   sourceTypeId,
        'no '    as aMatch,
         count(*) as n
from     startsPerDayPerVehicle
group by sourceTypeId;

Update tempA as a 
inner join ##defaultdb##.sourceUseType as m on a.sourceTypeId = m.sourceTypeId
set aMatch='yes';

Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "startsPerDayPerVehicle"       as tableName,
         4902            as checkNumber,
        "sourceTypeId"  as testDescription,
         sourceTypeId   as testValue,
         count(*)       as cou
from     tempA
where    aMatch <> 'yes ';
Delete from CDB_Checks where checkNumber=4902 and count=0;

--       check no. 4903: check for missing dayID, sourceTypeID combinations
--                       only when startsPerDayPerVehicle table is provided
INSERT INTO QA_Checks_Log values ( 4903, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         dayID, sourceTypeID)
Select   "startsPerDayPerVehicle"   as tableName,
         4903                  as checkNumber,
         'Missing combination of valid dayID, sourceTypeID' as testDescription,
         dayID, sourceTypeID
from (
	SELECT dayID, sourceTypeID
	FROM  ##defaultdb##.dayOfAnyWeek
	CROSS JOIN ##defaultdb##.sourceusetype
) as t1 
left join startsPerDayPerVehicle using (dayID, sourceTypeID)
join (select count(*) as n from startsPerDayPerVehicle) as t2
where startsPerDayPerVehicle.startsPerDayPerVehicle is NULL and n > 0
ORDER BY dayID, sourceTypeID LIMIT 1;


-- zoneMonthHour
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (5000, "zoneMonthHour", "Table Check:");

--       check no. 5001 -- check for unknown hourIDs
INSERT INTO QA_Checks_Log values ( 5001, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.hourOfAnyDay as m on a.hourId2 = m.hourId
set aMatch='yes';

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

--       check no. 5002 -- check for unknown monthIDs
INSERT INTO QA_Checks_Log values ( 5002, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.monthOfAnyYear as m on a.monthId2 = m.monthId
set aMatch='yes';
	
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zoneMonthHour"    as tableName,
         5002,
        "monthId"           as testDescription,
         monthId2           as testValue,
         n                  as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 5003 -- check for unknown zoneIDs
INSERT INTO QA_Checks_Log values ( 5003, 'OK', @hVersion, curDate(), curTime() );
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 
inner join zone as c on a.zoneID2 = c.zoneID
set aMatch='yes';	

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

--       check no. 5004 -- check that the temperature is between -80 and 150
INSERT INTO QA_Checks_Log values ( 5004, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zoneMonthHour"    as tableName,
         5004,
        "temperature"       as testDescription,
         temperature2       as testValue,
         n                  as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 5005 -- check that the humidity is between 0 and 100
INSERT INTO QA_Checks_Log values ( 5005, 'OK', @hVersion, curDate(), curTime() );
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 CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count  )
Select   "zoneMonthHour"    as tableName,
         5005,
        "relHumidity"       as testDescription,
         relHumidity2       as testValue,
         n                  as count               --
From     tempA
Where    aMatch <> 'yes';

--       check no. 5006: check for missing monthID, zoneID, hourID combinations
INSERT INTO QA_Checks_Log values ( 5006, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         monthID, zoneID, hourID)
Select   "zonemonthhour" as tableName,
         5006,
         'Missing combination of valid monthID, zoneID, hourID' as testDescription,
		 monthID, zoneID, hourID
from (
	SELECT monthID, zoneID, hourID
	FROM  ##defaultdb##.monthOfAnyYear
	CROSS JOIN zone
	CROSS JOIN ##defaultdb##.hourOfAnyDay
) as t1
left join zonemonthhour using (monthID, zoneID, hourID)
join (select count(*) as n from zonemonthhour) as t2
where (temperature is NULL or relHumidity is NULL) and n > 0
ORDER BY monthID, zoneID, hourID LIMIT 1;


-- zoneRoadType
Insert into CDB_Checks (CheckNumber, TableName, TestDescription) values (5100, "zoneRoadType", "Table Check:");

--       check no. 5101 -- check for unknown roadTypeIDs
INSERT INTO QA_Checks_Log values ( 5101, 'OK', @hVersion, curDate(), curTime() );
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 
inner join ##defaultdb##.roadType as m on a.roadTypeId2 = m.roadTypeId
set aMatch='yes';

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

--       check no. 5102 -- check for unknown zoneIDs
INSERT INTO QA_Checks_Log values ( 5102, 'OK', @hVersion, curDate(), curTime() );
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 
inner join zone as c on a.zoneID2 = c.zoneID
set aMatch='yes';	

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

--       check no. 5103 -- check that the SHOAllocFactor sums to 1 for each road type
--                         No tolerance on this one because there should only be one 
--                         row per road type, so no floating point issues here
INSERT INTO QA_Checks_Log values ( 5103, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         testValue,
         count,
         roadTypeID)
Select  "zoneRoadType"       as tableName,
         5103,
        "SHOAllocFactor should be 1"         as testDescription,
         sum(SHOAllocFactor) as SHOAllocFactor2,
         count(*) as `count`,
         roadTypeID
From     zoneroadtype
where    roadTypeID not in (1, 100)
Group by roadTypeId
Having   SHOAllocFactor2 <> 1 or `count` > 1;
 
--       check no. 5104: check for missing zoneID, roadTypeID combinations
INSERT INTO QA_Checks_Log values ( 5104, 'OK', @hVersion, curDate(), curTime() );
Insert into CDB_Checks
       ( TableName,
         CheckNumber,
         TestDescription,
         zoneID, roadTypeID)
Select   "zoneroadtype" as tableName,
         5104,
         'Missing combination of valid zoneID, roadTypeID' as testDescription,
		 zoneID, roadTypeID
from (
	SELECT zoneID, roadTypeID
	FROM  zone
	CROSS JOIN ##defaultdb##.roadtype where roadTypeID in (2, 3, 4, 5)
) as t1 left join zoneroadtype using (zoneID, roadTypeID)
where SHOAllocFactor is NULL 
ORDER BY zoneID, roadTypeID LIMIT 1;
-- ##############################################################################
-- End data QA checks
-- ##############################################################################


-- ##############################################################################
-- Final steps
-- ##############################################################################
--       Clean up the results table:
Delete from   CDB_Checks where TableName = "ALL";
Insert into   CDB_Checks ( TableName, TestDescription ) values   ("All Tables",     "Tables Checked.");
Update        CDB_Checks set msgType = 'Info'   where testDescription = 'Table Check:';
Update        CDB_Checks set msgType = 'Data Problem' where checkNumber is not null and msgType is null;
Update        CDB_Checks set msgType = 'DB Checked' where tableName = 'ALL';
Update        CDB_Checks set msgDate = curDate();
Update        CDB_Checks set msgTime = curTime();
Update        CDB_Checks set dataBaseName = database();
Update        CDB_Checks set countyId = (select min(countyId) from county);

-- set Status to 'Completed' by default (this gets overwritten in the cases outlined below)
Update        CDB_Checks set status = 'Completed' where `status` is NULL or `status` = '';

-- set Status to 'Error' for most checks
Update        CDB_Checks set status = 'Error'   where checkNumber is not null and testDescription <> 'Table Check:';

-- set Status to 'Warning' for select checks (these are typically distribution checks, as well as tables that give warnings
--                                            if you supply them: fuelformulation, fuelsupply, and zonemonthhour)
Update        CDB_Checks set status = 'Warning' where checkNumber in 
			  (1608,1609,1610,1611,1807,2001,2002,2003,2004,2005,2006,2007,2008,2009,2010,2011,2012,2101,
			   2102,2103,2104,2406,2507,2508,2808,2809,3605,3804,3805,3906,4506,5001,5002,5003,5004,5005,
			   5006);

-- special cases for VMT/starts checks --
-- mark all VMT row count checks as 'complete' regardless of results
Update        CDB_Checks set status = 'Completed' where checkNumber in (1002, 1003, 1004, 1005);
-- mark all starts row count checks as 'complete' regardless of results
Update        CDB_Checks set status = 'Completed' where checkNumber in (1007, 1008, 1009);

-- order the results
alter table CDB_Checks order by checkNumber, tableName;

-- Eliminate temporary tables from the CDB.
Drop table if exists tempA;
Drop table if exists tempB;
call emptyTableCleanUp();

-- Version is set at beginning of the file.
Update CDB_Checks set version = @version;

-- Create a database to hold all results from a batch file run.
create Database if not exists All_CDB_Checks;

-- Create a table in the database to hold all results from a batch file run.
create Table if not exists All_CDB_Checks.All_CDB_Checks like CDB_Checks;

-- Copy the results from this run into the table holding all results from a batch run.
insert into All_CDB_Checks.All_CDB_Checks select * from CDB_Checks;

-- Clear all connections to the database tables.
flush tables;

-- Eliminate the stored procedures generated by this script.
drop procedure if exists checkImCoverage;
drop procedure if exists checkHotellingActivityDistribution;
drop procedure if exists checkIdleModelYearGrouping;
drop procedure if exists checkTotalIdleFraction;
drop procedure if exists emptyTableCleanUp;

-- drop final tables
drop table CDB_Checks;
drop table QA_Checks_Log;

select '  .. M3onroadCDBchecks.sql Done',curTime(),database();

-- done.
