Get addresses that match list of customers

  • Let's say i have a table with columns:  cus_no, address, city, state, zip.

    How would i get a list of addresses that exist for a list of customers, i.e. cus_no IN (cus1, cus2, cus3) ?

  • You've practically written simple list already:

    SELECT cus_no, address, city, state, zip
    FROM table 
    WHERE cus_no IN (cus1, cus2, cus3)

    Since you're not on SQL 2017+, STRING_AGG() function  isn't available if you want a single row for each cus_no with a delimited list of addresses (addresses means what? Literally only the address column? Or all four columns that comprise a full address?).

    If this is your requirement, you'll need to use one of the legacy tricks for aggregating such data such as stuff() ... for xml path. See https://www.mssqltips.com/sqlservertip/5542/using-for-xml-path-and-stringagg-to-denormalize-sql-server-data/ for an example that concatenates multiple columns.

  • This is what I've come up with so far.  Can someone grade me on this, or say if you think there's a better way?

    with ad as (
    select ad.addr_1, ad.addr_2, ad.addr_3, ad.city, ad.state, ad.zip, ad.country
    , dense_rank() over (partition by ad.addr_1, ad.addr_2, ad.addr_3, ad.city, ad.state, ad.zip, ad.country order by cus_no) +
    dense_rank() over (partition by ad.addr_1, ad.addr_2, ad.addr_3, ad.city, ad.state, ad.zip, ad.country order by cus_no desc) - 1 add_cus_cnt
    , DENSE_RANK() over (partition by (select 1) order by cus_no) +
    DENSE_RANK() over (partition by (select 1) order by cus_no desc) - 1 cus_cnt
    from araltadr_wv ad
    where cus_no IN ('cus#1', 'cus#2', 'cus#3')
    )
    select distinct ad.addr_1, ad.addr_2, ad.addr_3, ad.city, ad.state, ad.zip, ad.country
    from ad
    where ad.add_cus_cnt = ad.cus_cnt
  • Load your list of customer no's you want to look up into a table and do something like WHERE cus_no IN (SELECT  cus_no FROM cus_no_stg)

  • Jackie Lowery wrote:

    This is what I've come up with so far.  Can someone grade me on this, or say if you think there's a better way?

    with ad as (
    select ad.addr_1, ad.addr_2, ad.addr_3, ad.city, ad.state, ad.zip, ad.country
    , dense_rank() over (partition by ad.addr_1, ad.addr_2, ad.addr_3, ad.city, ad.state, ad.zip, ad.country order by cus_no) +
    dense_rank() over (partition by ad.addr_1, ad.addr_2, ad.addr_3, ad.city, ad.state, ad.zip, ad.country order by cus_no desc) - 1 add_cus_cnt
    , DENSE_RANK() over (partition by (select 1) order by cus_no) +
    DENSE_RANK() over (partition by (select 1) order by cus_no desc) - 1 cus_cnt
    from araltadr_wv ad
    where cus_no IN ('cus#1', 'cus#2', 'cus#3')
    )
    select distinct ad.addr_1, ad.addr_2, ad.addr_3, ad.city, ad.state, ad.zip, ad.country
    from ad
    where ad.add_cus_cnt = ad.cus_cnt

    Can you describe in words what the values of add_cus_cnt and cus_cnt should represent?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • add_cus_cnt = distinct count of customers per address.

    cus_cnt = distinct count of customers

    The idea is that getting the addresses that have a distinct count of customers equal to the distinct count of customers in the where clause will give me the addresses that only exist for all the customers in the where clause.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply