Guest Post by David Churchward with 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:

 

  1. 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).
  2. 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:

 

  1. 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.
  2. 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

 

WHEN 2 THEN

 

WHEN 3 THEN 1

 

                                                                WHEN 4 THEN

 

WHEN 5 THEN

 

WHEN 6 THEN 2

 

                                                                WHEN 7 THEN

 

WHEN 8 THEN

 

WHEN 9 THEN 3

 

                                                                WHEN 10 THEN

 

WHEN 11 THEN

 

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!