SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Date Dimension Script

By Dave Bunch,

T-SQL script that creates a Date-Dimension and populates it for any date-range desired. Includes the following attributes:

Attribute Example Attribute Example
DateKey 20170510 PrevMonthFirstDay 2017-04-01
DateValue 2017-05-10 PrevMonthLastDay 2017-04-30
DaylightSavings_flag 1 QuarterDayNumber 40
DayName Wednesday QuarterDays 91
MonthDayNumber 10 QuarterFirstDay 2017-04-01
MonthDayOccurrence 2 QuarterLastDay 2017-06-30
MonthDays 31 WeekDayNumber 4
MonthFirstDay 2017-05-01 WeekFirstDay 2017-05-07
MonthLastDay 2017-05-31 WeekLastDay 2017-05-13
MonthName May YearDayNumber 130
NextMonthFirstDay 2017-06-01 YearMonth 2017-05
NextMonthLastDay 2017-06-30 YearMonthNumber 5
PreviousMonth 4 YearNumber 2017
PreviousQuarter 1 YearQuarterNumber 2
PreviousYear 2016 YearWeekNumber 19

Includes columns specific to auto-dealerships which can be re-purposed and/or omitted:

  • CurrentBusDay
  • SalesHoliday
  • SalesHolidayName
  • ServiceHoliday
  • ServiceHolidayName
  • TotSalesBusDay
  • TotServiceBusDay

Also includes DaylightSavings_flag column (code for which was hard to find).

Special Kudos to jhadden's  'Federal Holiday Function' post: https://www.sqlservercentral.com/Forums/1248417/Federal-Holiday-Function.

Total article views: 583 | Views in the last 30 days: 30
 
Related Articles
FORUM

connection attributes

view attributes of existing connections

FORUM

Two attributes multiple Entities

Two attributes multiple Entities

FORUM

file attributes

File attributes

FORUM

How to add attribute from Parent element to child element's attribute using visual studio designer

How to add attribute from Parent element to child element's attribute using visual studio designer

Tags
data warehousing    
database design    
development    
miscellaneous    
other    
sql server    
sql server 2012    
sql server 2016    
t-sql    
 
Contribute