I have a few tables that I need to join in a specific way to get the required results.
-
ACCOUNTS
This is the first table, I need to check every row in here to find the data I need, the relevant fields shown below. The main columns are ACCTNO and PRI_CUSTOMER_NO, this is the lead customer on the account.
ACCTNO PRI_CUSTOMER_NO CUST_CLASS 100186 610234 SME -
CUSTOMERROLE
This also uses ACCTNO as above and CUSTOMER_NO, this can the the same as the PRI_CUSTOMER_NO above but there can also be other customers on the same account. Using ACCTNO can return all customers for that account.
ACCTNO CUSTOMER_NO 100186 610234 100186 61665 100452 678812 -
CUSTOMER
This table is as below:
CUSTOMER_NO LASTNAME PRIVATE_PHONE PRIVATE_MOBILE WORK_PHONE EMAILSEARCH 610234 Smith NULL NULL NULL [email protected] 61665 NULL 846313 NULL NULL NULL -
CONSUMER
This table as below:
ACCTNO STATUS_TYPE 100186 30 100199 40 -
LOCADDRESS
For this table ITEM_ID is the key and it is the CUSTOMER_ID
| ITEM_ID | STREET | POSTCODE |
|---|---|---|
| 61665 | High St | SE12RX |
For each ACCTNO I need returning, ACCTNO, STATUS_TYPE, CUST_CLASS, LASTNAME, STREET, POSTCODE, PHONE, EMAIL.
I want a result for each ACCTNO so if the PRI_CUSTOMER_NO doesn’t have any data for the relevant fields, try looking for data for one of the other customers related to that ACCTNO, if there is no match then return NULL. If there is some data for one customer but some is missing, you can return data for any customer on that account, so mixing and matching customer numbers is acceptable I just need as many of the fields returning data as possible, as few nulls as possible.
I only need one phone number returning and I don’t mind which so if any of PRIVATE_PHONE|PRIVATE_MOBILE|WORK_PHONE has a match just return whichever as PHONE.
So for the above example the output would be as below.
| ACCTNO | STATUS_TYPE | CUST_CLASS | LASTNAME | STREET | POSTCODE | PHONE | |
|---|---|---|---|---|---|---|---|
| 100186 | 30 | SME | Smith | High St | SE12RX | 846313 | [email protected] |
Let me know if you need more example data, thanks.
3
Something like this perhaps:
SELECT Accounts.ACCTNO
, consumer.STATUS_TYPE
, Accounts.CUST_CLASS
, ISNULL(MAX(CASE WHEN customer.CUSTOMER_NO = Accounts.PRI_CUSTOMER_NO THEN customer.LASTNAME END), MAX(customer.LASTNAME))
, ISNULL(MAX(CASE WHEN customer.CUSTOMER_NO = Accounts.PRI_CUSTOMER_NO THEN LocalAddress.STREET END), MAX(LocalAddress.STREET))
, ISNULL(MAX(CASE WHEN customer.CUSTOMER_NO = Accounts.PRI_CUSTOMER_NO THEN LocalAddress.POSTCODE END), MAX(LocalAddress.POSTCODE))
, ISNULL(MAX(CASE WHEN customer.CUSTOMER_NO = Accounts.PRI_CUSTOMER_NO THEN COALESCE(customer.PRIVATE_PHONE,customer.PRIVATE_MOBILE,customer.WORK_PHONE) END), MAX(COALESCE(customer.PRIVATE_PHONE,customer.PRIVATE_MOBILE,customer.WORK_PHONE)))
, ISNULL(MAX(CASE WHEN customer.CUSTOMER_NO = Accounts.PRI_CUSTOMER_NO THEN EMAILSEARCH END), MAX(customer.EMAILSEARCH))
FROM (
VALUES (100186, 610234, N'SME')
) Accounts (ACCTNO,PRI_CUSTOMER_NO,CUST_CLASS)
INNER JOIN (
VALUES (100186, 610234)
, (100186, 61665)
, (100452, 678812)
) customerrole (ACCTNO,CUSTOMER_NO)
ON customerrole.ACCTNO = Accounts.ACCTNO
INNER JOIN (
VALUES (610234, N'Smith', NULL, NULL, NULL, N'[email protected]')
, (61665, NULL, 846313, NULL, NULL, NULL)
) customer (CUSTOMER_NO,LASTNAME,PRIVATE_PHONE,PRIVATE_MOBILE,WORK_PHONE,EMAILSEARCH)
ON customer.CUSTOMER_NO = customerrole.CUSTOMER_NO
INNER JOIN (
VALUES (100186, 30)
, (100199, 40)
) consumer (ACCTNO,STATUS_TYPE)
ON consumer.ACCTNO = Accounts.ACCTNO
LEFT JOIN (
VALUES (61665, N'High St', N'SE12RX')
) LocalAddress (ITEM_ID,STREET,POSTCODE)
ON LocalAddress.ITEM_ID = customer.CUSTOMER_NO
GROUP BY Accounts.ACCTNO
, consumer.STATUS_TYPE
, Accounts.CUST_CLASS
I make the join on AcctNo to get all the customers per account, and then by using aggregation, take first and foremost the main customer’s data, and then just the MAXIMUM, where non-nulls will come first.
1