Creating CTEs

Creating a CTE is not a complex process. It is a simple query that defines the name of the CTE and then uses it:

;  --previous line must have a ; terminator
WITH
ISODates as (
SELECT distinct [Date]
      ,[ISO Week Number] as [ISOWeekNumber]
  FROM [Dimension].[Date])
SELECT Date, [ISOWeekNumber]
FROM ISODates

The query gets the distinct list of Date instances and ISO Week Number from Date Dimension in the WorldWideImportersDW database sample. The start of the query is the with keyword, which has a semi-colon terminating the line before it. Next is the name you will use to refer to the CTE in any future queries, in this case, ISODates. The query renames ISO Week Number to ISOWeekNumber. The final step is to write a query that selects from or uses the CTE table that was created. Refer to the following figure:

Figure 5.1 – A complete CTE query and the result set from the WorldWideImportersDW...