incommon-exec-billing-contacts_sf.sql

/* Copyright © 2018 Internet2
This work is licensed under a https://creativecommons.org/licenses/by/4.0/ Creative Commons Attribution 4.0 International License. */

/* This SQL statement selects all Executive contacts, their name elements and email address from the salesforce contact affiliation table, and left joins it to any Billing contacts for the same account name */

SELECT * FROM (SELECT A.NAME AS E_ACCT_NAME, C.FIRST_NAME__C AS EXEC_FNAME, C.LAST_NAME__C AS EXEC_LNAME, C.CONTACT_EMAIL__C AS EXEC_MAIL
FROM SF_CONTACT_AFFILIATION__C C JOIN SF_ACCOUNT A ON C.ACCOUNT__C = A.ID
WHERE C.SERVICE__C LIKE '%InCommon%' AND C.STATUS__C = 'Current' AND C.ROLE__C = 'Executive Contact') AS EX
LEFT JOIN
(SELECT A.NAME AS B_ACCT_NAME, C.FIRST_NAME__C AS BLG_FNAME, C.LAST_NAME__C AS BLG_LNAME, C.CONTACT_EMAIL__C AS BLG_MAIL
FROM SF_CONTACT_AFFILIATION__C C JOIN SF_ACCOUNT A ON C.ACCOUNT__C = A.ID
WHERE C.SERVICE__C LIKE '%InCommon%' AND C.STATUS__C = 'Current' AND C.ROLE__C = 'Billing Contact') AS BI
ON EX.E_ACCT_NAME = BI.B_ACCT_NAME
ORDER BY E_ACCT_NAME