I cannot get this sql query to take my distances and put into categories in case statement -

I cannot get this sql query to take my distances and put into categories in case statement -

i have sql query taking latitude , longitude table , comparing set latitiude , longitude location. want able have case statement break downwards miles other columns.

*here taking latitude , longitude people , finding distance set point , making field called miles

select ( 3959 * acos ( cos ( radians(43.7779) ) * cos( radians( fc.address1_latitude ) ) * cos( radians( fc.address1_longitude ) - radians(-88.4215) ) + sin ( radians(43.7779) ) * sin( radians( fc.address1_latitude ) ) )) miles filteredcontact fc

**here problem is, trying take miles found , trying set categories in next case statements. example, 0-24 miles campus want set how many inquires, applicants, confirmed etc. want each set of distances.

select case when 'miles' < 25 '0-24' when 'miles' <= 25 , 'miles' <49 '25-49' when 'miles' >= 50 , 'miles' < 74 '50-74' when 'miles' >= 75 , 'miles' < 99 '75-99' when 'miles' >= 100 , 'miles' < 125 '100-125' when 'miles' >= 126 , 'miles' < 151 '126-151' when 'miles' >= 152 , 'miles' < 177 '152-177' when 'miles' >= 178 , 'miles' < 203 '178-203' when 'miles' >= 204 , 'miles' < 229 '204-229' when 'miles' >= 230 , 'miles' < 255 '230-255' when 'miles' >= 256 , 'miles' < 281 '256-281' when 'miles' >= 282 , 'miles' < 307 '282-307' when 'miles' >= 308 , 'miles' < 333 '308-333' when 'miles' >= 334 , 'miles' < 359 '334-359' when 'miles' >= 360 , 'miles' < 385 '360-385' when 'miles' >= 386 , 'miles' < 411 '386-411' when 'miles' >= 412 , 'miles' < 437 '412-437' when 'miles' >= 438 , 'miles' < 463 '438-463' when 'miles' >= 464 , 'miles' < 489 '464-489' when 'miles' >= 490 , 'miles' < 500 '490-500' else 'over 500' end

**here takes , puts miles above columns.

sum (case when fc.datatel_prospectstatusname in ('prospect','inquiry', 'applicant', 'admit', 'confirmed', 'enrolled', 'application started', 'application submitted', 'application completed', 'application moved erp') 1 else null end) "inq", sum (case when fc.datatel_prospectstatusname in ('applicant','admit', 'confirmed', 'enrolled', 'application submitted', 'application completed', 'application moved erp')then 1 else null end) "app", (select cast (sum(case when fc.datatel_prospectstatusname in ('applicant','admit', 'confirmed', 'enrolled', 'application submitted', 'application completed', 'application moved erp') 1 else null end)as float)/ (sum (case when fc.datatel_prospectstatusname in ('prospect','inquiry', 'applicant', 'admit', 'confirmed', 'enrolled', 'application started', 'application submitted', 'application completed', 'application moved erp') 1 else null end))) "app/inq", sum (case when fc.datatel_prospectstatusname in ('admit', 'confirmed', 'enrolled') 1 else null end) "adm", (select cast (sum(case when fc.datatel_prospectstatusname in ('admit', 'confirmed', 'enrolled') 1 else null end)as float)/ (sum (case when fc.datatel_prospectstatusname in ('applicant','admit', 'confirmed', 'enrolled', 'application submitted', 'application completed', 'application moved erp') 1 else null end))) "adm/app", sum (case when fc.datatel_prospectstatusname in ('confirmed', 'enrolled') 1 else null end) "dep", (select cast (sum(case when fc.datatel_prospectstatusname in ('confirmed', 'enrolled') 1 else null end)as float)/ (sum (case when fc.datatel_prospectstatusname in ('admit', 'confirmed', 'enrolled') 1 else null end))) "dep/adm", sum (case when fc.elucnsrv_currentprimaryappstatusname '%canceled%' 1 else null end) "can", (select cast (sum(case when fc.elucnsrv_currentprimaryappstatusname '%canceled%' 1 else null end)as float)/ (sum (case when fc.datatel_prospectstatusname in ('confirmed', 'enrolled') 1 else null end))) "can/dep" sum (case when fc.datatel_prospectstatusname ='enrolled' 1 else null end) "enr", (select cast (sum(case when fc.datatel_prospectstatusname ='enrolled' 1 else null end)as float)/ (sum (case when fc.datatel_prospectstatusname in ('admit', 'confirmed', 'enrolled') 1 else null end))) "enr/adm" filteredcontact fc fc.address1_latitude not null , fc.address1_longitude not null , fc.firstname not null , fc.lastname not null , fc.datatel_academiclevelofinterestidname in ('preprofessional' , 'undergraduate' , 'special') , (fc.statecodename = 'active' , fc.customertypecodename = 'prospective student') grouping fc.address1_latitude, fc.address1_longitude

not sure issue here, noticed few things:

in 2nd code block, have:

select case when 'miles' < 25 '0-24' when 'miles' **<=** 25 , 'miles' <49 '25-49' ...

and looks should

select case when 'miles' < 25 '0-24' when 'miles' **>=** 25 , 'miles' <49 '25-49' ...

in 2nd code block, compiler see , evaluate 'miles' string literal, rather pointer column (which appears intended behavior). cause conversion error

also, getting errors (i.e. not compiling), or info incorrect? if info incorrect, things how know wrong or how know info wrong help us.

one thing (it ain't pretty) this:

declare @milestable table (miles int) insert @milestable (miles) select ( 3959 * acos ( cos ( radians(43.7779) ) * cos( radians( fc.address1_latitude ) ) * cos( radians( fc.address1_longitude ) - radians(-88.4215) ) + sin ( radians(43.7779) ) * sin( radians( fc.address1_latitude ) ) )) filteredcontact fc select case when miles < 25 '0-24' when miles <= 25 , 'miles' <49 '25-49' when miles >= 50 , 'miles' < 74 '50-74' when miles >= 75 , 'miles' < 99 '75-99' when miles >= 100 , 'miles' < 125 '100-125' when miles >= 126 , 'miles' < 151 '126-151' when miles >= 152 , 'miles' < 177 '152-177' when miles >= 178 , 'miles' < 203 '178-203' when miles >= 204 , 'miles' < 229 '204-229' when miles >= 230 , 'miles' < 255 '230-255' when miles >= 256 , 'miles' < 281 '256-281' when miles >= 282 , 'miles' < 307 '282-307' when miles >= 308 , 'miles' < 333 '308-333' when miles >= 334 , 'miles' < 359 '334-359' when miles >= 360 , 'miles' < 385 '360-385' when miles >= 386 , 'miles' < 411 '386-411' when miles >= 412 , 'miles' < 437 '412-437' when miles >= 438 , 'miles' < 463 '438-463' when miles >= 464 , 'miles' < 489 '464-489' when miles >= 490 , 'miles' < 500 '490-500' else 'over 500' end @milestable

like said, it's not pretty alleviate conversion errors @ minimum....hope gets started in right direction!

sql