After futzing with it I got it. Heres the answer for those intrested...
declare @lat1 float
declare @long1 float
select @lat1 = latitude from zipnov99 where zipcode = '54901'
select @long1 = longitude from zipnov99 where zipcode = '54901'
select dis.distancecat, count(*)
from ( select s.studentid, case when d.distance < 50 then '<50'
when d.distance between 51 and 100 then '50 - 100'
when d.distance between 101 and 200 then '100 - 200'
when d.distance between 201 and 500 then '200 - 500'
else '>500' end as distancecat
from students s inner join (select s.studentid, dbo.globalDistance(@lat1, @long1, z.latitude, z.longitude) as distance
from students s inner join zipnov99 z on left(s.hzip,5)=z.zipcode
where periodid=4 ) d on s.studentid = d.studentid ) dis
group by dis.distancecat>I have this function that calculates the distace between two points on the globe. I want to do a count on how many there are within 50 miles, 50-100, 100-200, etc. The is what I got but I cant seem to get it to acutally count.
>
>
>
>
>declare @lat1 float
>declare @long1 float
>
>select @lat1 = latitude from zipnov99 where zipcode = '54901'
>select @long1 = longitude from zipnov99 where zipcode = '54901'
>
>select case when d.distance < 50 then '<50'
> when d.distance between 51 and 100 then '50 - 100'
> when d.distance between 101 and 200 then '100 - 200'
> when d.distance between 201 and 500 then '200 - 500'
> else '>500' end as catagory
>from students s inner join (
>select s.studentid, dbo.globalDistance(@lat1, @long1, z.latitude, z.longitude) as distance
>from students s inner join zipnov99 z on left(s.hzip,5)=z.zipcode
>where periodid=4 ) d on s.studentid = d.studentid
>
>
>This works, but now I want to do a count of each group. It wount let me group by 'catagory' because its not a real column I think. Any Ideas?
>
>
>Eric Stephani
