First day of previous week with a twist

[nscheaffer]

Last week I worked through creating a Calendar universe.  It is based on a single table (Common.dbo.Calendar.dt) that has one row for each date going back about 8 years and forward about 65 years.  It has columns that break out all of the elements of a date.  That certainly could have been done within the universe, but this was table we have had around for a while.  It also has columns that define things that are specific to my company such as which days are holidays for us.

The database we are using is on Microsoft SQL Server 2008 R2 and we our BusinessObjects installation is 4.1 SP01 Patch 2.

I wanted to create variety of dimensions such has the first and last days of the previous year, quarter, month, etc.  There are a number of different ways to go about this, but I chose to follow the model shown here...

http://irfansworld.wordpress.com/2011/01/24/sql-server-dates-part-1-year/

I used "YEAR" instead of "YY" as a matter of personal preference and here are my dimensions related to year.

Dimension	Definition
First Day of Previous Year	DATEADD(YEAR, DATEDIFF(YEAR, 0, Common.dbo.Calendar.dt) - 1, 0)
Last Day of Previous Year	DATEADD(YEAR, DATEDIFF(YEAR, 0, Common.dbo.Calendar.dt), 0) - 1
First Day of Current Year	DATEADD(YEAR, DATEDIFF(YEAR, 0, Common.dbo.Calendar.dt), 0)
Last Day of Current Year	DATEADD(YEAR, DATEDIFF(YEAR, 0, Common.dbo.Calendar.dt) + 1, 0) - 1
First Day of Next Year	DATEADD(YEAR, DATEDIFF(YEAR, 0, Common.dbo.Calendar.dt) + 1, 0)
Last Day of Next Year	DATEADD(YEAR, DATEDIFF(YEAR, 0, Common.dbo.Calendar.dt) + 2, 0) - 1

The logic here is to find the difference in years between 01/01/1900 (equivalent to 0) and the date in my table and then add that number of years minus 1 back to 01/01/1900 to arrive at the First Day of the Previous Year.  Once I have that, the rest of the dimensions are just variations of it.

I proceeded to make the corresponding dimensions related to quarter and month simply by changing "YEAR" to "QUARTER" and "MONTH", respectively.  However, this approach did not work the way that I wanted it to when trying to create the same set of dimensions for WEEK.  First off, 01/01/1900 was a Monday so if I used this same definition updated for week as follows...

DATEADD(WEEK, DATEDIFF(WEEK, 0, Common.dbo.Calendar.dt) - 1, 0)

it would assume that I wanted my week to start on Monday.  I could adjust the definition to have my week start on Sunday, by using "-1" instead of "0" as the reference date...

DATEADD(WEEK, DATEDIFF(WEEK, -1, Common.dbo.Calendar.dt) - 1, -1)

As I worked through this two things became clear to me...

1. I needed to be able to dynamically specify what day would be my starting day of the week.
2. I needed my universe to be resilient enough that I could build and schedule a report pulling data from the previous week and be able to rerun that report with no changes even if it was after the day it was originally scheduled to run.

I realized that no matter what day I specified as the start of my week or what day of week my date from my table was I wanted to subtract at least 7 days from the date in the table.  Therefore, I just had to figure how many additional days to subtract for each combination.  To help me figure that out I created the following table...

We can see that x - y works for the first row.

DATEADD(DAY, (7 + (x - y)) * -1, Common.dbo.Calendar.dt)

The extra "7" in there represents the 7 days I want to subtract no matter what and I multiply it by -1 because I am subtracting (adding negative) days.  Unfortunately, this breaks down as we move to the second row.

If we look at the first column we can see that x - y + 7 works.  That's great, but I wanted just one calculation, not two.  As it turns out, if we use this second equation and apply the mod operator (%) with 7 as the divisor, (x - y + 7) % 7, we have it.

When I plug that into my DATEADD function it looks like this.

DATEADD(DAY, (7 + ((x - y + 7) % 7)) * -1, Common.dbo.Calendar.dt)

Again, the extra "7" at the beginning is for the 7 days I always want to subtract.

In order to streamline things a bit, first I made a Days of Week static LOV...

Then I created Week Start Day parameter that prompts based on Days of Week with "1" for Sunday as the default value...

Next, I defined a Week Start Day dimension as @Prompt(Week Start Day).

And at last, I can define my First Day of Previous Week dimension referencing the Week Start Day dimension like this...

     DATEADD(DAY, (7 + (DATEPART(dw, common.dbo.Calendar.dt) - @Select(Derived\Week\Week Start Day) + 7) % 7) * -1, Common.dbo.Calendar.dt)

Rounding our the rest of the week related dimensions involves merely adjusting the first "7" in the function above to these corresponding values...

Last Day of Previous Week: 1

First Day of Current Week: 0

Last Day of Current Week: -6

First Day of Next Week: -7

Last Day of Next Week: -13

As I worked through this process and documented how to use the universe for my users I couldn't help but think there should be an easier way.  Why didn't I just subtract 7 days for the First Day of Previous Week and 1 day for the Last Day of Previous Week (Yesterday) and then schedule my report on whatever day I wanted my week to start?  The main problem with that approach as it relates to my situation is that if a scheduled report would fail and did not get re-run the same day the query filter would have to change in order to run it as it would have run on its regularly scheduled day.  Not very resilient.  With this design I will have up until a weekly reports next scheduled run date to re-run it with no changes.  I like that.

I am sure this scenario has been addressed by many in many different ways.  So this is my contribution to the mix.  I hope this helps someone out down the road.

Noel