CTEs are a great thing to use in your query. They are an excellent way to consolidate code and make it readable as well as create a “view” inside of your code. One great thing I have used them for many times was to pick out the max date associated with a set of key values. So let’s say there is a table that has a column called ID, Quantity and Date. And in this table there are duplicate IDs with different quantities and dates associated with them. So ID 4 could be in the table twice with a quantity of 4 and date of 1/1/2010 and also a quantity of 8 and a date of 2/8/2010. We will then create a CTE with all the valid key and date values.
SELECT ID, MAX(Date) FROM Table
Next we can select all of our data from the table and limit it to one the records that exist in that CTE by using WHERE Exists on the condition of the ID and Date matching an entry in the CTE.
The question becomes what happens if you want to use multiple CTEs in a query. I have wanted to do this before but had to find ways around it. There has to be one of several operations that follows a CTE in the query; a select statement, update, insert, delete or merge. (at least I think those are the only three off the top of my head) If I wanted to use more than one CTE I would have to declare one, use a select and then declare a second. What if I want to use 2 CTEs in the same query? Luckily there is a way to do this, and unfortunately I didn’t find this out until I finished that section of my project. Needless to say this would have been much better and made my life a lot easier! The basic syntax will be your normal CTE declaration, followed by a comma, followed by declaring the next CTE but leaving off the WITH. Was that confusing enough without showing you how to do it? Here is an example:
WITH Departments AS (Select * from HumanResources.Department),
EmployeeDepartments AS (Select * fromHumanResources.EmployeeDepartmentHistory)
SELECT *
FROM Departments d
INNER JOIN EmployeeDepartments ed ON d.DepartmentID = ed.DepartmentID
Notice where the first CTE ends and a comma is added then the second CTE name is given followed by the query for it. Normally each of those, if not used in the same query, would be written like this:
First CTE: WITH Departments AS (Select * from HumanResources.Department)
Other CTE: WITH EmployeeDepartments AS (Select * fromHumanResources.EmployeeDepartmentHistory)
Just remember the following:
WITH CTEName AS (Query),
SecondCTEName AS (SecondQuery)
The rest of your query here
