Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    10

    Unanswered: query by name and find zip code close by

    hello - here is my puzzle.

    I have a table with clients and prospect in it called tbl_contacts

    I have a created separate query - qry_prospects_retired (prospects within a given age range.)

    I would like to be able to query the tbl_contacts on a @name parameter and find propsects in the qry_prospects_retired that match the first 4 characters of the zip code.

    any thoughts are appreciated.

  2. #2
    Join Date
    Mar 2004
    Posts
    10
    Here is what I have so far.. this is for a perfect match on zip - first 4 of the zip would be ideal.

    Cheers!

    SELECT DISTINCT
    TOP (100) PERCENT qry_prospects_retired_63_to_68.Contact AS PROSPECT, wce_contact.Contact AS CLIENT, wce_contact.Zip,
    qry_prospects_retired_63_to_68.Zip AS PZip, wce_contact.IDStatus, wce_contact.LastName, qry_prospects_retired_63_to_68.Phone,
    qry_prospects_retired_63_to_68.Address1, qry_prospects_retired_63_to_68.City, qry_prospects_retired_63_to_68.State,
    qry_prospects_retired_63_to_68.Cell, qry_prospects_retired_63_to_68.CLBD AS [Prospect BD], qry_prospects_retired_63_to_68.RepLastName,
    wce_contact.CLBD

    FROM wce_contact INNER JOIN
    qry_prospects_retired_63_to_68 ON wce_contact.Zip LIKE qry_prospects_retired_63_to_68.Zip

    WHERE (wce_contact.IDStatus LIKE N'%client%') AND (NOT (wce_contact.IDStatus = N'1 Pending Full Client')) AND
    (NOT (qry_prospects_retired_63_to_68.Zip IS NULL)) AND (NOT (wce_contact.Zip IS NULL)) AND (wce_contact.Contact = @select_client)

    ORDER BY wce_contact.LastName

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    well, this will collect zip codes that match by 4 digits, anyway.
    Code:
    declare @test table (zip varchar(12))
    
    insert into @test 
    select '01234' union all
    select '01243' union all
    select '01235' union all
    select '01236' union all
    select '11234' union all
    select 'a01-b65'
    
    declare @target_zip varchar(12)
    set @target_zip = '01231'
    
    select *
    from @test
    where zip like left(@target_zip, 4) + '%'
    Even has a chance of using an index. I am not sure you should use this as a measure of closeness, between two zip codes, though. Actual areas may depend on population density.

  4. #4
    Join Date
    Mar 2004
    Posts
    10
    great - that helps. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •