Database Development - Cartesian joins to generate dates

It seems every time I hear the phrase 'cartision join' it's mentioned in a negative way. Cartesian joins also called cross joins are a type of table join great for building things, you just need to understand what they are and how they work to make them useful. A Cartesian join occurs in an SQL statement when one or more tables are cross joined on the other tables. This also occurs when you use old SQL syntax like FROM t1,t2 without a WHERE t1.recid=t2.recid join filter to link the tables. Row duplication occurs when records are linked this way causing all the records in one table to be returned for each record in the other table. In this example we are intentionally using row duplication to build a set of dates and then using a function to filter out invalid dates like '06/31/2011'. Actually in this example I delete the dates I do not want but the functionality is the same. 

e.x. 

FROM  @Year y
      CROSS JOIN @Month m
      CROSS JOIN @Day d
WHERE (ISDATE( CAST(M.intMonth as varchar(2))
      + '/' + CAST(d.intDay as varchar(2))
      + '/' + CAST(Y.intYear as varchar(4)))) = 1

This provides the data available to filter further by date range. If you spend anytime designing procedures to populate reports you will need a user defined table to generate dates for you as shown below.

SELECT *
FROM [dbo].[udt_GenerateDates]('6/1/2011','6/14/2011')


GeneratedDate GeneratedYear GeneratedMonth GeneratedDay
----------------------- ------------- -------------- ------------
2011-06-01 00:00:00.000 2011 6 1
2011-06-02 00:00:00.000 2011 6 2
2011-06-03 00:00:00.000 2011 6 3
2011-06-04 00:00:00.000 2011 6 4
2011-06-05 00:00:00.000 2011 6 5
2011-06-06 00:00:00.000 2011 6 6
2011-06-07 00:00:00.000 2011 6 7
2011-06-08 00:00:00.000 2011 6 8
2011-06-09 00:00:00.000 2011 6 9
2011-06-10 00:00:00.000 2011 6 10
2011-06-11 00:00:00.000 2011 6 11
2011-06-12 00:00:00.000 2011 6 12
2011-06-13 00:00:00.000 2011 6 13
2011-06-14 00:00:00.000 2011 6 14

(14 row(s) affected)


Here is the user defined table function.

CREATE FUNCTION [dbo].[udt_GenerateDates](
@DateFrom datetime
, @DateTo datetime
)
RETURNS @TableVar table (
GeneratedDate DateTime
, GeneratedYear int
, GeneratedMonth int
, GeneratedDay int
)
AS
BEGIN
----------------------------------
-- Declare variables
----------------------------------
DECLARE @intYear integer
DECLARE @intYearTo integer
DECLARE @intDay integer
DECLARE @intMonth integer
DECLARE @Year table (intYear int)
DECLARE @Month table (intMonth int)
DECLARE @Day table (intDay int)
----------------------------------
-- Initialize Defaults
----------------------------------SET @intYear = DATEPART(YYYY, @DateFrom) - 1
SET @intYearTo = DATEPART(YYYY, @DateTo)


-- Build Years
WHILE @intYear < @intYearTo
BEGIN
   SET @intYear = @intYear + 1
   INSERT INTO @Year(intYear)
   VALUES (@intYear)
END

-- Build Months
SET @intMonth = 0
WHILE @intMonth < 12
BEGIN
   SET @intMonth = @intMonth + 1
   INSERT INTO @Month(intMonth)
   VALUES (@intMonth)
END

-- Build Days
SET @intDay = 0
WHILE @intDay < 31
BEGIN
   SET @intDay = @intDay + 1
   INSERT INTO @Day(intDay)
   VALUES(@intDay )
END

----------------------------------
-- Cross join the tables to produce
-- all possible date values.
----------------------------------
INSERT INTO @TableVar(
  GeneratedDate
, GeneratedYear
, GeneratedMonth
, GeneratedDay
)
SELECT
  GeneratedDate  = CAST(M.intMonth as varchar(2))
                   + '/' + CAST(d.intDay as varchar(2))
                   + '/' + CAST(Y.intYear as varchar(4))
, GeneratedYear  = Y.intYear
, GeneratedMonth = M.intMonth
, GeneratedDay   = d.intDay
FROM @Year y
     CROSS JOIN @Month m
     CROSS JOIN @Day d
WHERE ISDATE(         CAST(M.intMonth as varchar(2))
              + '/' + CAST(d.intDay as varchar(2))
              + '/' + CAST(Y.intYear as varchar(4))
             ) = 1

-- Delete unwanted dates
DELETE FROM @TableVar
WHERE GeneratedDate < CONVERT(varchar(10), @DateFrom, 101)
OR GeneratedDate > @DateTo


RETURN
END
GO