Using Query Editor to Create a Time Table

 

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!

8 Responses to Using Query Editor to Create a Time Table

  1. Johannes says:

    Just to be sure I understand this correctly:
    1. Create a connection to any table in any database, so I can open the query editor
    2. Paste the script in the query editor

    Tried it this way and ran into several errors (could not find type “DATE” among other things) but I will try again tomorrow.
    Besides those errors (will be easy to fix once I’ve slept a couple of hours) I was wondering if there is no other possibility to open a query editor to generate data other than creating a connection just to bring it up.

  2. Johannes says:

    Oh.. I think I just realized what I kept missing: “FROM CTE_DatesTable” and other FROMs.
    You created a connection to the Azure table.. alright back to work.

  3. Marco Russo says:

    I have a post in my blog with a SQL query that is CTE-based and doesn’t suffer the recursion limit issue. It is also very fast.

    http://sqlblog.com/blogs/marco_russo/archive/2007/11/28/generate-date-dimension-that-support-working-days-calculation.aspx

  4. Lee Hawthorn says:

    Excellent post. On my machine this ran in around 3 seconds. It will be interesting to see the performance when the model is loaded to SharePoint.

    Lee

  5. Bas Kroes says:

    Great query! Got some questions from dutch users who don’t have their language options right. Explaining adding a SET LANGAUGE statement did the trick.

  6. I remember reading about this back when it was published and mentally filed it away until I needed it. Today was that day, and it worked like a charm!

  7. Alexandre says:

    I get the error “SQL Instruction invalid, ‘delete’, ‘insert’, ‘procedure’, ‘select’ or ‘update’ is expected.

    What should I do? I’m completely new to sql. Thanks

  8. gregkramer1 says:

    Probably beginner error here but what do you do with Alexandre’s error “SQL Instruction invalid, ‘delete’, ‘insert’, ‘procedure’, ‘select’ or ‘update’ is expected? Thanks.

Leave a Comment or Question