By Kasper de Jonge, crosspost from PowerPivotblog.nl
While building a new PowerPivot workbook i wanted to use Time Intelligent functions. One of the golden rules of PowerPivot time intelligent functions is to create a seperate related time table. I have created a stored procedure which creates a new table in PowerPivot. The important thing to do is when you want to call a stored procedure withing PowerPivot is set the “SET NOCOUNT OFF” at the beginning of your sproc. This will make sure your stored procedure will return only one dataset.
You can use this code to create the range:
/* Get a range of dates in the range @fromdate until @todate */ create procedure getDateRange @fromdate datetime, @todate datetime as begin SET NOCOUNT OFF /*Declare @todate datetime, @fromdate datetime select @fromdate = '2005-01-01' select @todate = '2008-12-31'*/ ;With DateSequence( [Date] ) as ( Select @fromdate as [Date] union all Select dateadd(day, 1, [Date]) from DateSequence where Date < @todate ) select YEAR([date]) as Year, Month([date]) as Month, DAY([DATE]) as Day, [date] as Date, CASE Month([date]) WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Okt' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' END as [MonthShort], DATENAME(MONTH,[Date]) as [MonthName], max(DATEPART(DAYOFYEAR,[Date])) as DayInYear, DATEPART( wk, [date]) as Weeknumber from DateSequence group by YEAR([date]),Month([date]),DATENAME(MONTH,[Date]), [date] option (MaxRecursion 10000) end GO
will give me:
Great tip got from Dave Wickert 🙂