|
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
*/
|