Execute the following T-SQL example script in Query Editor to demonstrate the architecture of an SQL query with CROSS JOIN.

The SQL query with the SELF CROSS JOIN of a CTE produces a sequence of 31 numbers which are used for running out the dates into the future starting with today:

-- Generate sequence 0-99 with cross join of CTE - SQL sequence

-- SQL cross join - SQL date sequence

USE AdventureWorks

GO

-- Create a cte to give the sequence of digits (0-9)

WITH cteSequence

     AS (SELECT 0 SeqNo

         UNION

         SELECT 1

         UNION

         SELECT 2

         UNION

         SELECT 3

         UNION

         SELECT 4

         UNION

         SELECT 5

         UNION

         SELECT 6

         UNION

         SELECT 7

         UNION

         SELECT 8

         UNION

         SELECT 9)

-- Main query

-- SQL dateadd datetime function

SELECT DayInMonth = Dateadd(d,10 * b.SeqNo + a.SeqNo,Convert(VARCHAR,Getdate(),111))

-- SQL self cross join

FROM   cteSequence a

       CROSS JOIN cteSequence b

WHERE  b.SeqNo < 4

       AND 10 * b.SeqNo + a.SeqNo < 31

GO

/* Partial results

DayInMonth

2015-01-31 00:00:00.000

2015-02-01 00:00:00.000

2015-02-02 00:00:00.000

2015-02-03 00:00:00.000

2015-02-04 00:00:00.000

2015-02-05 00:00:00.000

2015-02-06 00:00:00.000

2015-02-07 00:00:00.000

*/