;WITH CTE (userid, UserCreatedDate, SubCreatedDate, ExpiryDate) AS
(
	SELECT DISTINCT
		u.userid,
		fus.created_on				AS UserCreatedDate,
		fs.created_on				AS SubCreatedDate,
		fs.expires_on				AS ExpiryDate
		--'DSB'						AS DataSource
	FROM
		dbo.tbl_Users AS u
	LEFT JOIN
		cds.f_user_sub AS fus				ON u.userID = fus.userID
	LEFT JOIN
		cds.f_sub AS fs						ON fs.f_sub_id = fus.f_sub_id
	LEFT JOIN
		cds.f_pub AS fp						ON fp.f_pub_id = fs.f_pub_id
	LEFT JOIN
		cds.d_circ_status cs				ON cs.d_circ_status_id = fs.d_circ_status_id
	WHERE
		fp.pub_code = '00000501'		-- NEW DSB ENDS Report code
	UNION
	SELECT DISTINCT
		u.userid,
		fus.created_on				AS UserCreatedDate,
		fs.created_on				AS SubCreatedDate,
		fs.expires_on				AS ExpiryDate
		--'CDS'						AS DataSource
	FROM
		dbo.tbl_Users AS u
	LEFT JOIN
		cds.f_user_sub AS fus				ON u.userID = fus.userID
	LEFT JOIN
		cds.f_sub AS fs						ON fs.f_sub_id = fus.f_sub_id
	LEFT JOIN
		cds.f_pub AS fp						ON fp.f_pub_id = fs.f_pub_id
	LEFT JOIN
		cds.d_circ_status cs				ON cs.d_circ_status_id = fs.d_circ_status_id
	WHERE
		fp.pub_code = '2EN'		-- OLD CDS ENDS Report code
)
SELECT
	userid, 
	MIN(UserCreatedDate)	AS MinUserCreatedDate, 
	MIN(SubCreatedDate)		AS MinSubCreatedDate, 
	MAX(ExpiryDate)			AS MaxExpiryDate
FROM 
	CTE
GROUP BY
	userid
ORDER BY
	userid

SELECT DISTINCT
	u.userid,
	upper(u.userTitle)			AS Title,
	upper(u.userFirstname)		AS Firstname,
	upper(u.userLastname)		AS Lastname,
	lower(u.userEmail)			AS Email,
	u.usercompany				AS Company,
	fs.customer_number,
	u.userOptInThirdPartyEmails,
	u.userOptInHBPLEmails,
	u.is_email_verified,							--1 = Yes, 0 = No
	u.email_verified_on,
	fus.prereqs_complete		AS Is_Registered,	--1 = Yes, 0 = No
	cs.circ_status_code,
	cs.circ_status_description
FROM
	dbo.tbl_Users AS u
LEFT JOIN
	cds.f_user_sub AS fus				ON u.userID = fus.userID
LEFT JOIN
	cds.f_sub AS fs						ON fs.f_sub_id = fus.f_sub_id
LEFT JOIN
	cds.f_pub AS fp						ON fp.f_pub_id = fs.f_pub_id
LEFT JOIN
	cds.d_circ_status cs				ON cs.d_circ_status_id = fs.d_circ_status_id
/*******************************************************************	
I would like to include the CTE query as a left join in here
joining on u.userid (if possible)
*******************************************************************/
WHERE
	fp.pub_code = '00000501'	-- NEW DSB ENDS Report code

ORDER BY
	cs.circ_status_code