We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Complex Join

mcsdguyian
mcsdguyian asked
on
Medium Priority
340 Views
Last Modified: 2008-02-01
I will give 1500 points because the difficulty and urgency of the code. in order to get the other 1000 points I will set up 2  other question one called thanks1 and thanks2 each worth 500 point and only award it to the ONE who figures it out*****************


I need to return a search of members and return there MemberID within a distance from a Particular zipcode.
I have a Member Table which has my members Info I can pass there zipcode in and return other members within there zipcode, but I also need to get a rowcount and show that distance other members are from the 90210 zipcode
***The following code does work to return Distance, City and Zipcode from the 90210 Zipcode, but not my member info or the record count***
***Below this code is my code to return member info from the radius of a zipcode, but not  the rowcount or the actual distance ***


**The code below returns All Cities,zipcode and the distance within 25.12 miles of the 90210 zipcode***
Zipcode            City               Distance
90210         Beverly hills              0.0
90209         Beverly Hills         1.368293
90076         Los Angeles         1.9350587
ETC...



DECLARE @ZIPCode nchar(5)
SET @ZIPCode = 90210

DECLARE @Miles REAL
SET @Miles = 25.12

DECLARE @RowCount int
SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @ZIPCode AND CityType = 'D'

if @RowCount > 0
      BEGIN
            SELECT
                  z.ZIPCode, z.City,
                  dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
                  /*
                        The above functions requires the Distance Assistant.
                  */
            FROM
                  ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r
            WHERE
                  z.Latitude <= r.MaxLat
                  AND z.Latitude >= r.MinLat
                  AND z.Longitude <= r.MaxLong
                  AND z.Longitude >= r.MinLong
                  AND CityType = 'D'
                  AND ZIPCodeType <> 'M'
                  AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles
                  /*
                        The above functions requires the Distance Assistant.
                        Also note that SQL Server caches the results so that this and the "SELECT RadiusAssistant"
                        functions are both only computed once.
                  */
            ORDER BY Distance
      END
ELSE
      SELECT -1 As ZIPCode
      --ZIP Code not found...

*** This is how my code is setup by adding the count(*) I can not return values and have not been able to make the Distance calculator work right because, I am not sure how to write the query****

***So if someone can get this to return below properly and add the distance code from above which is this code****
***THINGS THAT MUST WORK
***1) Return Distance
***2)Return MemberID
***3)Return Rowcount of the records returned
***IT will be a combination of both.

***Will Say thank you.
                                                z.ZIPCode, z.City,
                  dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
*****************************************************************************************
IF @RowCount > 0
            
      BEGIN
            SELECT  @COUNT = COUNT( *) , M.MemberID
            FROM Member  M
            WHERE PostalCode IN (
            (SELECT z.ZIPCode
                                    
            FROM       ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r
                              
            WHERE       z.Latitude <= r.MaxLat
                  AND z.Latitude >= r.MinLat
                  AND z.Longitude <= r.MaxLong
                  AND z.Longitude >= r.MinLong
                  AND CityType = 'D'
                  AND ZIPCodeType <> 'M'
                  AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles))
      
      IF @COUNT = 0
            /*Returns that there are no records for current search*/
            RETURN -1
      End

            
      
ELSE
Begin
/*  THIS WILL RETURN  ZIPCODE INVALID */
RETURN 2

***IF you need more information just post your question.******
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks mikkelp

I guess I didn't need an actual count so I was able get it to do exactly what I needed, I just wanted to send back a return parameter.  This is what I did

DECLARE @ZIPCode nchar(5)
SET @ZIPCode = 90210

DECLARE @Miles REAL
SET @Miles = 5

DECLARE @RowCount int
SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @ZIPCode AND CityType = 'D'

if @RowCount > 0

      BEGIN
      SELECT  @RowCount as Zipcodes_Counted
            SELECT M.MemberID , Z.Distance AS D,M.Username
                FROM Member  M,
                (SELECT z.ZIPCode, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) as Distance
                FROM ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r
                WHERE z.Latitude <= r.MaxLat
                    AND z.Latitude >= r.MinLat
                     AND z.Longitude <= r.MaxLong
                    AND z.Longitude >= r.MinLong
                    AND CityType = 'D'
                    AND ZIPCodeType <> 'M'
                    AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles)as Z
                WHERE M.PostalCode = Z.Zipcode
      Order by Z.Distance
            
IF @@ROWCOUNT = 0
      --NO RECORDS FOUND...
      RETURN -1      
END

ELSE
      RETURN 2
      
      --NO VALID ZIPCode found...
Look for 2 other posts  called thanks1 and thanks2 and I will award 500 for each of those to you.
Thanks again
mcsdguyian

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.