A few years ago, while on a SQL Server data warehousing project, my project manager sent me a request - create a table with all kinds of information based on a given date. You know – given a date, be able to figure out day of the year, day of the week, month, last business day of the week, and so on, making sure the table stores enough dates to keep it maintenance free. Oh, and one more thing, it’s going to be linked off of the already complex star schema that we were developing for. Why? For reporting purposes of course.

Thankfully, I was able to talk my way out of it by explaining how SQL Server has built-in date functions that can do the job on the fly and that if I were to make that table, I’d have to go through the trouble of writing a bunch of date-related code anyway. However, the original developer for the database that Terje Nilima Monsen works on, must not have been as lucky as I was.

While hunting down an elusive email filter rule in the database for an issue tracking system, Terje found the below table:

How many rows are in it? Well, let's just say that it goes from 1992-10-27 to 2022-10-24 for total of 10958 rows of this: 

FactDayLocal NextFactDayLocal YYYYMMDD Year Quarter Month Week Day DayOfWeek StartofYearDate EndofYearDate StartofQuarterDate
EndofQuarterDate StartofMonthDate EndofMonthDate StartofWeekDate EndofWeekDate DaySeqNo QuarterSeqNo MonthSeqNo WeekSeqNo YearSeqNo 
PriorDaySeqNo PriorWeekDaySeqNo PriorMonthDaySeqNo PriorQuarterDaySeqNo PriorYearDaySeqNo PriorWeekSeqNo PriorMonthWeekSeqNo PriorQuarterWeekSeqNo 
PriorYearWeekSeqNo PriorMonthSeqNo PriorQuarterMonthSeqNo PriorYearMonthSeqNo PriorQuarterSeqNo PriorYearQuarterSeqNo PriorYearSeqNo PrevPriorDaySeqNo 
PrevPriorWeekDaySeqNo PrevPriorMonthDaySeqNo PrevPriorQuarterDaySeqNo PrevPriorYearDaySeqNo PrevPriorWeekSeqNo PrevPriorMonthWeekSeqNo 
PrevPriorQuarterWeekSeqNo PrevPriorYearWeekSeqNo PrevPriorMonthSeqNo PrevPriorQuarterMonthSeqNo PrevPriorYearMonthSeqNo PrevPriorQuarterSeqNo 
PrevPriorYearQuarterSeqNo PrevPriorYearSeqNo

2012-11-05 00:00:00.000 2012-11-06 00:00:00.000 20121105 2012 4 11 45 5 2 2012-01-01 00:00:00.000 2012-12-31 00:00:00.000 2012-10-01 00:00:00.000 
2012-12-31 00:00:00.000 2012-11-01 00:00:00.000 2012-11-30 00:00:00.000 2012-11-04 00:00:00.000 2012-11-10 00:00:00.000 2012310 1039 3118 13557 2012 
2012309 2012303 2012279 2012218 2011309 13556 13552 13544 13504 3117 3115 3106 1038 1035 2011 2012308 2012296 2012249 2012126 2010309 
13555 13548 13530 13452 3116 3112 3094 1037 1031 2010

2012-11-06 00:00:00.000 2012-11-07 00:00:00.000 20121106 2012 4 11 45 6 3 2012-01-01 00:00:00.000 2012-12-31 00:00:00.000 2012-10-01 00:00:00.000 
2012-12-31 00:00:00.000 2012-11-01 00:00:00.000 2012-11-30 00:00:00.000 2012-11-04 00:00:00.000 2012-11-10 00:00:00.000 2012311 1039 3118 13557 2012 
2012310 2012304 2012280 2012219 2011310 13556 13552 13544 13505 3117 3115 3106 1038 1035 2011 2012309 2012297 2012250 2012127 2010310 
13555 13548 13531 13452 3116 3112 3094 1037 1031 2010

2012-11-07 00:00:00.000 2012-11-08 00:00:00.000 20121107 2012 4 11 45 7 4 2012-01-01 00:00:00.000 2012-12-31 00:00:00.000 2012-10-01 00:00:00.000 
2012-12-31 00:00:00.000 2012-11-01 00:00:00.000 2012-11-30 00:00:00.000 2012-11-04 00:00:00.000 2012-11-10 00:00:00.000 2012312 1039 3118 13557 2012 
2012311 2012305 2012281 2012220 2011311 13556 13553 13544 13505 3117 3115 3106 1038 1035 2011 2012310 2012298 2012251 2012128 2010311 
13555 13548 13531 13453 3116 3112 3094 1037 1031 2010

Terje explained that he's fears grepping the source for references to this table as the knowledge may reason may very well kill him, and I certainly don't blame him. Especially since there is also a table called "HourOfDay" which has two columns and 24 rows (guess the content) and "DayOfWeek" which has three columns and seven entries.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!