title: Calendar table for Data Warehouse
body: <p>For my data warehouse, I am creating a calendar table as follows:</p>
  
  <pre><code>SET NOCOUNT ON
  
  DROP Table dbo.Calendar
  GO
  Create Table dbo.Calendar
  (
      CalendarId              Integer NOT NULL,
      DateValue               Date    NOT NULL,
      DayNumberOfWeek         Integer NOT NULL,
      NameOfDay               VarChar (10) NOT NULL,
      NameOfMonth             VarChar (10) NOT NULL,
      WeekOfYear              Integer NOT NULL,
      JulianDay               Integer NOT NULL,
      USAIsBankHoliday        Bit     NOT NULL,
      USADayName              VarChar (100) NULL,
  )
  ALTER TABLE dbo.Calendar ADD CONSTRAINT
      DF_Calendar_USAIsBankHoliday DEFAULT 0 FOR USAIsBankHoliday
  GO
  ALTER TABLE dbo.Calendar ADD CONSTRAINT
      DF_Calendar_USADayName DEFAULT '' FOR USADayName
  GO
  
  Declare @StartDate  DateTime = '01/01/2000'
  Declare @EndDate    DateTime = '01/01/2020'
  
  While @StartDate &lt; @EndDate
  Begin
      INSERT INTO dbo.Calendar 
      (
          CalendarId, 
          DateValue, 
          WeekOfYear,
          DayNumberOfWeek,
          NameOfDay,
          NameOfMonth,
          JulianDay
      )
      Values 
      (
          YEAR (@StartDate) * 10000 + MONTH (@StartDate) * 100 + Day (@StartDate), --CalendarId
          @StartDate,                 -- DateValue
          DATEPART (ww, @StartDate),  -- WeekOfYear
          DATEPART (dw, @StartDate),  -- DayNumberOfWeek
          DATENAME (dw, @StartDate),  -- NameOfDay
          DATENAME (M, @StartDate),   -- NameOfMonth
          DATEPART (dy, @StartDate)   -- JulianDay
      )
  
      Set @StartDate += 1
  End
  
  --=========================== Weekends
  -- saturday and sunday
  UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Weekend, ' WHERE DayNumberOfWeek IN (1, 7) 
  
  
  --=========================== Bank Holidays
  -- new years day
  UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'New Year''s Day, '  WHERE (CalendarId % 2000) IN (101)
  
  -- memorial day (last Monday in May)
  UPDATE dbo.Calendar 
  SET USAIsBankHoliday = 1, 
      USADayName += 'Memorial Day, '  
  WHERE 1=1
  AND CalendarId IN 
      (
          SELECT MAX (CalendarId)
          FROM dbo.Calendar 
          WHERE MONTH (DateValue) = 5 
          AND DATEPART (DW, DateValue)=2
          GROUP BY YEAR (datevalue)
      )
  
  -- independence day
  UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Independence Day, '  WHERE (CalendarId % 2000) IN (704)
  
  -- labor day (first Monday in September)
  UPDATE dbo.Calendar 
  SET USAIsBankHoliday = 1, 
      USADayName += 'Labor Day, '  
  WHERE 1=1
  AND CalendarId IN 
      (
          SELECT MIN (CalendarId)
          FROM dbo.Calendar 
          WHERE MONTH (DateValue) = 9
          AND DATEPART (DW, DateValue)=2
          GROUP BY YEAR (datevalue)
      )
  
  -- thanksgiving day (fourth Thursday in November)
  UPDATE dbo.Calendar 
  SET USAIsBankHoliday = 1, 
      USADayName += 'Thanksgiving Day, '  
  WHERE 1=1
  AND CalendarId IN 
      (
          SELECT Max (CalendarId)-2
          FROM dbo.Calendar 
          WHERE MONTH (DateValue) = 11
          AND DATEPART (DW, DateValue)=7
          GROUP BY YEAR (datevalue)
      )
  
  -- christmas
  UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Christmas Day, '  WHERE (CalendarId % 2000) IN (1225)
  
  --=========================== Other named days
  -- new years eve
  UPDATE dbo.Calendar SET USADayName += 'New Year''s Eve, '  WHERE (CalendarId % 2000) IN (1231)
  
  -- black friday (day after thanksgiving day)
  UPDATE dbo.Calendar SET USADayName += 'Black Friday, '  WHERE CalendarId IN (SELECT CalendarId+1 From dbo.Calendar Where USADayName like '%Thanksgiving%')
  
  -- christmas eve
  UPDATE dbo.Calendar SET USADayName += 'Christmas Eve, '  WHERE (CalendarId % 2000) IN (1224)
  
  -- boxing day
  UPDATE dbo.Calendar SET USADayName += 'Boxing Day, '  WHERE (CalendarId % 2000) IN (1226)
  
  --=========================== Remove trailing comma
  UPDATE dbo.Calendar SET USADayName = SubString (USADayName, 1, LEN (USADayName) -1) WHERE LEN (USADayName) &gt; 2
  
  SELECT * FROM dbo.Calendar
  </code></pre>
  
  <p><br><br><br>Here is the output of this command<br><br></p>
  
  <p><a href="https://i.stack.imgur.com/fiwWe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/fiwWe.png" alt=""></a>
  <br><br></p>
  
  <p>I have seen similar structures implemented in various flavours by data architects.</p>
  
  <p>My question is: What other data warehousing / dimension style useful information can I add to this table structure?</p>