MS SQL Server Time Dimension UDF

I use this function when writing reports.

Use:

SELECT
	time.Date
	, Count(tab.ID) AS [Count]
FROM
	dbo.TimeDimension('1/1/2007 00:00:00', '12/31/2007 23:59:59', 'month', 1) time
	LEFT OUTER JOIN
		TempTable tab
	ON tab.DateCreated BETWEEN time.Date AND time.DateEnd

Here is the function. I don’t know if there is a better way to do this, if there is can someone tell me. As far as I know I am the only person to post a function like this.

CREATE FUNCTION [dbo].[TimeDimension] (
	@DateStart datetime
	, @DateEnd datetime
	, @Type varchar(20)
	, @IncrementValue int
)
RETURNS @Dates TABLE ( Date datetime , DateEnd datetime )
AS
BEGIN
	DECLARE @DateCurrent datetime, @NextDate datetime
	SET @DateCurrent = @DateStart

	WHILE (@DateCurrent <= @DateEnd)
	BEGIN
		SET @NextDate = CASE (@Type)
			WHEN 'year' THEN DateAdd(year, @IncrementValue, @DateCurrent)
			WHEN 'quarter' THEN DateAdd(quarter, @IncrementValue, @DateCurrent)
			WHEN 'month' THEN DateAdd(month, @IncrementValue, @DateCurrent)
			WHEN 'dayofyear' THEN DateAdd(dayofyear, @IncrementValue, @DateCurrent)
			WHEN 'day' THEN DateAdd(day, @IncrementValue, @DateCurrent)
			WHEN 'week' THEN DateAdd(week, @IncrementValue, @DateCurrent)
			WHEN 'weekday' THEN DateAdd(weekday, @IncrementValue, @DateCurrent)
			WHEN 'hour' THEN DateAdd(hour, @IncrementValue, @DateCurrent)
			WHEN 'minute' THEN DateAdd(minute, @IncrementValue, @DateCurrent)
			WHEN 'second' THEN DateAdd(second, @IncrementValue, @DateCurrent)
			WHEN 'millisecond' THEN DateAdd(millisecond, @IncrementValue, @DateCurrent)
		END

		INSERT INTO @Dates ( Date , DateEnd )
		VALUES ( @DateCurrent , DateAdd(second, -1, @NextDate) )

		SET @DateCurrent = @NextDate
	END
	RETURN
END

• Tags

  cms code git sql wishlist

• Archives

  • September 2008
  • August 2008
  • July 2007
  • June 2007
  • February 2007

• Blogroll

  • GSL Solutions