Common Table Expressions

Producing Dates – Sometimes a list of dates is required for you to cross join against, I used to do this via a large table filled with dates which used to work well. Since the introduction of SQL Server 2005 however I now use a common table expression (CTE) to create a list of dates, the advantage here is speed (no table needs to be scanned) as well as storage space (no data is stored down). The following code will generate a list of dates between 01/01/2010 and 31/12/2010

WITH myDates AS
(
 SELECT CAST('01/Jan/2010' AS DATETIME) DateValue
 UNION ALL
 SELECT DateValue + 1
 FROM myDates
 WHERE DateValue + 1 <= N'31/Dec/2010'
)
-- Immediately use the above CTE table, the table will no longer exist after -- it’s first use
SELECT DateValue FROM myDates OPTION (MAXRECURSION 0)

Please note the OPTION (MAXRECURSION 0) statement, when using CTEs the default maximum number of recursions is 100, here we set it to 0 to stipulate that the loop must continue until it naturally completes. The key principle of the CTE is its ability to self reference allowing you to perform tasks on the data that is already present in the table without complex join scenarios. Also the ability to use recursion as shown above allows you to programmatically create large tables without the use of stored procedures. The only downside to CTEs appears to be the fact that the table itself only exists for the first statement use outside of the WITH statement. Anywho back to our statement, you’ll probably want this as part of another SQL Statement let’s say a list of dates in September and a list of sales against those dates? First of let’s create a function that will allow us to get the dates we need and using the code above.

CREATE FUNCTION DateList
( 
   @StartDate AS DATETIME, @EndDate as DATETIME
)
RETURNS @tabDates TABLE
(
   DateValue DATETIME
)
AS
BEGIN
   WITH myDates AS
   (
      SELECT @StartDate DateValue
      UNION ALL
         SELECT DateValue + 1
            FROM myDates
            WHERE DateValue + 1 <= @EndDate
   )
   INSERT INTO @tabDates SELECT DateValue FROM myDates OPTION (MAXRECURSION 0)
   RETURN
END

Now with the above function we can link this to anytable and retrieve the results so we’ll get a full list of dates for September and then link the total sales per date

DECLARE @Sales TABLE (ID INTEGER, [Date] DATETIME)
INSERT INTO @Sales VALUES (1, N'02/Sep/2010')
INSERT INTO @Sales VALUES (2, N'12/Sep/2010')
INSERT INTO @Sales VALUES (3, N'19/Sep/2010')
INSERT INTO @Sales VALUES (4, N'02/Sep/2010')
INSERT INTO @Sales VALUES (5, N'04/Sep/2010')
INSERT INTO @Sales VALUES (6, N'01/Sep/2010')
INSERT INTO @Sales VALUES (7, N'02/Sep/2010')



SELECT d.DateValue, COUNT(s.ID) 
FROM DateList(N'01/Sep/2010', N'30/Sep/2010') d
LEFT OUTER JOIN @Sales AS s ON d.DateValue = s.[Date]
GROUP BY d.DateValue

This is just a brief overview of CTEs and i'll create a more in depth example later on.