SELECT statement and CPU usage

in the below reporting self-generated code, i need to see what may be the point of contention for CPU... we are also looking at index, but beyond that we want to tell the vendor if the code is indeed causing a cpu hike. the report does bring lot of records back.. but is there any inherent bad elementss that would cause spu usage more in the code? (for this app, at this time, the vendor only generates the code, which we are fighting)

select "T0"."C0" AS "C0", "T0"."C2" AS "C1", "T0"."C3" AS "C2", "T0"."C1" AS "C3", "T0"."C4" AS "C4", "T0"."C5" AS "C5", "T0"."C6" AS "C6", "T0"."C7" AS "C7", "T0"."C8" AS "C8", "T0"."C9" AS "C9", "T0"."C10" AS "C10", "T0"."C11" AS "C11", "T0"."C12" AS "C12", "T0"."C13" AS "C13", "T0"."C14" AS "C14", "T0"."C15" AS "C15", "T0"."C16" AS "C16", "T0"."C17" AS "C17", "T0"."C18" AS "C18", "T0"."C19" AS "C19", "T0"."C20" AS "C20", "T0"."C21" AS "C21", "T0"."C22" AS "C22", "T0"."C23" AS "C23", "T0"."C24" AS "C24", "T0"."C25" AS "C25", "T0"."C26" AS "C26", "T0"."C27" AS "C27", "T0"."C28" AS "C28", "T0"."C29" AS "C29", "T0"."C30" AS "C30", "T0"."C31" AS "C31", "T0"."C32" AS "C32", "T0"."C33" AS "C33", "T0"."C34" AS "C34", "T0"."C35" AS "C35", "T0"."C36" AS "C36" from (select "Base1"."Branch_Year" AS "C0", max("Base1"."Branch_Year") AS "C1", "Base1"."Employee_ID" AS "C2", "Base1"."County_Code" AS "C3", "Base1"."Attributed_County_Type" AS "C4", "Base1"."Attributed_County_Code" AS "C5", "Base1"."Attributed_County_Name" AS "C6", "Base1"."Attributed_Branch_Type" AS "C7", "Base1"."Attributed_Branch_ID" AS "C8", "Base1"."Attributed_Branch_Name" AS "C9", "Base1"."Employee_Exp_Level" AS "C10", "Base1"."Employee_Gender_Code" AS "C11", "Base1"."MaxEdu_Code" AS "C12", "Base1"."c11" AS "C13", "Base1"."BIL_Program_Entrance_Code" AS "C14", "WorkVisa"."c7" AS "C15", "Base1"."Train_Req_Status_Code" AS "C16", "Base1"."Promotion_Status" AS "C17", "Base1"."Cert_Type_Code" AS "C18", "Base1"."Date_of_Entry_to_CurrZip" AS "C19", "Base1"."c38" AS "C20", "Base1"."Organization_Type" AS "C21", "Base1"."County_Name" AS "C22", "Base1"."Branch_Type" AS "C23", "Base1"."ZIP_ID" AS "C24", "Base1"."Full_ZIP_Name" AS "C25", "Base1"."Program_Name" AS "C26", "Base1"."Sales_Status_Code" AS "C27", "Base1"."Sales_Status" AS "C28", "Base1"."Visa_Employee" AS "C29", "Base1"."Employee_Status_Code" AS "C30", "Base1"."Employee_Name" AS "C31", "Base1"."Employee_Middle_Initial" AS "C32", "Base1"."Employee_Last_Name" AS "C33", "Base1"."EMP_BIRTHDATE" AS "C34", "Base1"."Employee_Birth_Date" AS "C35", "Base1"."Employee_First_Name" AS "C36" from (select "Base2"."Organization_Type" AS "Organization_Type", "Base2"."County_Code" AS "County_Code", "Base2"."County_Name" AS "County_Name", "Base2"."Branch_Type" AS "Branch_Type", "Base2"."ZIP_ID" AS "ZIP_ID", "Base2"."Full_ZIP_Code" AS "Full_ZIP_Code", "Base2"."Employee_ID" AS "Employee_ID", "Base2"."Employee_Exp_Level" AS "Employee_Exp_Level", "Base2"."Employee_Gender_Code" AS "Employee_Gender_Code", "Base2"."MaxEdu_Code" AS "MaxEdu_Code", "Base2"."c11" AS "c11", "Base2"."BIL_Program_Entrance_Code" AS "BIL_Program_Entrance_Code", "Base2"."WorkVisa_Status_Code" AS "WorkVisa_Status_Code", "Base2"."Train_Req_Status_Code" AS "Train_Req_Status_Code", "Base2"."Promotion_Status" AS "Promotion_Status", "Base2"."Branch_Year" AS "Branch_Year", "Base2"."Cert_Type_Code" AS "Cert_Type_Code", "Base2"."Date_of_Entry_to_CurrZip" AS "Date_of_Entry_to_CurrZip", "Base2"."Visa_Employee" AS "Visa_Employee", "Base2"."Employee_Status_Code" AS "Employee_Status_Code", "Base2"."LOC_Type_Code" AS "LOC_Type_Code", "Base2"."Branch_Type_Code" AS "Branch_Type_Code", "Base2"."Review_Status_Code" AS "Review_Status_Code", "Base2"."ZIP_Code_of_Sales" AS "ZIP_Code_of_Sales", "Base2"."ZIP_of_Sales" AS "ZIP_of_Sales", "Base2"."ZIP_of_Sales_Type" AS "ZIP_of_Sales_Type", "Base2"."County_Code_of_Sales" AS "County_Code_of_Sales", "Base2"."Reporting_County_Name" AS "Reporting_County_Name", "Base2"."Reporting_County_Type" AS "Reporting_County_Type", "Sales_Status"."Employee_ID" AS "Institutionalized_ID", "Base2"."Review_ID" AS "Review_ID", case  when "Base2"."Visa_Employee" = 'Yes' or "Base2"."Employee_Status_Code" = 'A' then 'N/A' when "Base2"."Employee_Status_Code" in (‘R’, ‘C’) or  NOT "Sales_Status"."Employee_ID" is null or  NOT "Base2"."Review_ID" is null then 'STATE' when "Base2"."ZIP_ID" = '99999' and "Base2"."County_Code" <> "Base2"."County_Code_of_Sales" or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Organization_Type" = 'IU' or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Employee_Status_Code" = 'D' or "Base2"."Employee_Status_Code" = 'F' or "Base2"."Branch_Type_Code" = 'DEP' then "Base2"."County_Code_of_Sales" else "Base2"."County_Code" end  AS "Attributed_County_Code", case  when "Base2"."Visa_Employee" = 'Yes' or "Base2"."Employee_Status_Code" = 'A' then 'N/A' when "Base2"."Employee_Status_Code" in (‘R’, ‘C’) or  NOT "Sales_Status"."Employee_ID" is null or  NOT "Base2"."Review_ID" is null then 'STATE' when "Base2"."ZIP_ID" = '99999' and "Base2"."County_Code" <> "Base2"."County_Code_of_Sales" or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Organization_Type" = 'IU' or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Employee_Status_Code" = 'D' or "Base2"."Employee_Status_Code" = 'F' or "Base2"."Branch_Type_Code" = 'DEP' then "Base2"."ZIP_Code_of_Sales" else "Base2"."ZIP_ID" end  AS "Attributed_Branch_ID", case  when "Base2"."Visa_Employee" = 'Yes' or "Base2"."Employee_Status_Code" = 'A' then 'N/A' when "Base2"."Employee_Status_Code" in (‘R’, ‘C’) or  NOT "Sales_Status"."Employee_ID" is null or  NOT "Base2"."Review_ID" is null then 'STATE' when "Base2"."ZIP_ID" = '99999' and "Base2"."County_Code" <> "Base2"."County_Code_of_Sales" or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Organization_Type" = 'IU' or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Employee_Status_Code" = 'D' or "Base2"."Employee_Status_Code" = 'F' or "Base2"."Branch_Type_Code" = 'DEP' then "Base2"."Reporting_County_Name" else "Base2"."County_Name" end  AS "Attributed_County_Name", case  when "Base2"."Visa_Employee" = 'Yes' or "Base2"."Employee_Status_Code" = 'A' then 'N/A' when "Base2"."Employee_Status_Code" in (‘R’, ‘C’) or  NOT "Sales_Status"."Employee_ID" is null or  NOT "Base2"."Review_ID" is null then 'STATE' when "Base2"."ZIP_ID" = '99999' and "Base2"."County_Code" <> "Base2"."County_Code_of_Sales" or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Organization_Type" = 'IU' or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Employee_Status_Code" = 'D' or "Base2"."Employee_Status_Code" = 'F' or "Base2"."Branch_Type_Code" = 'DEP' then "Base2"."ZIP_of_Sales" else "Base2"."Full_ZIP_Code" end  AS "Attributed_Branch_Name", case  when "Base2"."Visa_Employee" = 'Yes' or "Base2"."Employee_Status_Code" = 'A' then 'N/A' when "Base2"."Employee_Status_Code" in (‘R’, ‘C’) or  NOT "Sales_Status"."Employee_ID" is null or  NOT "Base2"."Review_ID" is null then 'STATE' when "Base2"."ZIP_ID" = '99999' and "Base2"."County_Code" <> "Base2"."County_Code_of_Sales" or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Organization_Type" = 'IU' or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Employee_Status_Code" = 'D' or "Base2"."Employee_Status_Code" = 'F' or "Base2"."Branch_Type_Code" = 'DEP' then "Base2"."Reporting_County_Type" else "Base2"."LOC_Type_Code" end  AS "Attributed_County_Type", case  when "Base2"."Visa_Employee" = 'Yes' or "Base2"."Employee_Status_Code" = 'A' then 'N/A' when "Base2"."Employee_Status_Code" in (‘R’, ‘C’) or  NOT "Sales_Status"."Employee_ID" is null or  NOT "Base2"."Review_ID" is null then 'STATE' when "Base2"."ZIP_ID" = '99999' and "Base2"."County_Code" <> "Base2"."County_Code_of_Sales" or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Organization_Type" = 'IU' or "Base2"."Train_Req_Status_Code" = 'Y' and "Base2"."Employee_Status_Code" = 'D' or "Base2"."Employee_Status_Code" = 'F' or "Base2"."Branch_Type_Code" = 'DEP' then "Base2"."ZIP_of_Sales_Type" else "Base2"."Branch_Type_Code" end  AS "Attributed_Branch_Type", "Base2"."c31" AS "c38", "Base2"."Program_Name" AS "Program_Name", "Sales_Status"."Sales_Status_Code" AS "Sales_Status_Code", "Sales_Status"."Sales_Status" AS "Sales_Status", "Base2"."Employee_Name" AS "Employee_Name", "Base2"."Employee_Middle_Initial" AS "Employee_Middle_Initial", "Base2"."Employee_Last_Name" AS "Employee_Last_Name", "Base2"."EMP_BIRTHDATE" AS "EMP_BIRTHDATE", "Base2"."Employee_First_Name" AS "Employee_First_Name", "Base2"."Employee_Birth_Date" AS "Employee_Birth_Date" from (select "Base3"."Organization_Type" AS "Organization_Type", "Base3"."County_Code" AS "County_Code", "Base3"."County_Name" AS "County_Name", "Base3"."Branch_Type" AS "Branch_Type", "Base3"."ZIP_ID" AS "ZIP_ID", "Base3"."Full_ZIP_Code" AS "Full_ZIP_Code", "Base3"."Employee_ID" AS "Employee_ID", "Base3"."Employee_Exp_Level" AS "Employee_Exp_Level", "Base3"."Employee_Gender_Code" AS "Employee_Gender_Code", "Base3"."MaxEdu_Code" AS "MaxEdu_Code", "Base3"."c11" AS "c11", "Base3"."BIL_Program_Entrance_Code" AS "BIL_Program_Entrance_Code", "Base3"."WorkVisa_Status_Code" AS "WorkVisa_Status_Code", "Base3"."Train_Req_Status_Code" AS "Train_Req_Status_Code", "Base3"."Promotion_Status" AS "Promotion_Status", "Base3"."Branch_Year" AS "Branch_Year", "Base3"."Cert_Type_Code" AS "Cert_Type_Code", "Base3"."Date_of_Entry_to_CurrZip" AS "Date_of_Entry_to_CurrZip", "Base3"."Visa_Employee" AS "Visa_Employee", "Base3"."Employee_Status_Code" AS "Employee_Status_Code", "Base3"."LOC_Type_Code" AS "LOC_Type_Code", "Base3"."Branch_Type_Code" AS "Branch_Type_Code", "Base3"."Review_Status_Code" AS "Review_Status_Code", "Programs"."Employee_ID" AS "Review_ID", "Base3"."ZIP_Code_of_Sales" AS "ZIP_Code_of_Sales", "Base3"."ZIP_of_Sales" AS "ZIP_of_Sales", "Base3"."ZIP_of_Sales_Type" AS "ZIP_of_Sales_Type", "Base3"."County_Code_of_Sales" AS "County_Code_of_Sales", "Base3"."Reporting_County_Name" AS "Reporting_County_Name", "Base3"."Reporting_County_Type" AS "Reporting_County_Type", "Base3"."c23" AS "c31", "Programs"."Program_Name" AS "Program_Name", "Base3"."Employee_Name" AS "Employee_Name", "Base3"."Employee_Middle_Initial" AS "Employee_Middle_Initial", "Base3"."Employee_Last_Name" AS "Employee_Last_Name", "Base3"."EMP_BIRTHDATE" AS "EMP_BIRTHDATE", "Base3"."Employee_Birth_Date" AS "Employee_Birth_Date", "Base3"."Employee_First_Name" AS "Employee_First_Name" from (select "Base4"."Organization_Type" AS "Organization_Type", "Base4"."County_Code" AS "County_Code", "Base4"."County_Name" AS "County_Name", "Base4"."Branch_Type" AS "Branch_Type", "Base4"."ZIP_ID" AS "ZIP_ID", "Base4"."Full_ZIP_Code" AS "Full_ZIP_Code", "Base4"."Employee_ID" AS "Employee_ID", "Base4"."Employee_Exp_Level" AS "Employee_Exp_Level", "Base4"."Employee_Gender_Code" AS "Employee_Gender_Code", "Base4"."MaxEdu_Code" AS "MaxEdu_Code", "Base4"."c11" AS "c11", "Base4"."BIL_Program_Entrance_Code" AS "BIL_Program_Entrance_Code", "Base4"."WorkVisa_Status_Code" AS "WorkVisa_Status_Code", "Base4"."Train_Req_Status_Code" AS "Train_Req_Status_Code", "Base4"."Promotion_Status" AS "Promotion_Status", "Base4"."Branch_Year" AS "Branch_Year", "Base4"."Cert_Type_Code" AS "Cert_Type_Code", "Base4"."Date_of_Entry_to_CurrZip" AS "Date_of_Entry_to_CurrZip", "Base4"."Visa_Employee" AS "Visa_Employee", "Base4"."Employee_Status_Code" AS "Employee_Status_Code", "Base4"."LOC_Type_Code" AS "LOC_Type_Code", "Base4"."Branch_Type_Code" AS "Branch_Type_Code", "Base4"."c28" AS "c23", "Base4"."Review_Status_Code" AS "Review_Status_Code", "Base4"."ZIP_Code_of_Sales" AS "ZIP_Code_of_Sales", "Base4"."ZIP_of_Sales" AS "ZIP_of_Sales", "Base4"."ZIP_of_Sales_Type" AS "ZIP_of_Sales_Type", "Base4"."County_Code_of_Sales" AS "County_Code_of_Sales", "Reporting_County"."Reporting_County_Name" AS "Reporting_County_Name", "Reporting_County"."Reporting_County_Type" AS "Reporting_County_Type", "Base4"."Employee_Name" AS "Employee_Name", "Base4"."Employee_Middle_Initial" AS "Employee_Middle_Initial", "Base4"."Employee_Last_Name" AS "Employee_Last_Name", "Base4"."EMP_BIRTHDATE" AS "EMP_BIRTHDATE", "Base4"."Employee_First_Name" AS "Employee_First_Name", "Base4"."Employee_Birth_Date" AS "Employee_Birth_Date" from (select "Base5"."Organization_Type" AS "Organization_Type", "Base5"."County_Code" AS "County_Code", "Base5"."County_Name" AS "County_Name", "Base5"."Branch_Type" AS "Branch_Type", "Base5"."ZIP_ID" AS "ZIP_ID", "Base5"."Full_ZIP_Code" AS "Full_ZIP_Code", "Base5"."Employee_ID" AS "Employee_ID", "Base5"."Employee_Exp_Level" AS "Employee_Exp_Level", "Base5"."Employee_Gender_Code" AS "Employee_Gender_Code", "Base5"."MaxEdu_Code" AS "MaxEdu_Code", "Base5"."c11" AS "c11", "Base5"."BIL_Program_Entrance_Code" AS "BIL_Program_Entrance_Code", "Base5"."WorkVisa_Status_Code" AS "WorkVisa_Status_Code", "Base5"."Train_Req_Status_Code" AS "Train_Req_Status_Code", "Base5"."Promotion_Status" AS "Promotion_Status", "Base5"."Branch_Year" AS "Branch_Year", "Base5"."Cert_Type_Code" AS "Cert_Type_Code", "Base5"."Date_of_Entry_to_CurrZip" AS "Date_of_Entry_to_CurrZip", "Base5"."Visa_Employee" AS "Visa_Employee", "Base5"."Employee_Status_Code" AS "Employee_Status_Code", "Base5"."LOC_Type_Code" AS "LOC_Type_Code", "Base5"."Branch_Type_Code" AS "Branch_Type_Code", "Base5"."Review_Status_Code" AS "Review_Status_Code", "Base5"."ZIP_Code_of_Sales" AS "ZIP_Code_of_Sales", "Branchs"."Full_ZIP_Code" AS "ZIP_of_Sales", "Branchs"."Organization_Type_Code" AS "ZIP_of_Sales_Type", "Base5"."County_Code_of_Sales" AS "County_Code_of_Sales", "Base5"."c26" AS "c28", "Base5"."Employee_Name" AS "Employee_Name", "Base5"."Employee_Middle_Initial" AS "Employee_Middle_Initial", "Base5"."Employee_Last_Name" AS "Employee_Last_Name", "Base5"."EMP_BIRTHDATE" AS "EMP_BIRTHDATE", "Base5"."Employee_Birth_Date" AS "Employee_Birth_Date", "Base5"."Employee_First_Name" AS "Employee_First_Name" from (select "bupa00"."ARM_TYPE_LONG" AS "Organization_Type", "bupa00"."COUNTY_CODE" AS "County_Code", "bupa00"."COUNTY_NAME" AS "County_Name", "bupa01"."ARM_TYPE_LONG" AS "Branch_Type", case  when "bupa01"."ZIP_ID" = '6554' and "bupa00"."COUNTY_CODE" = '127040504’ then '8086' when "bupa01"."ZIP_ID" = '0654' and "bupa00"."COUNTY_CODE" = '127041904’ then '8044' when "bupa01"."ZIP_ID" = '0685' and "bupa00"."COUNTY_CODE" = '127045454’ then '8042' when "bupa01"."ZIP_ID" = '7156' and "bupa00"."COUNTY_CODE" = '103021904’ then '8094' when "bupa01"."ZIP_ID" = '6946' and "bupa00"."COUNTY_CODE" = '103022104’ then '8087' when "bupa01"."ZIP_ID" = '3277' and "bupa00"."COUNTY_CODE" = '123463804’ then '8049' when "bupa01"."ZIP_ID" = '6711' and "bupa00"."COUNTY_CODE" = '105257602' then '8071' when "bupa01"."ZIP_ID" = '2064’ and "bupa00"."COUNTY_CODE" = '105257602' then '8071' when "bupa01"."ZIP_ID" = '1062' and "bupa00"."COUNTY_CODE" = '122097204’ then '8114' when "bupa01"."ZIP_ID" = '3858' and "bupa00"."COUNTY_CODE" = '126515001' then '8119' when "bupa01"."ZIP_ID" = '0859' and "bupa00"."COUNTY_CODE" = '114067002' then '8116' when "bupa01"."ZIP_ID" = '0712' and "bupa00"."COUNTY_CODE" = '127046904’ then '8000' when "bupa01"."ZIP_ID" = '5246' and "bupa00"."COUNTY_CODE" = '104435604’ then '8094’ when "bupa01"."ZIP_ID" = '0911' and "bupa00"."COUNTY_CODE" = '108070502' then '0914’ when "bupa01"."ZIP_ID" = '3406' and "bupa00"."COUNTY_CODE" = '123468604’ then '5081' when "bupa01"."ZIP_ID" = '6300' and "bupa00"."COUNTY_CODE" = '113365304’ then '2649' when "bupa01"."ZIP_ID" = '6906' and "bupa00"."COUNTY_CODE" = '103028754’ then '0467' when "bupa01"."ZIP_ID" = '6930' and "bupa00"."COUNTY_CODE" = '118403302' then '7348' when "bupa01"."ZIP_ID" = '7074’ and "bupa00"."COUNTY_CODE" = '123468604’ then '5081' when "bupa01"."ZIP_ID" = '7201' and "bupa00"."COUNTY_CODE" = '116191504’ then '1619' when "bupa01"."ZIP_ID" = '7264' and "bupa00"."COUNTY_CODE" = '123468604’ then '5081' when "bupa01"."ZIP_ID" = '8042' and "bupa00"."COUNTY_CODE" = '127045454’ then ' 8044' else "bupa01"."ZIP_ID" end  AS "ZIP_ID", "bupa01"."ZIP_CODE" AS "Full_ZIP_Code", "bupa02"."EMPLOYEE_ID" AS "Employee_ID", "bupa02"."CURR_EXP_LVL" AS "Employee_Exp_Level", "bupa02"."EMPLOYEE_GENDER_CD" AS "Employee_Gender_Code", "bupa02"."ETHNIC_CODE" AS "MaxEdu_Code", "bupa02"."MASTERY_CODE" AS "c11", "bupa02"."LEP_PART_CODE" AS "BIL_Program_Entrance_Code", "bupa02"."WORKVISA_CODE" AS "WorkVisa_Status_Code", "bupa02"."TRAIN_REQ_CODE" AS "Train_Req_Status_Code", left("bupa02"."GRAD_STATUS", 4) AS "Promotion_Status", cast("bupa02"."BRANCH_YEAR" as date) AS "Branch_Year", "bupa02"."CERT_TYPE_CODE" AS "Cert_Type_Code", "bupa02"."ENTRY_DATE_FIRSTZIP" AS "Date_of_Entry_to_CurrZip", "bupa02"."FOREIGN_EMPLOYEE" AS "Visa_Employee", "bupa02"."STATUS_CODE" AS "Employee_Status_Code", "bupa00"."LEVEL_AGGR" AS "LOC_Type_Code", "bupa01"."ARM_TYPE_CODE" AS "Branch_Type_Code", "bupa02"."REVIEW_CODE" AS "Review_Status_Code", "bupa02"."SALES_ZIP" AS "ZIP_Code_of_Sales", "bupa02"."SALES_COUNTY" AS "County_Code_of_Sales", case  when cast("bupa02"."BRANCH_YEAR" as date) = {d '2012-04-15'} then "bupa02"."EMPLOYEE_ID" else NULL end  AS "c26", "bupa02"."EMPLOYEE_NAME" AS "Employee_Name", "bupa02"."EMPLOYEE_MID_INIT" AS "Employee_Middle_Initial", "bupa02"."EMPLOYEE_LAST_NM" AS "Employee_Last_Name", "bupa02"."EMP_BIRTHDATE" AS "EMP_BIRTHDATE", upper (left(replace(replace("bupa02"."EMPLOYEE_FIRST_NM", '.', ''), ',', ''), 20)) AS "Employee_First_Name", case  when  NOT "bupa02"."EMP_BIRTHDATE" is null then rtrim(case  when month("bupa02"."EMP_BIRTHDATE") < 10 then '0' else '' end +convert(char,(month("bupa02"."EMP_BIRTHDATE") * 1000000 + day("bupa02"."EMP_BIRTHDATE") * 10000) + year("bupa02"."EMP_BIRTHDATE"))) else NULL end  AS "Employee_Birth_Date" from "DBO"."COUNTY" "bupa00", "DBO"."ZIP" "bupa01", "CONSTRCT"."HOD"."tblEmployeeMLEVEL0987" "bupa02", (select distinct "Except1"."Employee_ID" AS "Employee_ID" from (select distinct "Employee"."EMPLOYEE_ID" AS "Employee_ID" from "DBO"."COUNTY" "County", "DBO"."ZIP" "ZIP", "CONSTRCT"."HOD"."tblEmployeeMLEVEL0987" "Employee" where (CAST( CONVERT( CHAR(10), cast("Employee"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), DATEADD( YEAR, -3, convert(DATE, {d '2012-04-15'}) ), 121 ) AS VARCHAR(20) ) or CAST( CONVERT( CHAR(10), cast("Employee"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), DATEADD( YEAR, -2, convert(DATE, {d '2012-04-15'}) ), 121 ) AS VARCHAR(20) ) or CAST( CONVERT( CHAR(10), cast("Employee"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), DATEADD( YEAR, -1, convert(DATE, {d '2012-04-15'}) ), 121 ) AS VARCHAR(20) ) or CAST( CONVERT( CHAR(10), cast("Employee"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), convert(DATE, {d '2012-04-15'}), 121 ) AS VARCHAR(20) )) and "Employee"."CURR_EXP_LVL" in (‘09’, ‘00’, ‘0H’, ‘0G’, ‘SG’) and ("Employee"."STATUS_CODE" is null or "Employee"."STATUS_CODE" <> 'A') and ("County"."ARM_TYPE_LONG" in (‘SR’, ‘U1’, ‘TS’, 'SJCI') or "ZIP"."ARM_TYPE_CODE" = 'TOMCKT') and "County"."LEVEL_AGGR"  NOT  in (‘AS’, ‘PI’,’RI’) and "County"."COUNTY_CODE" in ('100510000', '101260304’, '101260804’, '101261302', '101262507', '101262904’, '101264004’, '101266007', '101268004’, '101301304’, '101301404’, '101302607', '101303504’, '101306504’, '101308504’, '101630504', '101630904’, '101631004’, '101631204’, '101631504’, '101631704’, '101631804’, '101631904’, ‘301632404’, ‘301633904’, ‘301634207', ‘301636504’, ‘301637002', ‘301638004’, ‘301638804’, ‘301638907', ‘301833400', ‘302020001', ‘302020002', ‘302020004’, ‘302023030', ‘302023080', ‘302023180', ‘302025007', ‘302027451', ‘303020001', ‘303020002', ‘303020004’, ‘303020004', ‘303020005', ‘303020407', ‘303020604’, ‘303020754’, ‘303021004’, ‘303021102', ‘303021252', ‘303021454’, ‘303021604’, ‘303021752', ‘303021904’, ‘303022104’, ‘303022254’, ‘303022481', ‘303022504’, ‘303022804’, ‘303023090', ‘303023154’, ‘303023410', ‘303023807', ‘303023912', ‘303024102', ‘303024604’, ‘303024754’, ‘303025002', ‘303025206', ‘303026002', ‘303026037', ‘303026304’, ‘303026344’, ‘303026402', ‘303026852', ‘303026874’, ‘303026902', ‘303027307', ‘303027352', ‘303027504’, ‘303027754’, ‘303028192', ‘303028204’, ‘303028246', ‘303028302', ‘303028654’, ‘303028704’, ‘303028754’, ‘303028807', ‘303028834’, ‘303028854’, ‘303029204’, ‘303029404’, ‘303029554’, ‘303029604’, ‘303029804’, ‘303029902', ‘303519376', ‘304101252', ‘304101307', ‘304103604’, ‘304105004’, ‘304105354’, ‘304107504’, ‘304107804’, ‘304107904’, ‘304372004’, ‘304374004’, ‘304374207', ‘304375004’, ‘304375204’, ‘304375302', ‘304376204’, ‘304377004’, ‘304378004’, ‘304431304', ‘304432504’, ‘304432804’, ‘304432830', ‘304432904’, ‘304433304’, ‘304433604', ‘304433904’, ‘304435004’, ‘304435107', ‘304435304’, ‘304435604’, ‘304435704’, ‘304437504’, ‘304510394', ‘305201034’, ‘305201352', ‘305201407', ‘305204704’, ‘305250001', ‘305250004', ‘305251454’, ‘305252507', ‘305252602', ‘305252807', ‘305252920', ‘305253304’, ‘305253554’, ‘305253904’, ‘305254054’, ‘305254354’, ‘305256554’, ‘305257512', ‘305257602', ‘305258304’, ‘305258504’, ‘305259104’, ‘305259704’, ‘305620001', ‘305628007', ‘305628302', ‘306160304’, ‘306161204’, ‘306161357', ‘306161704’, ‘306166504’, ‘306167504', ‘306168004’, ‘306169004’, ‘306172004’, ‘306272004’, ‘306330704’, ‘306330804’, ‘306333407', ‘306338004’, ‘306611304’, ‘306612204’, ‘306616204’, ‘306617204’, ‘306618604’, ‘306619107', ‘307650604’, ‘307650704’, ‘307651207', ‘307651604’, ‘307652207', ‘307652604’, ‘307653040', ‘307653102', ‘307653204’, ‘307653802', ‘307654104’, ‘307654404’, ‘307654904’, ‘307655804’, ‘307655904’, ‘307656304’, ‘307656407', ‘307656502', ‘307657104’, ‘307657504’, ‘307658904’, ‘308051004’, ‘308051307', ‘308051504’, ‘308053004’, ‘308056004', ‘308057079', ‘308058004’, ‘308070001', ‘308070502', ‘308070607', ‘308071004’, ‘308071504', ‘308073504’, ‘308077504’, ‘308078004’, ‘308079004', ‘308110307', ‘308110604’, ‘308111204’, ‘308111304’, ‘308111404’, ‘308112004’, ‘308112204’, ‘308112502', ‘308112607', ‘308114504’, ‘308116004’, ‘308116304’, ‘308116504’, ‘308118504’, ‘308515107', ‘308561004’, ‘308561804’, ‘308565204’, ‘308565504’, ‘308566304’, ‘308567004', ‘308567204', ‘308567404', ‘308567704’, ‘308567807', ‘308568404', ‘308569104’, ‘309122704’, ‘309243504’, ‘309246004’, ‘309248004’, ‘309420107', ‘309420804’, ‘309422304’, ‘309426004’, ‘309426304’, ‘309427504’, ‘309530304', ‘309531304', ‘309532804', ‘309535504', ‘309537504', ‘310140001', ‘310141004’, ‘310141104’, ‘310141607', ‘310143060', ‘310143120', ‘310143310', ‘310147004’, ‘310148002', ‘310171004’, ‘310171607', ‘310171804’, ‘310173004’, ‘310173504', ‘310175004’, ‘310177004’, ‘310179004’, ‘310183602', ‘310183707', ‘311291304', ‘311292304', ‘311292507', ‘311297504', ‘311312504’, ‘311312607', ‘311312804', ‘311315438', ‘311316004’, ‘311317504’, ‘311343604’, ‘311440001', ‘311444207', ‘311444602', ‘312009104’, ‘312009604’, ‘312013054', ‘312013754’, ‘312015204’, ‘312018524’, ‘312281302', ‘312282004', ‘312282307', ‘312283004’, ‘312286004’, ‘312289004’, ‘312671304’, ‘312671604’, ‘312671804’, ‘312672204’, ‘312672804’, ‘312673300', ‘312673500', ‘312674404’, ‘312675504’, ‘312676204’, ‘312676404’, ‘312676504’, ‘312676704’, ‘312678504’, ‘312679002', ‘312679107', ‘312679404’, ‘313361304’, ‘313361504’, ‘313361704’, ‘313362204’, ‘313362304’, ‘313362404’, ‘313362604’, ‘313362940', ‘313363104’, ‘313363604’, ‘313363807', ‘313364002', ‘313364404’, ‘313364504’, ‘313365204’, ‘313365304’, ‘313367004’, ‘313369004’, ‘313380304’, ‘313381304’, ‘313382304’, ‘313384307', ‘313384604’, ‘313385004’, ‘313385304’, ‘314060392', ‘314060504’, ‘314060557', ‘314060754’, ‘314060854’, ‘314061104’, ‘314061504’, ‘314062004’, ‘314062504’, ‘314063004’, ‘314063504’, ‘314064004’, ‘314065504’, ‘314066504’, ‘314067002', ‘314067107', ‘314067504’, ‘314068004’, ‘314068104’, ‘314069104’, ‘314069354’, ‘314514135', ‘315210504’, ‘315211004’, ‘315211104’, ‘315211604’, ‘315211657', ‘315212504’, ‘315216504’, ‘315218004’, ‘315218304’, ‘315219002', ‘315220001', ‘315220002', ‘315220004’, ‘315221402', ‘315221607', ‘315221754’, ‘315222504', ‘315222752', ‘315223050', ‘315224004’, ‘315226004’, ‘315226104’, ‘315228004’, ‘315228304’, ‘315229004’, ‘315503004', ‘315504004’, ‘315506004’, ‘315508004’, ‘315674604’, ‘316191004', ‘316191104’, ‘316191204’, ‘316191504’, ‘316191757', ‘316195004', ‘316197504’, ‘316471804’, ‘316493130', ‘316493504’, ‘316495004’, ‘316495104’, ‘316495207', ‘316496504’, ‘316496604’, ‘316498004’, ‘316555004’, ‘316557104’, ‘316604004’, ‘316605004’, ‘316606707', ‘317080504’, ‘317080607', ‘317081004’, ‘317083004', ‘317086004’, ‘317086504’, ‘317086654’, ‘317089004’, ‘317412004’, ‘317414004’, ‘317414204’, ‘317414807', ‘317415004', ‘317415104’, ‘317415304’, ‘317416104’, ‘317417202', ‘317576304’, ‘317596004’, ‘317597004’, ‘317598504’, ‘318400001', ‘318401404’, ‘318401604’, ‘318402604’, ‘318403004’, ‘318403207', ‘318403302', ‘318403904’, ‘318406004’, ‘318406602', ‘318408607', ‘318408707', ‘318408852', ‘318409204’, ‘318409302', ‘318667504’, ‘319350001', ‘319350304’, ‘319351304’, ‘319352204’, ‘319354207', ‘319354604’, ‘319355504’, ‘319356504’, ‘319356604’, ‘319357004’, ‘319357402', ‘319358404’, ‘319581004’, ‘319582504’, ‘319583004’, ‘319584504’, ‘319584604’, ‘319584707', ‘319586504’, ‘319648304’, ‘319648704’, ‘319648904’, ‘319665004’, ‘320450002', ‘320450004’, ‘320452004’, ‘320454507', ‘320455204’, ‘320455404’, ‘320456004’, ‘320480001', ‘320480002', ‘320480804’, ‘320481002', ‘320481107', ‘320483007', ‘320483170', ‘320483302', ‘320484804’, ‘320484904’, ‘320485604’, ‘320486004’, ‘320488604’, ‘320522004’, ‘321131507', ‘321135004’, ‘321135504’, ‘321136504’, ‘321136604’, ‘321139004', ‘321390302', ‘321391304’, ‘321392304’, ‘321393007', ‘321393330', ‘321394504’, ‘321394604’, ‘321395104’, ‘321395604’, ‘321395704’, ‘321397804’, ‘322090001', ‘322091002', ‘322091304’, ‘322091352', ‘322091457', ‘322092002', ‘322092102', ‘322092354’, ‘322093140', ‘322093460', ‘322097007', ‘322097204’, ‘322097502', ‘322097604', ‘322098004’, ‘322098104’, ‘322098202', ‘322098404’, ‘322099007', ‘323460001', ‘323460302', ‘323460504', ‘323460957', ‘323461302', ‘323461602', ‘323463370', ‘323463507', ‘323463604’, ‘323463804’, ‘323464502', ‘323464604’, ‘323465304’, ‘323465507', ‘323465602', ‘323465702', ‘323466104’, ‘323466304’, ‘323466404’, ‘323467104’, ‘323467204’, ‘323467304’, ‘323468304’, ‘323468402', ‘323468504’, ‘323468604’, ‘323469007', ‘323469304’, ‘324150001', ‘324150002', ‘324150004’, ‘324150004', ‘324150005', ‘324150504’, ‘324151607', ‘324151902', ‘324152004’, ‘324152880', ‘324153320', ‘324153350', ‘324153504’, ‘324153510', ‘324154004’, ‘324156504’, ‘324156604’, ‘324156704’, ‘324157204’, ‘324157802', ‘324158504’, ‘324159002', ‘325230001', ‘325230002', ‘325231232', ‘325231304’, ‘325232407', ‘325232900', ‘325232950', ‘325234104’, ‘325234502', ‘325235104’, ‘325235502', ‘325236904’, ‘325237604’, ‘325237702', ‘325237904’, ‘325238402', ‘325238502', ‘325239452', ‘325239604’, ‘325239652', ‘326510001', ‘326510002', ‘326510004’, ‘326510004', ‘326510005', ‘326510006', ‘326510007', ‘326510008', ‘326510009', ‘326510010', ‘326510011', ‘326510014’, ‘326510014', ‘326510015', ‘326510016', ‘326510017', ‘326510018', ‘326510019', ‘326510020', ‘326510021', ‘326510022', ‘326510024’, ‘326511748', ‘326512840', ‘326512850', ‘326512860', ‘326512870', ‘326512960', ‘326512980', ‘326512990', ‘326513000', ‘326513020', ‘326513070', ‘326513100', ‘326513110', ‘326513150', ‘326513160', ‘326513190', ‘326513200', ‘326513210', ‘326513220', ‘326513230', ‘326513240', ‘326513250', ‘326513260', ‘326513270', ‘326513280', ‘326513290', ‘326513380', ‘326513400', ‘326513415', ‘326513420', ‘326513440', ‘326513450', ‘326513452', ‘326513470', ‘326513480', ‘326513490', ‘326513510', ‘326513734', ‘326514007', ‘326515001', ‘326516457', ‘326517286', ‘326517648', ‘326518547', ‘326519434’, ‘326519434', ‘326519476', ‘327040001', ‘327040002', ‘327040504’, ‘327040704’, ‘327041204’, ‘327041307', ‘327041504’, ‘327041604’, ‘327041904’, ‘327042004’, ‘327042854’, ‘327043430', ‘327044104’, ‘327045304’, ‘327045454’, ‘327045654’, ‘327045854’, ‘327046904’, ‘327047404', ‘327049304’, ‘328030604’, ‘328030852', ‘328033054’, ‘328034504’, ‘328034607', ‘328321104’, ‘328323304’, ‘328323704’, ‘328324207', ‘328325204’, ‘328326304’, ‘328327304’, ‘328328004’, ‘329540804’, ‘329544504’, ‘329544704’, ‘329544907', ‘329545004’, ‘329546004’, ‘329546104’, ‘329546804’, ‘329546907', ‘329547204’, ‘329547304’, ‘329547604’, ‘329547804’, ‘329548804’, ‘333513315', ‘334677866', ‘339481451', ‘341019741', ‘347513704’, ‘351514721', ‘360028259', ‘368513758', ‘368518014’, ‘371510294’, ‘372510794’, ‘373515368', ‘375390169', ‘377518712', ‘381519176', ‘381677919', ‘382514568', ‘385515524’, ‘388392660', ‘389670676', ‘392518422', ‘397010542', ‘399025446') and "Employee"."ENTRY_DATE_FIRSTZIP" > convert(datetime2, '2009-04-15 00:00:00.000000000') and "Employee"."ENTRY_DATE_FIRSTZIP" <= convert(datetime2, '2009-04-15 00:00:00.000000000') and "County"."COUNTY_KEY" = "Employee"."COUNTY_KEY" and "ZIP"."ZIP_KEY" = "Employee"."ZIP_KEY" and (235005004 = 99999999999 or 235005004 = 235005004 or "County"."COUNTY_CODE" = 315220093) except select distinct "Union1"."Employee_ID" AS "Employee_ID" from (select distinct "Employee"."EMPLOYEE_ID" AS "Employee_ID" from "CONSTRCT"."HOD"."tblEmployeeMLEVEL0987" "Employee", "DBO"."KNOWLEDGE_INFO" "Knowledge_Attributes", "DBO"."KNOWLEDGE_FACT" "Knowledge_Passdown_Scores" where "Knowledge_Attributes"."PEER_GROUP" = 'NASDUR' and (cast("Knowledge_Passdown_Scores"."BRANCH_YEAR" as date) = DATEADD( YEAR, -3, convert(DATE, {d '2012-04-15'}) ) or cast("Knowledge_Passdown_Scores"."BRANCH_YEAR" as date) = DATEADD( YEAR, -2, convert(DATE, {d '2012-04-15'}) ) or cast("Knowledge_Passdown_Scores"."BRANCH_YEAR" as date) = DATEADD( YEAR, -1, convert(DATE, {d '2012-04-15'}) ) or cast("Knowledge_Passdown_Scores"."BRANCH_YEAR" as date) = {d '2012-04-15'}) and "Employee"."EMPLOYEE_KEY" = "Knowledge_Passdown_Scores"."EMPLOYEE_KEY" and "Knowledge_Attributes"."TEST_KEY" = "Knowledge_Passdown_Scores"."TEST_KEY" union  select "MLEVEL_PROMOTION_RATE_FINAL"."EMPLOYEE_ID" AS "Employee_ID" from "CONSTRCT"."HOD"."tblEmployeeMLEVEL0987" "MLEVEL_PROMOTION_RATE_FINAL" where "MLEVEL_PROMOTION_RATE_FINAL"."CURR_EXP_LVL" in (‘09’, ‘00’, ‘0H’, ‘0G’, ‘SG’) and "MLEVEL_PROMOTION_RATE_FINAL"."STATUS_CODE" in (‘R’, ‘C’) and "MLEVEL_PROMOTION_RATE_FINAL"."ENTRY_DATE_FIRSTZIP" > convert(datetime2, '2009-04-15 00:00:00.000000000') and "MLEVEL_PROMOTION_RATE_FINAL"."ENTRY_DATE_FIRSTZIP" <= convert(datetime2, '2009-04-15 00:00:00.000000000') and (cast("MLEVEL_PROMOTION_RATE_FINAL"."BRANCH_YEAR" as date) = DATEADD( YEAR, -3, convert(DATE, {d '2012-04-15'}) ) or cast("MLEVEL_PROMOTION_RATE_FINAL"."BRANCH_YEAR" as date) = DATEADD( YEAR, -2, convert(DATE, {d '2012-04-15'}) ) or cast("MLEVEL_PROMOTION_RATE_FINAL"."BRANCH_YEAR" as date) = DATEADD( YEAR, -1, convert(DATE, {d '2012-04-15'}) ) or cast("MLEVEL_PROMOTION_RATE_FINAL"."BRANCH_YEAR" as date) = {d '2012-04-15'})) "Union1"("Employee_ID")) "Except1"("Employee_ID")) "Avoid_IDs" where "bupa02"."EMPLOYEE_ID" = "Avoid_IDs"."Employee_ID" and (CAST( CONVERT( CHAR(10), cast("bupa02"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), DATEADD( YEAR, -3, convert(DATE, {d '2012-04-15'}) ), 121 ) AS VARCHAR(20) ) or CAST( CONVERT( CHAR(10), cast("bupa02"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), DATEADD( YEAR, -2, convert(DATE, {d '2012-04-15'}) ), 121 ) AS VARCHAR(20) ) or CAST( CONVERT( CHAR(10), cast("bupa02"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), DATEADD( YEAR, -1, convert(DATE, {d '2012-04-15'}) ), 121 ) AS VARCHAR(20) ) or CAST( CONVERT( CHAR(10), cast("bupa02"."BRANCH_YEAR" as date), 121 ) AS VARCHAR(20) ) = CAST( CONVERT( CHAR(10), convert(DATE, {d '2012-04-15'}), 121 ) AS VARCHAR(20) )) and "bupa02"."CURR_EXP_LVL" in (‘09’, ‘00’, ‘0H’, ‘0G’, ‘SG’) and ("bupa02"."STATUS_CODE" is null or "bupa02"."STATUS_CODE" <> 'A') and ("bupa00"."ARM_TYPE_LONG" in (‘SR’, ‘U1’, ‘TS’, 'SJCI') or "bupa01"."ARM_TYPE_CODE" = 'TOMCKT') and "bupa00"."LEVEL_AGGR"  NOT  in (‘AS’, ‘PI’,’RI’) and "bupa00"."COUNTY_CODE" in (‘300510000', ‘301260304’, ‘301260804’, ‘301261302', ‘301262507', ‘301262904’, ‘301264004’, ‘301266007', ‘301268004’, ‘301301304’, ‘301301404’, ‘301302607', ‘301303504’, ‘301306504’, ‘301308504’, ‘301630504', ‘301630904’, ‘301631004’, ‘301631204’, ‘301631504’, ‘301631704’, ‘301631804’, ‘301631904’, ‘301632404’, ‘301633904’, ‘301634207', ‘301636504’, ‘301637002', ‘301638004’, ‘301638804’, ‘301638907', ‘301833400', ‘302020001', ‘302020002', ‘302020004’, ‘302023030', ‘302023080', ‘302023180', ‘302025007', ‘302027451', ‘303020001', ‘303020002', ‘303020004’, ‘303020004', ‘303020005', ‘303020407', ‘303020604’, ‘303020754’, ‘303021004’, ‘303021102', ‘303021252', ‘303021454’, ‘303021604’, ‘303021752', ‘303021904’, ‘303022104’, ‘303022254’, ‘303022481', ‘303022504’, ‘303022804’, ‘303023090', ‘303023154’, ‘303023410', ‘303023807', ‘303023912', ‘303024102', ‘303024604’, ‘303024754’, ‘303025002', ‘303025206', ‘303026002', ‘303026037', ‘303026304’, ‘303026344’, ‘303026402', ‘303026852', ‘303026874’, ‘303026902', ‘303027307', ‘303027352', ‘303027504’, ‘303027754’, ‘303028192', ‘303028204’, ‘303028246', ‘303028302', ‘303028654’, ‘303028704’, ‘303028754’, ‘303028807', ‘303028834’, ‘303028854’, ‘303029204’, ‘303029404’, ‘303029554’, ‘303029604’, ‘303029804’, ‘303029902', ‘303519376', ‘304101252', ‘304101307', ‘304103604’, ‘304105004’, ‘304105354’, ‘304107504’, ‘304107804’, ‘304107904’, ‘304372004’, ‘304374004’, ‘304374207', ‘304375004’, ‘304375204’, ‘304375302', ‘304376204’, ‘304377004’, ‘304378004’, ‘304431304', ‘304432504’, ‘304432804’, ‘304432830', ‘304432904’, ‘304433304’, ‘304433604', ‘304433904’, ‘304435004’, ‘304435107', ‘304435304’, ‘304435604’, ‘304435704’, ‘304437504’, ‘304510394', ‘305201034’, ‘305201352', ‘305201407', ‘305204704’, ‘305250001', ‘305250004', ‘305251454’, ‘305252507', ‘305252602', ‘305252807', ‘305252920', ‘305253304’, ‘305253554’, ‘305253904’, ‘305254054’, ‘305254354’, ‘305256554’, ‘305257512', ‘305257602', ‘305258304’, ‘305258504’, ‘305259104’, ‘305259704’, ‘305620001', ‘305628007', ‘305628302', ‘306160304’, ‘306161204’, ‘306161357', ‘306161704’, ‘306166504’, ‘306167504', ‘306168004’, ‘306169004’, ‘306172004’, ‘306272004’, ‘306330704’, ‘306330804’, ‘306333407', ‘306338004’, ‘306611304’, ‘306612204’, ‘306616204’, ‘306617204’, ‘306618604’, ‘306619107', ‘307650604’, ‘307650704’, ‘307651207', ‘307651604’, ‘307652207', ‘307652604’, ‘307653040', ‘307653102', ‘307653204’, ‘307653802', ‘307654104’, ‘307654404’, ‘307654904’, ‘307655804’, ‘307655904’, ‘307656304’, ‘307656407', ‘307656502', ‘307657104’, ‘307657504’, ‘307658904’, ‘308051004’, ‘308051307', ‘308051504’, ‘308053004’, ‘308056004', ‘308057079', ‘308058004’, ‘308070001', ‘308070502', ‘308070607', ‘308071004’, ‘308071504', ‘308073504’, ‘308077504’, ‘308078004’, ‘308079004', ‘308110307', ‘308110604’, ‘308111204’, ‘308111304’, ‘308111404’, ‘308112004’, ‘308112204’, ‘308112502', ‘308112607', ‘308114504’, ‘308116004’, ‘308116304’, ‘308116504’, ‘308118504’, ‘308515107', ‘308561004’, ‘308561804’, ‘308565204’, ‘308565504’, ‘308566304’, ‘308567004', ‘308567204', ‘308567404', ‘308567704’, ‘308567807', ‘308568404', ‘308569104’, ‘309122704’, ‘309243504’, ‘309246004’, ‘309248004’, ‘309420107', ‘309420804’, ‘309422304’, ‘309426004’, ‘309426304’, ‘309427504’, ‘309530304', ‘309531304', ‘309532804', ‘309535504', ‘309537504', ‘310140001', ‘310141004’, ‘310141104’, ‘310141607', ‘310143060', ‘310143120', ‘310143310', ‘310147004’, ‘310148002', ‘310171004’, ‘310171607', ‘310171804’, ‘310173004’, ‘310173504', ‘310175004’, ‘310177004’, ‘310179004’, ‘310183602', ‘310183707', ‘311291304', ‘311292304', ‘311292507', ‘311297504', ‘311312504’, ‘311312607', ‘311312804', ‘311315438', ‘311316004’, ‘311317504’, ‘311343604’, ‘311440001', ‘311444207', ‘311444602', ‘312009104’, ‘312009604’, ‘312013054', ‘312013754’, ‘312015204’, ‘312018524’, ‘312281302', ‘312282004', ‘312282307', ‘312283004’, ‘312286004’, ‘312289004’, ‘312671304’, ‘312671604’, ‘312671804’, ‘312672204’, ‘312672804’, ‘312673300', ‘312673500', ‘312674404’, ‘312675504’, ‘312676204’, ‘312676404’, ‘312676504’, ‘312676704’, ‘312678504’, ‘312679002', ‘312679107', ‘312679404’, ‘313361304’, ‘313361504’, ‘313361704’, ‘313362204’, ‘313362304’, ‘313362404’, ‘313362604’, ‘313362940', ‘313363104’, ‘313363604’, ‘313363807', ‘313364002', ‘313364404’, ‘313364504’, ‘313365204’, ‘313365304’, ‘313367004’, ‘313369004’, ‘313380304’, ‘313381304’, ‘313382304’, ‘313384307', ‘313384604’, ‘313385004’, ‘313385304’, ‘314060392', ‘314060504’, ‘314060557', ‘314060754’, ‘314060854’, ‘314061104’, ‘314061504’, ‘314062004’, ‘314062504’, ‘314063004’, ‘314063504’, ‘314064004’, ‘314065504’, ‘314066504’, ‘314067002', ‘314067107', ‘314067504’, ‘314068004’, ‘314068104’, ‘314069104’, ‘314069354’, ‘314514135', ‘315210504’, ‘315211004’, ‘315211104’, ‘315211604’, ‘315211657', ‘315212504’, ‘315216504’, ‘315218004’, ‘315218304’, ‘315219002', ‘315220001', ‘315220002', ‘315220004’, ‘315221402', ‘315221607', ‘315221754’, ‘315222504', ‘315222752', ‘315223050', ‘315224004’, ‘315226004’, ‘315226104’, ‘315228004’, ‘315228304’, ‘315229004’, ‘315503004', ‘315504004’, ‘315506004’, ‘315508004’, ‘315674604’, ‘316191004', ‘316191104’, ‘316191204’, ‘316191504’, ‘316191757', ‘316195004', ‘316197504’, ‘316471804’, ‘316493130', ‘316493504’, ‘316495004’, ‘316495104’, ‘316495207', ‘316496504’, ‘316496604’, ‘316498004’, ‘316555004’, ‘316557104’, ‘316604004’, ‘316605004’, ‘316606707', ‘317080504’, ‘317080607', ‘317081004’, ‘317083004', ‘317086004’, ‘317086504’, ‘317086654’, ‘317089004’, ‘317412004’, ‘317414004’, ‘317414204’, ‘317414807', ‘317415004', ‘317415104’, ‘317415304’, ‘317416104’, ‘317417202', ‘317576304’, ‘317596004’, ‘317597004’, ‘317598504’, ‘318400001', ‘318401404’, ‘318401604’, ‘318402604’, ‘318403004’, ‘318403207', ‘318403302', ‘318403904’, ‘318406004’, ‘318406602', ‘318408607', ‘318408707', ‘318408852', ‘318409204’, ‘318409302', ‘318667504’, ‘319350001', ‘319350304’, ‘319351304’, ‘319352204’, ‘319354207', ‘319354604’, ‘319355504’, ‘319356504’, ‘319356604’, ‘319357004’, ‘319357402', ‘319358404’, ‘319581004’, ‘319582504’, ‘319583004’, ‘319584504’, ‘319584604’, ‘319584707', ‘319586504’, ‘319648304’, ‘319648704’, ‘319648904’, ‘319665004’, ‘320450002', ‘320450004’, ‘320452004’, ‘320454507', ‘320455204’, ‘320455404’, ‘320456004’, ‘320480001', ‘320480002', ‘320480804’, ‘320481002', ‘320481107', ‘320483007', ‘320483170', ‘320483302', ‘320484804’, ‘320484904’, ‘320485604’, ‘320486004’, ‘320488604’, ‘320522004’, ‘321131507', ‘321135004’, ‘321135504’, ‘321136504’, ‘321136604’, ‘321139004', ‘321390302', ‘321391304’, ‘321392304’, ‘321393007', ‘321393330', ‘321394504’, ‘321394604’, ‘321395104’, ‘321395604’, ‘321395704’, ‘321397804’, ‘322090001', ‘322091002', ‘322091304’, ‘322091352', ‘322091457', ‘322092002', ‘322092102', ‘322092354’, ‘322093140', ‘322093460', ‘322097007', ‘322097204’, ‘322097502', ‘322097604', ‘322098004’, ‘322098104’, ‘322098202', ‘322098404’, ‘322099007', ‘323460001', ‘323460302', ‘323460504', ‘323460957', ‘323461302', ‘323461602', ‘323463370', ‘323463507', ‘323463604’, ‘323463804’, ‘323464502', ‘323464604’, ‘323465304’, ‘323465507', ‘323465602', ‘323465702', ‘323466104’, ‘323466304’, ‘323466404’, ‘323467104’, ‘323467204’, ‘323467304’, ‘323468304’, ‘323468402', ‘323468504’, ‘323468604’, ‘323469007', ‘323469304’, ‘324150001', ‘324150002', ‘324150004’, ‘324150004', ‘324150005', ‘324150504’, ‘324151607', ‘324151902', ‘324152004’, ‘324152880', ‘324153320', ‘324153350', ‘324153504’, ‘324153510', ‘324154004’, ‘324156504’, ‘324156604’, ‘324156704’, ‘324157204’, ‘324157802', ‘324158504’, ‘324159002', ‘325230001', ‘325230002', ‘325231232', ‘325231304’, ‘325232407', ‘325232900', ‘325232950', ‘325234104’, ‘325234502', ‘325235104’, ‘325235502', ‘325236904’, ‘325237604’, ‘325237702', ‘325237904’, ‘325238402', ‘325238502', ‘325239452', ‘325239604’, ‘325239652', ‘326510001', ‘326510002', ‘326510004’, ‘326510004', ‘326510005', ‘326510006', ‘326510007', ‘326510008', ‘326510009', ‘326510010', ‘326510011', ‘326510014’, ‘326510014', ‘326510015', ‘326510016', ‘326510017', ‘326510018', ‘326510019', ‘326510020', ‘326510021', ‘326510022', ‘326510024’, ‘326511748', ‘326512840', ‘326512850', ‘326512860', ‘326512870', ‘326512960', ‘326512980', ‘326512990', ‘326513000', ‘326513020', ‘326513070', ‘326513100', ‘326513110', ‘326513150', ‘326513160', ‘326513190', ‘326513200', ‘326513210', ‘326513220', ‘326513230', ‘326513240', ‘326513250', ‘326513260', ‘326513270', ‘326513280', ‘326513290', ‘326513380', ‘326513400', ‘326513415', ‘326513420', ‘326513440', ‘326513450', ‘326513452', ‘326513470', ‘326513480', ‘326513490', ‘326513510', ‘326513734', ‘326514007', ‘326515001', ‘326516457', ‘326517286', ‘326517648', ‘326518547', ‘326519434’, ‘326519434', ‘326519476', ‘327040001', ‘327040002', ‘327040504’, ‘327040704’, ‘327041204’, ‘327041307', ‘327041504’, ‘327041604’, ‘327041904’, ‘327042004’, ‘327042854’, ‘327043430', ‘327044104’, ‘327045304’, ‘327045454’, ‘327045654’, ‘327045854’, ‘327046904’, ‘327047404', ‘327049304’, ‘328030604’, ‘328030852', ‘328033054’, ‘328034504’, ‘328034607', ‘328321104’, ‘328323304’, ‘328323704’, ‘328324207', ‘328325204’, ‘328326304’, ‘328327304’, ‘328328004’, ‘329540804’, ‘329544504’, ‘329544704’, ‘329544907', ‘329545004’, ‘329546004’, ‘329546104’, ‘329546804’, ‘329546907', ‘329547204’, ‘329547304’, ‘329547604’, ‘329547804’, ‘329548804’, ‘333513315', ‘334677866', ‘339481451', ‘341019741', ‘347513704’, ‘351514721', ‘360028259', ‘368513758', ‘368518014’, ‘371510294’, ‘372510794’, ‘373515368', ‘375390169', ‘377518712', ‘381519176', ‘381677919', ‘382514568', ‘385515524’, ‘388392660', ‘389670676', ‘392518422', ‘397010542', ‘399025446') and "bupa02"."ENTRY_DATE_FIRSTZIP" > convert(datetime2, '2009-04-15 00:00:00.000000000') and "bupa02"."ENTRY_DATE_FIRSTZIP" <= convert(datetime2, '2009-04-15 00:00:00.000000000') and "bupa00"."COUNTY_KEY" = "bupa02"."COUNTY_KEY" and "bupa01"."ZIP_KEY" = "bupa02"."ZIP_KEY" and (235005004 = 111111111 or 235005004 = 235005004 or "bupa00"."COUNTY_CODE" = 315220093)) "Base5" LEFT OUTER JOIN (select distinct "County"."COUNTY_CODE" AS "County_Code", case  when "ZIP"."ZIP_ID" = '6554' and "County"."COUNTY_CODE" = ‘327040504’ then '8086' when "ZIP"."ZIP_ID" = '0654' and "County"."COUNTY_CODE" = ‘327041904’ then '8044' when "ZIP"."ZIP_ID" = '0685' and "County"."COUNTY_CODE" = ‘327045454’ then '8042' when "ZIP"."ZIP_ID" = '7156' and "County"."COUNTY_CODE" = ‘303021904’ then '8094' when "ZIP"."ZIP_ID" = '6946' and "County"

Select allOpen in new window

thanks for the tip to over come the IN issue..

Yes, this is generated. That is what we would like changed and working with the vendor on that.

You mentioned 3 things that are done in the filter: recast, sub-selects, implied joins. Would the idea be to bring these 3 before the FILTER (where condition) and not after it?

thanks a lot.