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