This is the third article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:

Introduction to Common Table Expression (a.k.a CTE)
Recursive CTE
Nested Common Table Expressions

Multiple CTEs in a Single Query

Many a times we come across a scenario, where we need to use multiple CTEs in a single query. For instance assume first CTE gives employee details and second CTE gives total salary paid to the employee year to date after performing all the aggregation of the salary paid from the beginning of the year to till date. So, here combining the first CTE with the Second CTE in a single query gives a meaning ful details of employee with his year to date salary.

Below is very basic example of using multiple CTEs in a single query:

WITH FirstCTE AS(SELECT 1 EmployeeId,'Shreeganesh Biradar' Name)
	, SecondCTE AS (SELECT 1 EmployeeId, '$ 100000' YTDSalary)
SELECT FC.EmployeeId, FC.Name, SC.YTDSalary
FROM FirstCTE FC
		INNER JOIN SecondCTE SC
		ON FC.EmployeeId = SC.EmployeeId

RESULT: