Combining two different CTE

[indianapolymath]

How do I combine these results? Union all was my first thought.

 

;WITH SUMMARY_CTE (SCHOOL_YEAR, YEAR_COUNT)
AS
(
	SELECT 
		SCHOOL_YEAR,
		COUNT(*)
	FROM DATAMART.dbo.LAS_LINKS_RESULT AS llr WITH (NOLOCK)
		JOIN STUDENT_SCHL ss ON ss.STN = llr.STN and ss.IsSchoolOfRecord = 1 and ss.STATUS = 'A'
		JOIN ILC_SCHL AS ilcs WITH (NOLOCK) ON ss.SCHL_ID = ilcs.SCHL_ID
	WHERE ilcs.CORP_ID = @pCORP_ID
		AND (ilcs.SCHL_ID = @pSCHL_ID OR @pSCHL_ID IS NULL)
		AND (llr.GRADE_CODE = @pGRADE OR @pGRADE IS NULL)
		AND (llr.SCHOOL_YEAR >= @pMIN_YEAR AND llr.SCHOOL_YEAR <= @pMAX_YEAR)
		AND llr.SPEAKING_PROFICIENCY_CODE BETWEEN '1' AND '5'
	GROUP BY SCHOOL_YEAR
)
SELECT 
	SCHOOL_YEAR																						AS SCHOOL_YEAR,
	SPEAKING_PROFICIENCY_CODE																		AS PROFICIENCY_CODE, 
	SPEAKING_PROFICIENCY																			AS PROFICIENCY, 
	COUNT(*)																						AS COUNT,
	(CONVERT(NUMERIC(5,2),COUNT(*))/ 
	(SELECT CONVERT(NUMERIC(5,2),SUMMARY_CTE.YEAR_COUNT) 
		FROM SUMMARY_CTE
		WHERE SUMMARY_CTE.SCHOOL_YEAR = llr.SCHOOL_YEAR
		) * 100)																					AS PERC,
	'Speaking'																						AS CATEGORY,
	1																								AS CAT_ORDER
FROM DATAMART.dbo.LAS_LINKS_RESULT AS llr WITH (NOLOCK)
	JOIN STUDENT_SCHL ss ON ss.STN = llr.STN and ss.IsSchoolOfRecord = 1 and ss.STATUS = 'A'
	JOIN ILC_SCHL AS ilcs WITH (NOLOCK) ON ss.SCHL_ID = ilcs.SCHL_ID
WHERE ilcs.CORP_ID = @pCORP_ID
	AND (ilcs.SCHL_ID = @pSCHL_ID OR @pSCHL_ID IS NULL)
	AND (llr.GRADE_CODE = @pGRADE OR @pGRADE IS NULL)
	AND (llr.SCHOOL_YEAR >= @pMIN_YEAR AND llr.SCHOOL_YEAR <= @pMAX_YEAR)
	AND llr.SPEAKING_PROFICIENCY_CODE BETWEEN '1' AND '5'
GROUP BY SCHOOL_YEAR, SPEAKING_PROFICIENCY_CODE, SPEAKING_PROFICIENCY
  
UNION ALL
  
;WITH SUMMARY_CTE (SCHOOL_YEAR, YEAR_COUNT)
AS
(
	SELECT 
		SCHOOL_YEAR,
		COUNT(*)
	FROM DATAMART.dbo.LAS_LINKS_RESULT AS llr WITH (NOLOCK)
		JOIN STUDENT_SCHL ss ON ss.STN = llr.STN and ss.IsSchoolOfRecord = 1 and ss.STATUS = 'A'
		JOIN ILC_SCHL AS ilcs WITH (NOLOCK) ON ss.SCHL_ID = ilcs.SCHL_ID
	WHERE ilcs.CORP_ID = @pCORP_ID
		AND (ilcs.SCHL_ID = @pSCHL_ID OR @pSCHL_ID IS NULL)
		AND (llr.GRADE_CODE = @pGRADE OR @pGRADE IS NULL)
		AND (llr.SCHOOL_YEAR >= @pMIN_YEAR AND llr.SCHOOL_YEAR <= @pMAX_YEAR)
		AND llr.LISTENING_PROFICIENCY_CODE BETWEEN '1' AND '5'
	GROUP BY SCHOOL_YEAR
)
SELECT 
	SCHOOL_YEAR																						AS SCHOOL_YEAR,
	LISTENING_PROFICIENCY_CODE																		AS PROFICIENCY_CODE, 
	LISTENING_PROFICIENCY																			AS PROFICIENCY, 
	COUNT(*)																						AS COUNT,
	(CONVERT(NUMERIC(5,2),COUNT(*))/ 
	(SELECT CONVERT(NUMERIC(5,2),SUMMARY_CTE.YEAR_COUNT) 
		FROM SUMMARY_CTE
		WHERE SUMMARY_CTE.SCHOOL_YEAR = llr.SCHOOL_YEAR
			) * 100)																				AS PERC,
	'Listening'																						AS CATEGORY,
	1																								AS CAT_ORDER
FROM DATAMART.dbo.LAS_LINKS_RESULT AS llr WITH (NOLOCK)
	JOIN STUDENT_SCHL ss ON ss.STN = llr.STN and ss.IsSchoolOfRecord = 1 and ss.STATUS = 'A'
	JOIN ILC_SCHL AS ilcs WITH (NOLOCK) ON ss.SCHL_ID = ilcs.SCHL_ID
WHERE ilcs.CORP_ID = @pCORP_ID
	AND (ilcs.SCHL_ID = @pSCHL_ID OR @pSCHL_ID IS NULL)
	AND (llr.GRADE_CODE = @pGRADE OR @pGRADE IS NULL)
	AND (llr.SCHOOL_YEAR >= @pMIN_YEAR AND llr.SCHOOL_YEAR <= @pMAX_YEAR)
	AND llr.LISTENING_PROFICIENCY_CODE BETWEEN '1' AND '5'
GROUP BY SCHOOL_YEAR, LISTENING_PROFICIENCY_CODE, LISTENING_PROFICIENCY

[indianapolymath]

To make it simple, I want to combine two separate CTEs:
;WITH myCTE (CustID, Co) AS
(
SELECT CustomerID, CompanyName FROM Customers
)
SELECT CompanyName FROM Customers WHERE CustomerID = 'ALFKI'

"combine with:" using a using state does not work...help!

;WITH myCTE (CustID, Co) AS
(
SELECT CustomerID, CompanyName FROM Customers
)
SELECT CompanyName FROM Customers WHERE CustomerID = 'BBICT'

[skhanal]

What error are you getting?. Also what do you mean by "combine with:" , I see you are using UNION ALL.