select SQL_CALC_FOUND_ROWS HQ.Tag as AgencyTag, HQ.LocationsRecruitedFor AS LocationsRecruitedFor, HQ.RecruitmentTypesCovered AS RecruitmentTypesCovered, HQ.RecruitmentLevelsCovered AS RecruitmentLevelsCovered, HQ.WhatRecruitedFor AS WhatRecruitedFor, HQ.URL AS URL, HQ.EmployerURL AS EmployerURL, HQ.FAL AS FAL, HQ.Fax AS Fax, HQ.EmployerFax AS EmployerFax, HQ.AssistedContactTelephone AS HQAssistedContactTelephone, CONCAT_WS(", ", HQ.Address, HQ.Town, HQ.County, HQ.PostCode) AS HQAddress, OO.VirtualOffice as VO, CONCAT_WS(",", COALESCE(salary_range17k, ""), COALESCE(salary_range1725k, ""), COALESCE(salary_range2535k, ""), COALESCE(salary_range3560k, ""), COALESCE(salary_range60100k, ""), COALESCE(salary_range100k, "")) AS SalaryBreakdown, SUM(OO.Telephone <> "" OR OO.Fax <> "") AS CandTelFax, SUM(OO.EmployerTelephone <> "" OR OO.EmployerFax <> "") AS EmpTelFax, SUM(OO.Email <> "") AS CandEmail, SUM(OO.EmployerEmail <> "") AS EmpEmail, SUM(HQ.URL <> "") as CandWebsite, SUM(HQ.EmployerURL <> "") as EmpWebsite, SUM(OO.social_facebook <> "" OR HQ.social_facebook <> "") AS SocFacebook, SUM(OO.social_twitter <> "" OR HQ.social_twitter <> "") AS SocTwitter, TRIM(HQ.Name) AS Name, HQ.DateMemberSince AS date_member_since, HQ.DateEstablished AS date_established, HQ.Email as email, HQ.EmployerEmail as employer_email, HQ.social_twitter, HQ.social_facebook, HQ.flag_ai_strapline_summary as enable_ai_strapline_summary, IF(HQ.ExpectedSCP IS NULL, 1, 0) AS AgencyIsOnTrial, DATE_FORMAT(DateEnteredOnTrial, "%d/%m/%Y") AS DateActivated, coalesce(ExpectedSCP, 0) AS ExpectedScpValue, coalesce((select round(SUM(value) / 3, 2) from `agencycentral`.`AGDATA_90Day_SCP` where agency_tag = HQ.Tag), 0) As DeliveredScpValue, '4' as AgencyLevel, if (HQ.ExpectedSCP > 0,(coalesce((select sum(value) from `agencycentral`.`AGDATA_90Day_SCP` as `SCP` where SCP.agency_tag = HQ.Tag) / 3, 0) / POW(HQ.ExpectedSCP, '1.05' )),( '1.3635983899843' / coalesce((select `Multiplier` from `agencycentrallive`.`AgencyTrialMultiplier` where HQ.Tag = AgencyTag), 1))) as scp_ratio, MIN(if( OO.locationID <> 0 and OO.locationID is not null and (Level1 = '1925' or Level2 = '1925' or Level3 = '1925' or Level4 = '1925' ), 0, IF(OO.Longitude IS NOT NULL AND OO.Longitude <> 0 AND OO.Latitude IS NOT NULL AND OO.Latitude <> 0, (3963.1676 * ACOS( COS(RADIANS(90-OO.Latitude)) * COS(RADIANS(90- '51.5088214734' )) + SIN(RADIANS(90-OO.Latitude)) * SIN(RADIANS(90- '51.5088214734' )) * COS(RADIANS(OO.Longitude- '-0.014110233881' )) ) ), 1000) )) AS distance, COALESCE((null), (select `Data` from `Resources` where `key1` = 'construction' and `resourceType` = 'INDSTRAP' and key4 = HQ.Tag limit 1), (select `Data` from `Resources` where resourceType = "COSTRAP" and `key1` = 'construction' and `key2` = '1925' and `key3` = '0' and key4 = OO.Tag limit 1), (select `Data` from `Resources` where resourceType = "BYTOWNSTRAP" and `key1` = '1925' and `key2` = '0' and key4 = OO.Tag limit 1), HQ.BriefDescription) as strapline, COALESCE(IF(HQ.edited_description="Y",1,NULL),IF(HQ.edited_briefdescription="Y",1,NULL),IF(HQ.edited_employerdescription="Y",1,NULL)) as has_edited_strapline, COALESCE((null), (select `data_2` from `Resources` where `key1` = 'construction' and `resourceType` = 'INDSTRAP' and key4 = HQ.Tag limit 1), (select `data_2` from `Resources` where resourceType = "COSTRAP" and `key1` = 'construction' and `key2` = '1925' and `key3` = '0' and key4 = OO.Tag limit 1), (select `data_2` from `Resources` where resourceType = "BYTOWNSTRAP" and `key1` = '1925' and `key2` = '0' and key4 = OO.Tag limit 1), HQ.BriefDescription_AI) as ai_strapline, COALESCE(IF(HQ.edited_description="Y",1,NULL),IF(HQ.edited_briefdescription="Y",1,NULL),IF(HQ.edited_employerdescription="Y",1,NULL)) as has_edited_strapline, "" as subcat_list, concat_ws("", ( select `industry_name_1` from `ACVCIndustries` inner join `AgencyIndustries` on `AgencyIndustries`.`industry_key` = `ACVCIndustries`.`AC_industry_id` where AgencyIndustries.agency_tag = HQ.Tag and AgencyIndustries.industry_key = "construction" ), ",,", ( select GROUP_CONCAT(DISTINCT industry_name_1) from `ACVCIndustries` inner join `AgencyIndustries` on `AgencyIndustries`.`industry_key` = `ACVCIndustries`.`AC_industry_id` where AgencyIndustries.agency_tag = HQ.Tag and AgencyIndustries.industry_key <> "construction" ) ) as IndustryList , `ALA`.`activity_type` as `last_activity_type`, `ALA`.`activity_datetime` as `last_activity_date`, `ALA`.`avg_accept_rate` as `avg_accept_rate`, `ALA`.`avg_response_time` as `avg_response_time`, coalesce((select IF (res.data = 2, 1, 0) from `Resources` as `res` where res.resourceType = "SKLEVEL" and res.key1 = "construction" and res.key2 in("all", "allconstructionskills") and res.key3 = res.key2 and res.key4 = HQ.Tag limit 1), 0) as has_featured, ((IF ((select COUNT(*) from `agencycentrallive`.`SEARCH_AgencyOtherOffices` where HQ = HQ.Tag and `Deleted` <> 'yes' ) = 1, 2.4, IF ((select COUNT(*) from `agencycentrallive`.`SEARCH_AgencyOtherOffices` where HQ = HQ.Tag and `Deleted` <> 'yes' ) = 2, 2.1, IF((select COUNT(*) from `agencycentrallive`.`SEARCH_AgencyOtherOffices` where HQ = HQ.Tag and `Deleted` <> 'yes' ) = 3, 1.8, 1.5) ) ) * IF( MIN(if( OO.locationID <> 0 and OO.locationID is not null and (Level1 = '1925' or Level2 = '1925' or Level3 = '1925' or Level4 = '1925' ), 0, IF(OO.Longitude IS NOT NULL AND OO.Longitude <> 0 AND OO.Latitude IS NOT NULL AND OO.Latitude <> 0, (3963.1676 * ACOS( COS(RADIANS(90-OO.Latitude)) * COS(RADIANS(90- '51.5088214734' )) + SIN(RADIANS(90-OO.Latitude)) * SIN(RADIANS(90- '51.5088214734' )) * COS(RADIANS(OO.Longitude- '-0.014110233881' )) ) ), 1000) )) <= 10, 7, IF(MIN(if( OO.locationID <> 0 and OO.locationID is not null and (Level1 = '1925' or Level2 = '1925' or Level3 = '1925' or Level4 = '1925' ), 0, IF(OO.Longitude IS NOT NULL AND OO.Longitude <> 0 AND OO.Latitude IS NOT NULL AND OO.Latitude <> 0, (3963.1676 * ACOS( COS(RADIANS(90-OO.Latitude)) * COS(RADIANS(90- '51.5088214734' )) + SIN(RADIANS(90-OO.Latitude)) * SIN(RADIANS(90- '51.5088214734' )) * COS(RADIANS(OO.Longitude- '-0.014110233881' )) ) ), 1000) )) <= 30, 4, IF(MIN(if( OO.locationID <> 0 and OO.locationID is not null and (Level1 = '1925' or Level2 = '1925' or Level3 = '1925' or Level4 = '1925' ), 0, IF(OO.Longitude IS NOT NULL AND OO.Longitude <> 0 AND OO.Latitude IS NOT NULL AND OO.Latitude <> 0, (3963.1676 * ACOS( COS(RADIANS(90-OO.Latitude)) * COS(RADIANS(90- '51.5088214734' )) + SIN(RADIANS(90-OO.Latitude)) * SIN(RADIANS(90- '51.5088214734' )) * COS(RADIANS(OO.Longitude- '-0.014110233881' )) ) ), 1000) )) <= 100, 2, IF(MIN(if( OO.locationID <> 0 and OO.locationID is not null and (Level1 = '1925' or Level2 = '1925' or Level3 = '1925' or Level4 = '1925' ), 0, IF(OO.Longitude IS NOT NULL AND OO.Longitude <> 0 AND OO.Latitude IS NOT NULL AND OO.Latitude <> 0, (3963.1676 * ACOS( COS(RADIANS(90-OO.Latitude)) * COS(RADIANS(90- '51.5088214734' )) + SIN(RADIANS(90-OO.Latitude)) * SIN(RADIANS(90- '51.5088214734' )) * COS(RADIANS(OO.Longitude- '-0.014110233881' )) ) ), 1000) )) <= 250, 1.1, 0.65) ) ) ))) / (if (HQ.ExpectedSCP > 0,(coalesce((select sum(value) from `agencycentral`.`AGDATA_90Day_SCP` as `SCP` where SCP.agency_tag = HQ.Tag) / 3, 0) / POW(HQ.ExpectedSCP, '1.05' )),( '1.3635983899843' / coalesce((select `Multiplier` from `agencycentrallive`.`AgencyTrialMultiplier` where HQ.Tag = AgencyTag), 1)))) AS score from `agencycentrallive`.`SEARCH_Agencies` as `HQ` inner join `SEARCH_AgencyOtherOffices` as `OO` on `OO`.`HQ` = `HQ`.`Tag` left join `commondata`.`Location` as `l` on `l`.`LocationID` = IF(OO.locationID <> 0, OO.locationID, OO.TownLocationID) left join `SEARCH_AgencyLastActivity` as `ALA` on `HQ`.`Tag` = `ALA`.`tag` where (`l`.`Level1` in ( '1925' ) or `l`.`Level2` in ( '1925' ) or `l`.`Level3` in ( '1925' ) or `l`.`Level4` in ( '1925' )) and (select 1 from `AgencyIndustries` where agency_tag = HQ.Tag and `industry_key` = 'construction' limit 1) IS NOT NULL and (select MIN(data) from `Resources` where ResourceType = "COLEVEL" and `key1` = 'construction' and `key2` = '290' and `key3` = '0' and key4 = OO.Tag) IS NOT NULL and exists (select 1 from `agencycentrallive`.`AgencyLocationCounties` inner join `commondata`.`AggregateLocation` on `CountyID` = `URLCode` and `Level` = 2 where AgencyLocationCounties.AgencyTag = HQ.Tag and (`AggregateLocationID` in ( '290-0' ) or `SuperParentID` in ( '290-0' )) limit 1) and not exists (select 1 from `agencycentrallive`.`AgencyVisibility` where `key1` = 'construction' and `key2` = '1925' and `key3` = '' and key4 = HQ.Tag and key_value = 0) and `OO`.`VirtualOffice` = 'NO' and `OO`.`deleted` <> 'yes' and OO.HQ = HQ.Tag and `HQ`.`ActiveState` <> 'off' group by `HQ`.`Tag` having !IF (HQ.FAL = 'Y' AND has_edited_strapline is null,1,0) = 1 and (CandTelFax > 0 OR EmpTelFax > 0 OR CandEmail > 0 OR EmpEmail > 0 OR CandWebsite > 0 OR EmpWebsite > 0) order by `AgencyLevel` asc, ((IF ((select COUNT(*) from `agencycentrallive`.`SEARCH_AgencyOtherOffices` where HQ = HQ.Tag and `Deleted` <> 'yes' ) = 1, 2.4, IF ((select COUNT(*) from `agencycentrallive`.`SEARCH_AgencyOtherOffices` where HQ = HQ.Tag and `Deleted` <> 'yes' ) = 2, 2.1, IF((select COUNT(*) from `agencycentrallive`.`SEARCH_AgencyOtherOffices` where HQ = HQ.Tag and `Deleted` <> 'yes' ) = 3, 1.8, 1.5) ) ) * IF( MIN(if( OO.locationID <> 0 and OO.locationID is not null and (Level1 = '1925' or Level2 = '1925' or Level3 = '1925' or Level4 = '1925' ), 0, IF(OO.Longitude IS NOT NULL AND OO.Longitude <> 0 AND OO.Latitude IS NOT NULL AND OO.Latitude <> 0, (3963.1676 * ACOS( COS(RADIANS(90-OO.Latitude)) * COS(RADIANS(90- '51.5088214734' )) + SIN(RADIANS(90-OO.Latitude)) * SIN(RADIANS(90- '51.5088214734' )) * COS(RADIANS(OO.Longitude- '-0.014110233881' )) ) ), 1000) )) <= 10, 7, IF(MIN(if( OO.locationID <> 0 and OO.locationID is not null and (Level1 = '1925' or Level2 = '1925' or Level3 = '1925' or Level4 = '1925' ), 0, IF(OO.Longitude IS NOT NULL AND OO.Longitude <> 0 AND OO.Latitude IS NOT NULL AND OO.Latitude <> 0, (3963.1676 * ACOS( COS(RADIANS(90-OO.Latitude)) * COS(RADIANS(90- '51.5088214734' )) + SIN(RADIANS(90-OO.Latitude)) * SIN(RADIANS(90- '51.5088214734' )) * COS(RADIANS(OO.Longitude- '-0.014110233881' )) ) ), 1000) )) <= 30, 4, IF(MIN(if( OO.locationID <> 0 and OO.locationID is not null and (Level1 = '1925' or Level2 = '1925' or Level3 = '1925' or Level4 = '1925' ), 0, IF(OO.Longitude IS NOT NULL AND OO.Longitude <> 0 AND OO.Latitude IS NOT NULL AND OO.Latitude <> 0, (3963.1676 * ACOS( COS(RADIANS(90-OO.Latitude)) * COS(RADIANS(90- '51.5088214734' )) + SIN(RADIANS(90-OO.Latitude)) * SIN(RADIANS(90- '51.5088214734' )) * COS(RADIANS(OO.Longitude- '-0.014110233881' )) ) ), 1000) )) <= 100, 2, IF(MIN(if( OO.locationID <> 0 and OO.locationID is not null and (Level1 = '1925' or Level2 = '1925' or Level3 = '1925' or Level4 = '1925' ), 0, IF(OO.Longitude IS NOT NULL AND OO.Longitude <> 0 AND OO.Latitude IS NOT NULL AND OO.Latitude <> 0, (3963.1676 * ACOS( COS(RADIANS(90-OO.Latitude)) * COS(RADIANS(90- '51.5088214734' )) + SIN(RADIANS(90-OO.Latitude)) * SIN(RADIANS(90- '51.5088214734' )) * COS(RADIANS(OO.Longitude- '-0.014110233881' )) ) ), 1000) )) <= 250, 1.1, 0.65) ) ) ))) / (if (HQ.ExpectedSCP > 0,(coalesce((select sum(value) from `agencycentral`.`AGDATA_90Day_SCP` as `SCP` where SCP.agency_tag = HQ.Tag) / 3, 0) / POW(HQ.ExpectedSCP, '1.05' )),( '1.3635983899843' / coalesce((select `Multiplier` from `agencycentrallive`.`AgencyTrialMultiplier` where HQ.Tag = AgencyTag), 1)))) DESC, if (HQ.ExpectedSCP > 0,(coalesce((select sum(value) from `agencycentral`.`AGDATA_90Day_SCP` as `SCP` where SCP.agency_tag = HQ.Tag) / 3, 0) / POW(HQ.ExpectedSCP, '1.05' )),( '1.3635983899843' / coalesce((select `Multiplier` from `agencycentrallive`.`AgencyTrialMultiplier` where HQ.Tag = AgencyTag), 1))) asc;

Construction Recruitment Agencies in Poplar

Wrong Page?