Skip to content

Instantly share code, notes, and snippets.

@royashbrook
Created June 30, 2014 20:07
Show Gist options
  • Save royashbrook/d6bd1635afdd44ea8984 to your computer and use it in GitHub Desktop.
Save royashbrook/d6bd1635afdd44ea8984 to your computer and use it in GitHub Desktop.
Generate Dimension Table in C#
//uses LINQPad
void Main()
{
List<dimDate> dimDates = genDimDate(
DateTime.Parse("1/1/2012")
, DateTime.Parse("12/31/2017")
);
dimDates.Dump();
}
//returns a list of the dimDate objects
List<dimDate> genDimDate(DateTime StartDate, DateTime EndDate){
int TotalDays = (int)(EndDate.AddDays(1) - StartDate).TotalDays;
return Enumerable.Range(0,TotalDays).Select (e =>
new dimDate() {date=StartDate.AddDays(e)}
).OrderBy (e => e.DateID ).ToList();
}
// dimDate based on my current localization preferences
// can be augmented to fit other localizations, this is just the template.
// sources:
// WeekOfYear - http://msdn.microsoft.com/en-us/library/system.globalization.calendar.getweekofyear.aspx
// QuarterOfYear - //http://stackoverflow.com/questions/8698303/how-do-i-discover-the-quarter-of-a-given-date-in-c-net-3-5-or-4-0
// IsWeekend - //http://stackoverflow.com/questions/12781751/determine-if-begindate-is-weekend
// IsLeapYear - //http://msdn.microsoft.com/en-us/library/system.datetime.isleapyear.aspx
// IsHoliday - //http://stackoverflow.com/questions/18326446/how-to-calculate-holidays-for-the-usa
// note: this should be reworked before any use in a production environment.
// takes ~0.2s to run on my machine and i only need to run it once.
public class dimDate{
//using a composite int based on date as an ID for default
public int DateID { get { return int.Parse(this.date.ToString("yyyyMMdd")); } }
public string DateString { get { return this.date.ToString("MM/dd/yyyy"); } }
public int Month { get { return this.date.Month; } }
public int Day { get { return this.date.Day; } }
public int Year { get { return this.date.Year; } }
public int DayofWeek { get { return (int) this.date.DayOfWeek; } }
public string DayofWeekName { get { return this.date.DayOfWeek.ToString(); } }
public int DayofYear { get { return this.date.DayOfYear; } }
public int WeekOfYear { get { return this.getWeekOfYear(); } }
public string MonthName { get { return this.date.ToString("MMMM"); } }
public int QuarterOfYear { get { return this.getQuarterOfYear(); } }
public bool IsWeekend { get { return this.getIsWeekend(); } }
public bool IsLeapYear { get { return DateTime.IsLeapYear(this.date.Year); } }
public bool IsUSHoliday { get { return this.getIsUSHoliday(); } }
public bool IsSpecialDay { get { return this.getIsSpecialDay(); } }
public DateTime date { //note this uses midnight time
get { return this._date.Date; }
set { _date = value; }
}
private DateTime _date;
private bool getIsSpecialDay(){ //sample function to call out individual identified days
List<DateTime> sd = new List<DateTime>();
sd.Add(DateTime.Parse("1/20/2014")); //random day
sd.Add(DateTime.Parse("5/5/2015")); //random day
sd.Add(DateTime.Parse("10/28/2013")); //random day
return sd.Count (s => s == this.date ) > 0;
}
private bool getIsUSHoliday(){
return getHolidayList(this.date.Year).Count (r => r.Date == this.date) > 0 ;
}
private bool getIsWeekend(){
return (this.date.DayOfWeek == DayOfWeek.Saturday) || (this.date.DayOfWeek == DayOfWeek.Sunday);
}
private int getQuarterOfYear()
{
return (int)Math.Floor(((decimal)this.date.Month + 2) / 3);
}
private int getWeekOfYear(){
System.Globalization.DateTimeFormatInfo dfi = System.Globalization.DateTimeFormatInfo.CurrentInfo;
System.Globalization.Calendar cal = dfi.Calendar;
return cal.GetWeekOfYear(this.date,dfi.CalendarWeekRule,dfi.FirstDayOfWeek);
}
//http://stackoverflow.com/questions/18326446/how-to-calculate-holidays-for-the-usa
// slightly modified for my readability
// begin...
private class Holiday
{
public string HolidayName { get; set; }
public DateTime Date { get; set; }
public Holiday(string holidayName, DateTime date)
{
HolidayName = holidayName;
Date = date;
}
}
// generate holiday list for a given year
// http://www.usa.gov/citizens/holidays.shtml
// http://archive.opm.gov/operating_status_schedules/fedhol/2013.asp
private static List<Holiday> getHolidayList(int vYear)
{
int FirstWeek = 1;
int SecondWeek = 2;
int ThirdWeek = 3;
int FourthWeek = 4;
int LastWeek = 5;
List<Holiday> HolidayList = new List<Holiday>();
// New Year's Day Jan 1
HolidayList.Add(new Holiday("NewYears", new DateTime(vYear, 1, 1)));
// Martin Luther King, Jr. third Mon in Jan
HolidayList.Add(new Holiday("MLK", GetNthDayOfNthWeek(new DateTime(vYear, 1, 1), DayOfWeek.Monday, ThirdWeek)));
// Washington's Birthday third Mon in Feb
HolidayList.Add(new Holiday("WashingtonsBDay", GetNthDayOfNthWeek(new DateTime(vYear, 2, 1), DayOfWeek.Monday, ThirdWeek)));
// Memorial Day last Mon in May
HolidayList.Add(new Holiday("MemorialDay", GetNthDayOfNthWeek(new DateTime(vYear, 5, 1), DayOfWeek.Monday, LastWeek)));
// Independence Day July 4
HolidayList.Add(new Holiday("IndependenceDay", new DateTime(vYear, 7, 4)));
// Labor Day first Mon in Sept
HolidayList.Add(new Holiday("LaborDay", GetNthDayOfNthWeek(new DateTime(vYear, 9, 1), DayOfWeek.Monday, FirstWeek)));
// Columbus Day second Mon in Oct
HolidayList.Add(new Holiday("Columbus", GetNthDayOfNthWeek(new DateTime(vYear, 10, 1), DayOfWeek.Monday, SecondWeek)));
// Veterans Day Nov 11
HolidayList.Add(new Holiday("Veterans", new DateTime(vYear, 11, 11)));
// Thanksgiving Day fourth Thur in Nov
HolidayList.Add(new Holiday("Thanksgiving", GetNthDayOfNthWeek(new DateTime(vYear, 11, 1), DayOfWeek.Thursday, FourthWeek)));
// Christmas Day Dec 25
HolidayList.Add(new Holiday("Christmas", new DateTime(vYear, 12, 25)));
//saturday holidays are moved to Fri; Sun to Mon
foreach (var holiday in HolidayList)
{
if (holiday.Date.DayOfWeek == DayOfWeek.Saturday)
holiday.Date = holiday.Date.AddDays(-1);
if (holiday.Date.DayOfWeek == DayOfWeek.Sunday)
holiday.Date = holiday.Date.AddDays(1);
}
//return
return HolidayList;
}
//specify which day of which week of a month and this function will get the date
//this function uses the month and year of the date provided
private static System.DateTime GetNthDayOfNthWeek(DateTime dt, DayOfWeek dayofWeek, int WhichWeek)
{
//get first day of the given date
System.DateTime dtFirst = new DateTime(dt.Year, dt.Month, 1);
//get first DayOfWeek of the month
System.DateTime dtRet = dtFirst.AddDays(6 - (int)dtFirst.AddDays(-1 * ((int)dayofWeek + 1)).DayOfWeek);
//get which week
dtRet = dtRet.AddDays((WhichWeek - 1) * 7);
//if day is past end of month then adjust backwards a week
if (dtRet >= dtFirst.AddMonths(1))
dtRet = dtRet.AddDays(-7);
//return
return dtRet;
}
// ..end
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment