SQL Leap Year Time Dimension (Parallel Period)

Hi Guys,

Seen as the internet is so Unhelpful when it comes to working with leap years in SQL Server.

I’ve written a script that will allow you to do parallel period comparisons down to the day. So if today is Thursday and you want to compare your data to last year Thursday e.g. Sales. Then you will need to create a script that can’t just take you back a year, and add or remove a few days. You need to take into consideration that in every leap year there’s 53 weeks and an extra day. So just to save you all the hassle here’s the script. If you don’t know how to check for leap years, you can get the logic from the script too.

You guys might want to think about adding this to your Date Dimensions in the future if you’re creating Sales data marts.

/******************Parallel Peroid Comparison to day****************************/

/********This is if you want to compare today to last year what today is********/

-- Replace FullDate with your own Current date time in your Time Dimension; Format = GETDATE()

-- Replace DayOfYear with your own Current day of year in your Time Dimension; Format = DATENAME(DAYOFYEAR, FullDate)

SELECT *,CASE WHEN (YEAR(FullDate) % 4 <> 0) THEN 0 ELSE 1 END IsLeapYear

, CASE WHEN ((YEAR(FullDate) % 4 = 0) AND [DayOfYear] > 59) THEN DATENAME(WEEKDAY,DATEADD(dd, +2,DATEADD(yy,-1,FullDate)))

WHEN ((YEAR(FullDate) % 4 = 1) AND [DayOfYear] < 60) THEN DATENAME(WEEKDAY,DATEADD(dd, +2,DATEADD(yy,-1,FullDate)))

WHEN ((YEAR(FullDate) % 4 <> 0) AND [DayOfYear] > 59) THEN DATENAME(WEEKDAY,DATEADD(dd, 1,DATEADD(yy,-1,FullDate)))

WHEN ((YEAR(FullDate) % 4 <> 0) AND [DayOfYear] < 60) THEN DATENAME(WEEKDAY,DATEADD(dd, 1,DATEADD(yy,-1,FullDate))) ELSE DATENAME(WEEKDAY,DATEADD(dd, +1,DATEADD(yy,-1,FullDate))) END LeapYearDay

FROM DimDate

What irritates me is how simple it would be to do this in MDX.

ParallelPeriod( [ Level_Expression [ ,Index [ , Member_Expression ] ] ] )

SELECT ParallelPeriod ([Date].[Calendar].[Calendar Quarter]

   , 3

   , [Date].[Calendar].[Month].[October 2003])

   ON 0

   FROM [Adventure Works]