September 29, 2022 at 5:26 pm
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) ?
September 29, 2022 at 6:05 pm
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.
September 29, 2022 at 6:23 pm
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
September 29, 2022 at 8:59 pm
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)
September 30, 2022 at 8:39 am
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
September 30, 2022 at 1:51 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy