One customer, many addresses. need only one.

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

One customer, many addresses. need only one.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources