Regardless of your RDBMS you’ll find more than a handful of scripts online to add a Date Dimension or Calendar Table to your schema. One problem I’ve seen is they lack an every-other-week column. So, let’s explore a common SQL Server script and how to add an Every Other Week flag to our date dimension.
This method is written for Microsoft’s SQL Server, but it is ANSI standard and will work with any date dimension which has an integer day-of-week column, which I’ve never seen one that doesn’t…
For posterity purposes, if you’d like to use the same Dim Date I’m using, then please download your script here at CodeProject.
The Code: Every Other Week Flag
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE VIEW DimDate as SELECT case when DayOfWeekUSA = 1 then case when row_number() over(partition by DayOfWeekUSA order by DateKey) % 2 = 1 then 1 else 0 end else null end EveryOtherSunday ,case when DayOfWeekUSA = 2 then case when row_number() over(partition by DayOfWeekUSA order by DateKey) % 2 = 1 then 1 else 0 end else null end EveryOtherMonday ,case when DayOfWeekUSA = 3 then case when row_number() over(partition by DayOfWeekUSA order by DateKey) % 2 = 1 then 1 else 0 end else null end EveryOtherTuesday ,case when DayOfWeekUSA = 4 then case when row_number() over(partition by DayOfWeekUSA order by DateKey) % 2 = 1 then 1 else 0 end else null end EveryOtherWednesday ,case when DayOfWeekUSA = 5 then case when row_number() over(partition by DayOfWeekUSA order by DateKey) % 2 = 1 then 1 else 0 end else null end EveryOtherThursday ,case when DayOfWeekUSA = 6 then case when row_number() over(partition by DayOfWeekUSA order by DateKey) % 2 = 1 then 1 else 0 end else null end EveryOtherFriday ,case when DayOfWeekUSA = 7 then case when row_number() over(partition by DayOfWeekUSA order by DateKey) % 2 = 1 then 1 else 0 end else null end EveryOtherSaturday ,d.* FROM DimDate d ; |
The above code block will create a view over the top of the Date Dimension and add 7 new columns for each day of the week. Every other week is flagged with a ‘1’ and the in-between weeks with a ‘0’.
This takes advantage of a window function (Row_Number) and the fact that every other week is an even/odd number. So by numbering each weekday then checking to see if that week is an even or an odd number we can assign a flag for that week; The case statement wrapping the window function accomplishes this. Also, as a plus, this view is very fast! If you’re still concerned with the speed you can instead add the columns to the table itself.
Do you have any fields that you add to your date dimension?