 SELECT  distinct providers_new.*,
       Sqrt(Pow(111*(zip_codes.latitude-41.8945828), 2)
            + Pow(80*(zip_codes.longitude- -87.62317059999998), 2)) AS distance
FROM   providers_new
       LEFT JOIN zip_codes
              ON providers_new.provider_zip = zip_codes.zip_code
		LEFT JOIN client_regions region on region.region_id = providers_new.provider_region

left join specialties spa on spa.specialty = providers_new.provider_specialty_1
left join specialties spb on spb.specialty = providers_new.provider_specialty_2
left join specialties spc on spc.specialty = providers_new.provider_specialty_3
left join specialties spd on spd.specialty = providers_new.provider_specialty_4

WHERE  providers_new.provider_address_type = 'O'
       AND provider_effective_date <= '2014-08-19'
       AND ( provider_term_date >= '2014-08-19'
              OR provider_term_date IS NULL )
       AND providers_new.exclude_dir = 'N'
       AND Sqrt(Pow(111*(zip_codes.latitude-41.8945828), 2)
                + Pow(80*(zip_codes.longitude - -87.62317059999998), 2)) < 100
       
and (
    spa.search_include >= 1
        OR spb.search_include >= 2
        OR spc.search_include >= 3
        OR spd.search_include >= 4
    )
and (
    spa.class = 'H'
    OR spb.class = 'H'
    OR spc.class = 'H'
    OR spd.class = 'H'
)
       AND region.tpa_id = 'TEST'
	  AND region.client_id = 'smartcare'
	  AND region.effective_date <= '2014-08-19'
	  AND ( region.term_date >= '2014-08-19'
			 OR region.term_date IS NULL )
ORDER  BY distance,
          providers_new.provider_doctor_last_name,
          providers_new.provider_doctor_first_name  