Guest Post by David Churchwardwith Technical Input from Scott Senkeresty
The first thing I ever do when I create a new PowerPivot workbook is create the date tables that I need. Until now, I’ve used a preconditioned SQL table to draw this information from. Now, thanks to Scott Senkeresty, you can now create your own – from PowerPivot!
Scott is a true genius. He spends a lot of time under the hood of Pivotstream operations working his magic and wizardry and, every now and then, he pops up for air to deliver a masterpiece like this.
It’s as Simple as Copy and Paste
If you read my last post SQL UNPIVOT Makes My Data Skinny, you’ll know where query editor is and the sort of tricks that it opens up within PowerPivot. In this post, there’s some SQL scripts that create time tables and you can simply cut and paste them into the query editor and use them straight away.
Having said this, it’s worth checking out Rob’s post The Ultimate Date Table to see why a date table is important and where you can get one of your own from Azure DataMarket. However, I think we’ve gone one step better now and given you a script that you can parameterise easily, condition relatively easily and setup extremely easily. Add to that the fact that it runs in mere seconds!
AND there’s one more bonus, especially for Accountants, you can set this up for YOUR Fiscal Year!
Copy, Paste, Save – Simples!
To get going, simply copy and paste the script from THIS ATTACHMENT into query editor. When you save it – bam – time table complete.
DECLARE @FirstDate DATE
DECLARE @LastDate DATE
SET @FirstDate ='1 Jan 2008'-- Enter first date of calendar as 'dd mmm yyyy'
SET @LastDate ='31 Dec 2015'-- Enter last date of calendar as 'dd mmm yyyy'
IF @FirstDate ISNULLOR @FirstDate =''
BEGIN
SET @FirstDate ='1 Jan 2000'
END
IF @LastDate ISNULLOR @LastDate =''
OR @LastDate < @FirstDate
ORDATEDIFF(DAY, @FirstDate, @LastDate)> 10000
BEGIN
SET @LastDate =DATEADD(DAY, 10000, @FirstDate)
END
;WITH CTE_DatesTable
AS
(
SELECT @FirstDate AS [Date]
UNIONALL
SELECTDATEADD(DAY, 1, [Date])
FROM CTE_DatesTable
WHEREDATEADD(DAY, 1, [Date])<= @LastDate
)
SELECT
Date,
DATEPART(DAYOFYEAR,Date) ASDayOfYear,
DATEPART(DAY,Date) ASDay,
DATEPART(MONTH,Date) ASMonth,
DATEPART(YEAR,Date) ASYear,
DATENAME(WEEKDAY,Date) ASDayOfWeek,
LEFT(DATENAME(WEEKDAY,Date),3) AS DayOfWeekAbr,
DATEPART(WEEK,Date) AS WeekOfYear,
DATEPART(QUARTER,Date) ASQuarter,
DATENAME(MONTH,Date) ASMonthName,
LEFT(DATENAME(MM,Date),3) AS MonthNameAbr,
DATEADD(DAY,-1,DATEADD(MONTH, 1,DATEADD(DAY,(DATEPART(DAY,
DATE)*-1)+1,Date))) AS MonthEndDate
FROM
CTE_DatesTable
OPTION (MAXRECURSION 10000)
Without doing anything to this script, you’ll get a time table that spans Jan 2008 to Dec 2015. That could be enough. In which case, you’re done. However, I’m sure you want more.
Change Date Parameters to Widen the Timeframe
The great news with this script is that it’s so easy to adjust to cover the timeframe that you need. You just need to follow these simple couple of steps:
- Towards the top of the script you’ll see “SET @FirstDate = “. You simply need to change the value to the first date in your dataset (or perhaps an earlier date to be sure).
- I’m sure you’ve already guessed step 2 – change “SET @LastDate = “ to a value that is beyond the last date in your dataset.
I almost wish I could make it more difficult but Scott’s nailed this so well that it’s almost impossible to get wrong – well almost!
A Quick Health Warning
This script uses a recursive CTE (Common Table Expression). If you’re not careful, it’s very possible to spin this baby into an infinite cycle. Without going into why this happens, here’s a couple of points to make sure that you don’t fall into that trap:
- The IF – BEGIN – SET – END statements are there to ensure that valid entries are made for @FirstDate and @LastDate. It’s probably best to leave these in.
- At the end of the script, there’s a command OPTION(MAXRECURSION 10000). This ensures that the script won’t loop more than 10,000 times (ie 10,000 days). If you set the recursion to zero, this removes all limits and then you could get into an infinite cycle.
Don’t be scared to give it a go. There’s controls here to stop anything going wrong and what’s the worst that could happen anyway!
The Next Level – Fiscal Elements
In all my time using SQL and PowerPivot, I’m yet to work in an environment where a year runs from 1st January to 31st December. As a result, we refer to a Fiscal Year to represent this alternate calendar. I took the liberty of hacking Scott’s script to add these elements in.
DECLARE @FirstDate DATE
DECLARE @LastDate DATE
DECLARE @FiscalStart INT
SET @FirstDate ='1 Jan 2008'-- Enter first date of calendar as 'dd mmm yyyy'
SET @LastDate ='31 Dec 2015'-- Enter last date of calendar as 'dd mmm yyyy'
SET @FiscalStart = 4 -- First calendar period of Fical Year - 1=Jan, 2=Feb...., 12=Dec
IF @FirstDate ISNULLOR @FirstDate =''
BEGIN
SET @FirstDate ='1 Jan 2000'
END
IF @LastDate ISNULLOR @LastDate =''
OR @LastDate < @FirstDate
ORDATEDIFF(DAY, @FirstDate, @LastDate)> 10000
BEGIN
SET @LastDate =DATEADD(DAY, 10000, @FirstDate)
END
;WITH CTE_DatesTable
AS
(
SELECT @FirstDate AS [Date]
UNIONALL
SELECTDATEADD(DAY, 1, [Date])
FROM CTE_DatesTable
WHEREDATEADD(DAY, 1, [Date])<= @LastDate
)
SELECT
Date,
DATEPART(DAYOFYEAR,Date) ASDayOfYear,
DATEPART(DAY,Date) ASDay,
DATEPART(MONTH,Date) ASMonth,
DATEPART(YEAR,Date) ASYear,
DATENAME(WEEKDAY,Date) ASDayOfWeek,
LEFT(DATENAME(WEEKDAY,Date),3) AS DayOfWeekAbr,
DATEPART(WEEK,Date) AS WeekOfYear,
DATEPART(QUARTER,Date) ASQuarter,
DATENAME(MONTH,Date) ASMonthName,
LEFT(DATENAME(MM,Date),3) AS MonthNameAbr,
DATEADD(DAY,-1,DATEADD(MONTH, 1,DATEADD(DAY,(DATEPART(DAY,
DATE)*-1)+1,Date))) AS MonthEndDate,
DATEADD(DAY,-1,DATEADD(MONTH, 1 + 12 -(DATEPART(MONTH,DATE)
- @FiscalStart + 1 +CASEWHEN @FiscalStart >DATEPART(MONTH,DATE)
THEN 12 ELSE 0 END),DATEADD(DAY,(DATEPART(DAY,DATE)*-1)+1,
Date))) AS YearEndDate,
DATEPART(MONTH,DATE)- @FiscalStart + 1 +
CASEWHEN @FiscalStart >DATEPART(MONTH,DATE)THEN 12
ELSE 0 END AS FiscalPeriod,
YEAR(DATEADD(DAY,-1,DATEADD(MONTH, 1 + 12 -(DATEPART(MONTH,
DATE)- @FiscalStart + 1 +CASEWHEN @FiscalStart >
DATEPART(MONTH,DATE)THEN 12 ELSE 0 END),DATEADD(DAY,
(DATEPART(DAY,DATE)*-1)+1,Date))))
AS FiscalYearEnd,
CASEDATEPART(MONTH,DATE)- @FiscalStart + 1 +CASEWHEN
@FiscalStart >DATEPART(MONTH,DATE)THEN 12 ELSE 0 END
WHEN 1 THEN 1
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 2
WHEN 5 THEN 2
WHEN 6 THEN 2
WHEN 7 THEN 3
WHEN 8 THEN 3
WHEN 9 THEN 3
WHEN 10 THEN 4
WHEN 11 THEN 4
WHEN 12 THEN 4
END AS FiscalQuarter
FROM
CTE_DatesTable
OPTION (MAXRECURSION 10000)
Again, you can simply copy and paste the script from THIS ATTACHMENT into query editor. However, you need to set a parameter to tell the script when your fiscal year starts. Towards the top of the script, you’ll see a command “SET @FiscalStart = “ and you need to enter a month number from 1 to 12 where 1 is January through to 12 being December. Therefore, if the first month of your financial year is April, set this value to 4. Otherwise, the script works the same as the previous example but returns some additional Fiscal Time elements.
And it’s as simple as that! Thanks Scott – truly brilliant!
Get in touch with a P3 team member