SQL Solution Needed

[tc2]

I have 2 tables: CUST and ADDR. First I’ll describe the data and then the query needed.

CUST TABLE
Has PK CUST_ID and FK/composite unique key (CUST_NUM, CONTACT_NUM). A record exists on CUST for each organization (ABC Company) as well as a record for each contact within an organization (Jon Doe “contact at” ABC Company), with each having a unique CUST_ID. Organizations will have a CONTACT_NUM = 0 and may be assigned to one PARENT, which is another record on CUST. PARENT organization records cannot have a PARENT assigned to them and cannot have any associated contacts. A customer contact record cannot have a PARENT.

CUST_ID CUST_NUM CONTACT_NUM PARENT
111 123 0 88888888 org record
222 123 1 0 contact record
333 123 2 0 contact record
444 321 0 44444444 org record
555 123 4 0 contact record
666 567 0 88888888 org record
777 567 1 0 contact record
888 123 6 0 contact record
44444444 0 0 0 parent record
88888888 0 0 0 parent record

ADDR TABLE
Has PK ADDR_ID and a FK/composite NON-unique key (CUST_ID, ADDR_NUM, USE_CD, PREF_CD). A record on ADDR does not represent a unique address, but represents the intersection between customer, address, address use (mailing/billing/shipping), and preferred address status (preferred/not preferred). A customer can only have one preferred address for each address use, but can have multiple non-preferred addresses per USE_CD.

ADDR_ID CUST_ID ADDR_NUM USE_CD PREF_CD
1 222 1 4 1
2 222 2 2 1
3 222 3 4 2
4 333 2 2 2
5 333 9 4 2
6 555 1 3 2
7 777 2 4 1
8 777 3 3 2
9 888 1 4 2
10 888 1 4 2


QUERY
I want to select all customer contact records whose organization is assigned to parent 88888888 (excluding org records) . For each customer contact, I want only 1 address, which I’ll define as “the best” address. To identify the best address, analyze USE_CD and PREF_CD and select the first record in the following prioritized list of conditions:
1) USE_CD = 4 and PREF_CD = 1 (MAILING PREFERRED) one or zero per cust
2) USE_CD = 2 and PREF_CD = 1 (BILLING PREFERRED) one or zero per cust
3) USE_CD = 4 and PREF_CD = 2 (MAILING SECONDARY) multiples possible per cust
4) Any other address record. multiples possible per cust

If a record doesn’t exist for a customer contact that meets condition 1 or 2 above, there may be multiple addresses that meet condition 3, so randomly pull the first record meeting condition 3, and use the same logic for condition for 4 if no records are found that meet condition 3.

Result Set for above data should look as follows:
CUST_ID CUST_NUM CONTACT_NUM ADDR_NUM
222 123 1 1
333 123 2 9
555 123 4 1
777 567 1 2
888 123 6 1

Here is the SQL that I currently have (but the correlated sub-select doesn’t allow the order by clause or the fetch first row clause). I can’t do a min(addr_rank) with a group by, because the addr_num is what is needed and is not unique with cust_id and addr_rank. Any suggestions for correcting this sql or writing something else that works is greatly appreciated.
SELECT B.CUST_ID
,B.CUST_NUM
,B.CONTACT_NUM
,C.ADDR_NUM
FROM CUSTOMER A
,CUSTOMER B
,ADDR C
WHERE A.PARENT IN (88888888)
AND A.CUST_NUM = B.CUST_NUM
AND B.CONTACT_NUM <> 0
AND C.ADDR_NUM IN
(SELECT E.ADDR_NUM
FROM (SELECT F.CUST_ID, F.ADDR_NUM
,CASE WHEN F.USE_CD = 4 AND F.PREF_CD = 1
THEN 1
WHEN F.USE_CD = 2 AND F.PREF_CD = 1
THEN 2
WHEN F.USE_CD = 4 AND F.PREF_CD = 2
THEN 3
ELSE 4
END AS ADDR_RANK
FROM ADDR F
WHERE F.STAT_CD = 1 (active status)
AND F.CUST_ID = B.CUST_ID) E
ORDER BY E.ADDR_RANK
FETCH FIRST ROW ONLY)
FOR FETCH ONLY WITH UR;

[Snib]

TC2,

I set the table up in MS Access to try out the SQL so you will need to convert the nested IIF statments into CASE statement but beyond that the SQL is as per the column names you supplied.

The SQL will not cater for the possibility off there being more than 2 addresses for one contact with the same catergory as there is nothing on your tables to distinguish between the rows. It would return multiple rows in this situation.

What you could do is added a timestamp_inserted field to the address table. This could then be used as a further MAX statement on this value within the MAX_ADDRESS sub-table to ensure that you only selected the row with the MAX timestamp_inserted within the ADDRESS_RANK. You would then add a futher predicate to compare the timestamp_inserted on the ADDR_DETAILS sub-table agains the MAX(timestamp_inserted) returned from the MAX_ADDRESS sub-table.

Anyway, this is the SQL I came up with, hope this helps:

select addr_details.cust_num,
addr_details.cust_id,
addr_details.addr_rank,
addr_details.addr_id
from
(SELECT CONTACT.CUST_NUM,
CONTACT.CUST_ID,
iif(USE_CD = 4 AND PREF_CD = 1,1,
IIF(USE_CD = 2 AND PREF_CD = 1, 2,
iif(USE_CD = 4 AND PREF_CD = 2,3,4))) as ADDR_RANK,
ADDRESS.ADDR_ID
FROM CUSTOMER as CUST,
CUSTOMER as CONTACT,
ADDRESS
where CUST.PARENT = 88888888
and CUST.CONTACT_NUM = 0
and CUST.CUST_NUM = CONTACT.CUST_NUM
and CONTACT.CONTACT_NUM > 0
and ADDRESS.CUST_ID = CONTACT.CUST_ID) as ADDR_DETAILS,
(SELECT CONTACT.CUST_NUM,
CONTACT.CUST_ID,
max(iif(USE_CD = 4 AND PREF_CD = 1,1,
IIF(USE_CD = 2 AND PREF_CD = 1, 2,
iif(USE_CD = 4 AND PREF_CD = 2,3,4)))) as ADDR_RANK
FROM CUSTOMER as CUST,
CUSTOMER as CONTACT,
ADDRESS
where CUST.PARENT = 88888888
and CUST.CONTACT_NUM = 0
and CUST.CUST_NUM = CONTACT.CUST_NUM
and CONTACT.CONTACT_NUM > 0
and ADDRESS.CUST_ID = CONTACT.CUST_ID
group by contact.cust_num, contact.cust_id) as MAX_ADDRESS

where addr_details.cust_num = max_address.cust_num
and addr_details.cust_id = max_address.cust_id
and addr_details.addr_rank = max_address.addr_rank

order by 1,2,3
;


Regards

Snib