--================================================================================
--Author:	Craig Buchanan;craig.buchanan@cogniza.com
--Purpose:	Generates a table of data ranges (the most recent 12 months, plus
--		three of Crystal Reports named-date ranges: MonthToDate,LastFullMonth
--		and YearToDate
--Parameters:	None
--Returns:	a table of date range values
--================================================================================
DROP FUNCTION dbo.DateRanges
GO

CREATE FUNCTION dbo.DateRanges ()
RETURNS @DateRangeTable TABLE (
	[Sequence]	decimal,
	StartDate	smalldatetime,
	EndDate		smalldatetime,
	Value		varchar(25),
	[Description]	varchar(20)
	)

AS
BEGIN

	--grab the system's date/time (using a view)
	DECLARE @EndDate	Datetime
	SELECT	@EndDate = Now
	FROM	dbo.GetNow

	--calculate the first date of the current month
	DECLARE @FirstOfMonth	Datetime
	SET @FirstOfMonth = CONVERT(VARCHAR,DatePart(m,@EndDate)) + '/1/' + CONVERT(CHAR(4),DatePart(yyyy,@EndDate))

	--calculate the first day of the month, 12 months ago
	DECLARE @StartDate	Datetime
	SET @StartDate = DateAdd(m,-12,@FirstOfMonth)

	--create a sequence number generator
	DECLARE @i		int
	SET @i = 1

	--insert 12 months of data
	WHILE @StartDate < @FirstOfMonth
	BEGIN
		INSERT INTO @DateRangeTable (
			[Sequence],
			StartDate,
			EndDate,
			Value,
			[Description]
			)
		VALUES	(
			@i,
			@StartDate,
			DateAdd(m,1,@StartDate)-1,
			
			--create a value in the format i;StartDate:EndDate
			--ensure i is 0 padded
			Right( '0' + convert(varchar, @i), 2) + ';' + 
			CONVERT(CHAR(10),@StartDate,110) + ':' +
			CONVERT(CHAR(10),DateAdd(m,1,@StartDate)-1,110),

			DATENAME(mm, @StartDate) + ' ' + CONVERT(VARCHAR,DATEPART(yyyy, @StartDate))
			)

		--add a month; increment counter
		SET @StartDate = DateAdd(m,1,@StartDate)
		SET @i = @i + 1

	END

	--add crystal report data ranges in the format index;DateRange
	--'Year to Date'
	INSERT INTO @DateRangeTable ([Sequence],StartDate,EndDate,Value,[Description])
	VALUES	(
			@i,
			'1/1/' + CONVERT(CHAR(4),DatePart(yyyy, @EndDate)),
			CONVERT(CHAR(10),@EndDate,110),
			Right( '0' + convert(varchar, @i), 2) + ';YearToDate','Year To Date'
			)
	SET @i = @i + 1

	--'Last Full Month'
	INSERT INTO @DateRangeTable ([Sequence],StartDate,EndDate,Value,[Description])
	VALUES	(
			@i,
			CONVERT(CHAR(10),DateAdd(m,-1,@FirstOfMonth),110),
			CONVERT(CHAR(10),@FirstOfMonth-1,110),
			Right( '0' + convert(varchar, @i), 2) + ';LastFullMonth','Last Full Month'
			)
	SET @i = @i + 1

	--'Month to Date'
	INSERT INTO @DateRangeTable ([Sequence],StartDate,EndDate,Value,[Description])
	VALUES	(
			@i,
			CONVERT(VARCHAR,DatePart(m, @EndDate)) + '/1/' + CONVERT(CHAR(4),DatePart(yyyy, @EndDate)),
			CONVERT(CHAR(10),@EndDate,110),
			Right( '0' + convert(varchar, @i), 2) + ';MonthToDate','Month To Date'
			)
	SET @i = @i + 1

	RETURN

END
GO

GRANT SELECT ON dbo.DateRanges TO PUBLIC
GO