Auxiliary Numbers Table - Populated by CTE (Based on Itzik Ben-Gan)

USE [Auxiliary]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE  [dbo].[Nums]
([n] [int] NOT NULL, PRIMARY KEY CLUSTERED
 ([n]  ASC ))
GO
;WITH x00(n) AS (SELECT 1 UNION ALL SELECT 1),
x02 (n) AS (SELECT 1 FROM x00 a, x00 b),
x04 (n) AS (SELECT 1 FROM x02 a, x02 b),
x08 (n) AS (SELECT 1 FROM x04 a, x04 b),
x16 (n) AS (SELECT 1 FROM x08 a, x08 b),
x32 (n) AS (SELECT 1 FROM x16 a, x16 b),
cTally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY n) FROM x32)
 
INSERT INTO Nums(n)
SELECT * from cTally
WHERE n <= 1000000;
GO



 Based on the CTE used here:
http://sqlreportingservicescrystalreports.blogspot.co.uk/2009/12/auxilary-cte-of-numbers-sql.html

Create a CTE list of dates on the fly - SQL

CTE list of dates on the fly:

WITH D AS
(
SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29
)
SELECT DATEADD(dd, -D.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate
FROM D;


2000/pre CTE solution here

Create a list of dates on the fly - SQL

I've recently had the requirement to produce a list of the last 30 days on the fly (I didn't have the option to refer to a 'Numbers' table). This was to be used as a 'Command' in Crystal Reports and joined to a database table to produce 'missing dates'.

Using the system table, spt_values, the first stage was to produce a list of numbers:

SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29

This is then extended to produce a list of the last 30 dates.

SELECT DATEADD(dd, -D.rn, DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)) AS ReportDate
FROM (
SELECT number AS rn FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 29
) AS D

This was with help from both Chris Morris & Kevin McKelvey at http://www.sqlservercentral.com/

CTE solution here



 

Auxiliary Calendar table - SQL

This code is derived from a useful article by Todd Fifield at SQLServerCentral.com and has been modified to include columns based on the ISO8601 date and times standard (here and here).

IF OBJECT_ID('dbo.CalendarDay') IS NOT NULL
DROP TABLE dbo.CalendarDay;
GO

CREATE TABLE CalendarDay
( DayID INT IDENTITY(1, 1)
, DayDate SMALLDATETIME
, WeekNumber INT
, OrdinalDate VARCHAR(8)
, DateWeekNumber VARCHAR(10) 
, DayNumber INT
, NameOfDay VARCHAR(10) 
)
GO
SET NOCOUNT ON;
DECLARE
@Date SMALLDATETIME;
SET DATEFIRST 1; --assuming start of week is monday
SET @Date = '2010-01-01';
WHILE @Date < '2011-01-01'
BEGIN
INSERT INTO CalendarDay
( DayDate, WeekNumber, OrdinalDate, DateWeekNumber, DayNumber, NameOfDay )
SELECT @Date
, (DATEPART(WK, @Date))
, CAST(DATEPART(YEAR, @Date) as VARCHAR) + '-' + CAST(DATEPART(DY, @Date) as VARCHAR)
, CAST(DATEPART(YEAR, @Date) as VARCHAR) + '-'
+ 'W' + CAST(DATEPART(WK, @Date) as VARCHAR) + '-'
+ CAST(DATEPART(WEEKDAY, @Date) as VARCHAR)
, DATEPART(WEEKDAY, @Date)
, CASE DATEPART(WEEKDAY, @Date)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
WHEN 3 THEN 'Wednesday'
WHEN 4 THEN 'Thursday'
WHEN 5 THEN 'Friday'
WHEN 6 THEN 'Saturday'
ELSE 'Sunday' END;
SET @Date = DATEADD(day, 1, @Date);
END
GO

Auxiliary CTE of Months - SQL

WITH CTE_Months
AS
(
SELECT Mths ='January ' UNION ALL
SELECT Mths ='February ' UNION ALL
SELECT Mths ='March ' UNION ALL
SELECT Mths ='April ' UNION ALL
SELECT Mths ='May ' UNION ALL
SELECT Mths ='June ' UNION ALL
SELECT Mths ='July ' UNION ALL
SELECT Mths ='August ' UNION ALL
SELECT Mths ='September ' UNION ALL
SELECT Mths ='October ' UNION ALL
SELECT Mths ='November ' UNION ALL
SELECT Mths ='December '
)
SELECT Mths FROM CTE_Months

Auxiliary Months of Year Table - SQL

IF OBJECT_ID('dbo.CalendarMonth') IS NOT NULL
DROP TABLE dbo.CalendarMonth;
GO

CREATE TABLE dbo.CalendarMonth
(
mon nvarchar(10) NOT NULL
);

INSERT INTO dbo.CalendarMonth(mon)
SELECT 'January ' UNION ALL
SELECT 'February ' UNION ALL
SELECT 'March ' UNION ALL
SELECT 'April ' UNION ALL
SELECT 'May ' UNION ALL
SELECT 'June ' UNION ALL
SELECT 'July ' UNION ALL
SELECT 'August ' UNION ALL
SELECT 'September ' UNION ALL
SELECT 'October ' UNION ALL
SELECT 'November ' UNION ALL
SELECT 'December '

If, for example you are trying to provide an output of sales data for every month and the sale table doesn't have records for all 12 months, by creating an OUTER JOIN to the Calendar table, each month is returned.

IF OBJECT_ID('dbo.yearsales') IS NOT NULL
DROP TABLE dbo.yearsales;
GO
CREATE TABLE dbo.yearsales
(
sales int NOT NULL,saledate smalldatetime NOT NULL
)


INSERT INTO dbo.yearsales (sales, saledate)
SELECT '4','Jan 1 2009 12:00AM' UNION ALL
SELECT '5','Feb 1 2009 12:00AM' UNION ALL
SELECT '1','Apr 3 2009 12:00AM' UNION ALL
SELECT '11','May 5 2009 12:00AM' UNION ALL
SELECT '1','Jun 15 2009 12:00AM' UNION ALL
SELECT '4','Aug 5 2009 12:00AM' UNION ALL
SELECT '3','Oct 12 2009 12:00AM' UNION ALL
SELECT '17','Nov 2 2009 12:00AM' UNION ALL
SELECT '19','Dec 14 2009 12:00AM'

SELECT yearsales.sales, yearsales.saledate, DATENAME(month, yearsales.saledate) AS salesmonthname, CalendarMonth.mon
FROM yearsales RIGHT OUTER JOINCalendarMonth ON DATENAME(month, yearsales.saledate) = CalendarMonth.mon

Auxiliary CTE of numbers - SQL

Following on from the previous auxilary CTE of numbers, http://sqlreportingservicescrystalreports.blogspot.com/2009/12/create-common-table-expression-of.html
I realised after reading T-SQL Querying by Itzik Ben-Gan that he's created a much more efficient way of doing it:


DECLARE @n AS BIGINT;
SET @n = 100;
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;





taken from:
http://www.amazon.co.uk/Inside-Microsoft-SQL-Server-2005/dp/0735623139