SELECT
C.FIRSTNAME,C.LASTNAME,
ADDRESS1 = CASE WHEN A.DESCRIPTION LIKE 'Shipping%' THEN A1.ADDRESS1 ELSE A2.ADDRESS1 END,
ADDRESS2 = CASE WHEN A.DESCRIPTION LIKE 'Shipping%' THEN A1.ADDRESS2 ELSE A2.ADDRESS2 END,
CITY = CASE WHEN A.DESCRIPTION LIKE 'Shipping%' THEN A1.CITY ELSE A2.CITY END,
STATE = CASE WHEN A.DESCRIPTION LIKE 'Shipping%' THEN A1.STATE ELSE A2.STATE END,
POSTALCODE = CASE WHEN A.DESCRIPTION LIKE 'Shipping%' THEN A1.POSTALCODE ELSE A2.POSTALCODE END,
COUNTRY = CASE WHEN A.DESCRIPTION LIKE 'Shipping%' THEN A1.COUNTRY ELSE A2.COUNTRY END,
	C.WORKPHONE,C.EMAIL,EH.ENROLLSTATUS
FROM SYSDBA.EVXENROLLHX EH
	INNER JOIN SYSDBA.EVXEVENT E ON EH.EVXEVENTID = E.EVXEVENTID
	INNER JOIN SYSDBA.EVXEVTICKET ET ON EH.EVXEVTICKETID = ET.EVXEVTICKETID
	INNER JOIN SYSDBA.CONTACT C ON ET.ATTENDEECONTACTID = C.CONTACTID
LEFT JOIN SYSDBA.ADDRESS A1 ON C.CONTACTID = A1.ENTITYID
LEFT JOIN SYSDBA.ADDRESS A2 ON C.ADDRESSID = A2.ADDRESSID
	CASE
	WHEN DESCRIPTION LIKE 'Shipping%' THEN INNER JOIN SYSDBA.ADDRESS A ON C.CONTACTID = A.ENTITYID
	ELSE INNER JOIN SYSDBA.ADDRESS A ON C.ADDRESSID = A.ADDRESSID
	END
WHERE E.DELIVERYMETHOD = 'Virtual'
	and EH.EVXEVENTID = '?'