Every Other Week Flag for Date Dimension or Calendar Table

Data set of Every Other Week Flag using a Date Dimension
Data set of Every Other Week Flag using a Date Dimension

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

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?