SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 CTE query within another query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KnooKie
Aged Yak Warrior

United Kingdom
623 Posts

Posted - 05/09/2013 :  11:16:26  Show Profile  Visit KnooKie's Homepage  Reply with Quote
Hi All

Is it possible to include the following CTE query...

;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


within this one with a LEFT JOIN in the place specified below...

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



i seem to get incorrect syntax near ; when i try it so am wondering if it is possible to include my CTE query within the other one?

Any ideas appreciated.

====
Paul

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/09/2013 :  11:59:04  Show Profile  Reply with Quote



you can try something like this:
dont forget to include columns from CTE table in your final select statement...




;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 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,
	[INCLUDE CTE columns...]
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
LEFT JOIN 
	CTE									ON u.userid = CTE.userid/*******************************************************************	
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



Go to Top of Page

KnooKie
Aged Yak Warrior

United Kingdom
623 Posts

Posted - 05/09/2013 :  12:12:59  Show Profile  Visit KnooKie's Homepage  Reply with Quote
Perfect - Thank you

====
Paul
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000