Need to get max date from two columns for every ID

Transact-SQL
1

SELECT MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID,COMM_TYPE_CODE,
COMM_LOCATION_CODE, FORMATTED_PHONE_ADDRESS,ADDDATE, LATEST_DATE = WHEN CASE MODDATE IS NULL THEN ADDDATE ELSE MAX(MODDATE) END
FROM #TEMP_PHONE WHERE COMM_LOCATION_CODE IN('HOME','HOME1')
GROUP BY MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID,COMM_TYPE_CODE,
COMM_LOCATION_CODE, FORMATTED_PHONE_ADDRESS,ADDDATE

--I am getting error. I need ta get best phone no. Their may be two or three phone numbers. Based on latest modate i need to get that row phone number.

2

This perhaps?

SELECT	MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID, COMM_TYPE_CODE, 
	COMM_LOCATION_CODE, FORMATTED_PHONE_ADDRESS, ADDDATE, 
	LATEST_DATE = COALESCE(MODDATE, ADDDATE)
FROM
(
	SELECT	ROW_NUMBER() OVER(PARTITION BY MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID
				ORDER BY MODDATE DESC) AS row_num,
		MASTER_CUSTOMER_ID, SUB_CUSTOMER_ID, COMM_TYPE_CODE, 
		COMM_LOCATION_CODE, FORMATTED_PHONE_ADDRESS, ADDDATE, 
		MODDATE
	FROM	#TEMP_PHONE 
	WHERE	COMM_LOCATION_CODE IN('HOME','HOME1')
) AS T
WHERE row_num = 1
ORDER BY ...
1 Like
3

Thanks a lot.