Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
elmorekevin
Starting Member
2 Posts |
 Posted - 2015-04-22 : 23:39:46
|
I'm new to mssql and databases in general, and I've been reading and experimenting all evening trying to solve this issue. It seems like a simple thing to ask the database to do, but HOW?!
Each customer could have many addresses, but I only want to return a single, prioritized address. They could have a service (6), home (1), or billing (3) address, or credit card (5). I would like to return only the service address first, but if the service address doesn't exist, then return the home, and if home address doesn't exist, then return the billing address.
I tried using Case, but I get more than one true statement per customer. Here's my code:
***********************
select c.id, c.name, c.active, a.type, a.addr1, a.latitude, a.longitude,
case
when a.type=6 then 'keep'
when a.type=1 then 'keep'
when a.type=3 then 'keep'
end as hello
from customer c, address a
where c.id=a.idnum and c.active='Y' and a.idtype='Customer' and a_type.id=a.type --and a.latitude is not null
order by c.id
************************
and some result:
id name active type addr1 latitude longitude hello
1000 john Y 3 1234 st. 39.0 -77.0 keep
1000 john Y 5 1234 st. 39.0 -77.0 NULL
1000 john Y 1 1324 st. 39.0 -77.0 keep
Thanks for any suggestions!
Kevin
VP for Operations |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-23 : 03:41:28
|
This perhaps?
SELECT c.id, c.name, c.active, a.type, a.addr1, a.latitude, a.longitude
FROM
(
select c.id, c.name, c.active, a.type, a.addr1, a.latitude, a.longitude,
ROW_NUMBER OVER (
PARTITION BY c.id
ORDER BY c.id, CASE
when a.type=6 then 1
when a.type=1 then 2
when a.type=3 then 3
when a.type=5 then 4
ELSE 999
END,
-- Ensure that the ORDER BY is repeatable for any customer with duplicate a.type records
a.PrimaryKeyFields
AS [T_RowNumber]
from customer AS c
JOIN address AS a
ON a.idnum = c.id
where c.active='Y' and a.idtype='Customer'
) AS X
WHERE [T_RowNumber] = 1 -- Only the first address for each customer
order by c.id
Note that you should use JOIN rather than
FROM Table1 AS T1, Table2 AS T2
WHERE T1.SomeID = T2.SomeID
|
 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2015-04-27 : 13:47:23
|
OUTER APPLY was designed to handle this type of thing:
select c.id, c.name, c.active, a.type, a.addr1, a.latitude, a.longitude
from customer c
outer apply (
select top (1) idnum, type, addr1, latitude, longitude
from address
where
idnum = c.id and
idtype='Customer' --and latitude is not null
order by
case type
when '6' then 1
when '1' then 2
when '3' then 3
when '5' then 4
else 5 end
) as a
where c.active='Y'
order by c.id
|
|
|
|
elmorekevin
Starting Member
2 Posts |
Posted - 2015-05-17 : 03:29:24
|
Thank you Kristen and Scott! I was able to get this to work for some slight formatting modifications (and I just learned how to insert code in this forum):
SELECT X.id, name, active, type, addr1, latitude as lat, longitude as lon, access_point as ap,
private_ip, cpe_snr_min, ap_ip, manufacturer, ap_lat, ap_lon
FROM
(
select c.id, c.name, c.active, a.type, a.addr1, a.latitude, a.longitude, nit.access_point,
nit.private_ip, nit.cpe_snr_min, radios.ip as ap_ip, radios.manufacturer, sites.lat as ap_lat,
sites.lon as ap_lon,
ROW_NUMBER() OVER (
PARTITION BY c.id
ORDER BY c.id, CASE
when a.type=6 then 1
when a.type=1 then 2
when a.type=3 then 3
when a.type=5 then 4
when a.type=2 then 5
when a.type=4 then 6
ELSE 999
END
) as row
from customer AS c
inner join address AS a
ON a.idnum = c.id
join networkinfo_tab nit
on c.id=nit.d_custid
left join network_radios radios
on nit.access_point=radios.radio_name
left join network_sites sites
on radios.location_code=sites.code
where c.active='Y' and a.idtype='Customer'
) AS X
WHERE row = 1 -- Only the first address for each customer |
|
|
|
|
|
|
|
|
One customer, many addresses. need only one.