D J Horton Consulting Ltd

Personal reference to useful tips and tricks for SQL Server, SQL Server Reporting Services and Crystal Reports.



My alternative to scraps of paper lying about.



Others may find bits of it useful!








Saturday, 29 May 2010

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












No comments: