Calendar table for Data Warehouse
quarter of year
year_quarter (2013-3)
month of year
year_month (2013-08)
week of year
week of month
day of year
day of quarter
day of month
day of week
- Quarter
- Year
- Financial/Accounting Year
- Financial/Accounting Quarter
- isWeekend
- isWeekday
- isWorkDay
- WeekId (weeks since start of year)
- isLastDayofMonth
- DaysSince (e.g. days since 1/1/2000)
This is my list of possible columns in calendar dimension:
- Key
- Date
- Is Yesterday
- Is Today
- Is Tomorrow
- Day of Year
- Day of Halfyear
- Day of Quarter
- Day of Month
- Day of Week
- Day of Week Short Name
- Day of Week Short Name CS
- Day of Week Long Name
- Day of Week Long Name CS
- Days in Week
- Days in Month
- Days in Quarter
- Days in Halfyear
- Days in Year
- Reverse Day of Week
- Reversse Day of Month
- Reverse Day of Quarter
- Reverse Day of Halfyear
- Reverse Day of Year
- Is Last 7 days
- Is Last 14 days
- Is Last 30 days
- Is Last 90 Days
- Is Last 180 Days
- Is Last 365 Days
- Is Weekday
- Is Weekend
- Workday of Week
- Workday of Month
- Workday of Quarter
- Workday of Halfyear
- Workday of Year
- Reverse Workday of Week
- Reverse Workday of Month
- Reverse Workday of Quarter
- Reverse Workday of Halfyear
- Reverse Workday of Year
- Workdays in Week
- Workdays in Month
- Workdays in Quarter
- Workdays in Halfyear
- Workdays in Year
- Is Last Workday in Week
- Is Last Workday in Month
- Is Workday
- Is Holiday
- Is Future
- Is Past
- Is Previous Month
- Is Current Month
- Is Following Month
- Is Month to Date
- Is Beginning of Month
- Is End of Month
- Is Past Month
- Beginning of Month
- End of Month
- Month Number
- Month Name Long
- Month Name Long CS
- Month Name Short
- Month Name Short CS
- Month of Quarter
- Month of Halfyear
- Is Previous Week
- Is Current Week
- Is Following Week
- Is Week to Date
- Is Beginning of Week
- Is End of Week
- Is Past Week
- Beginning of Week
- End of Week
- Week Number
- Week Name Long
- Week Name Short
- Week of Month
- Is Previous Quarter
- Is Current Quarter
- Is Following Quarter
- Is Quarter to Date
- Is Beginning of Quarter
- Is End of Quarter
- Is Past Quarter
- Beginning of Quarter
- End of Quarter
- Quarter Number
- Quarter Name Long
- Quarter Name Long CS
- Quarter Name Short
- Is Previous Halfyear
- Is Current Halfyear
- Is Following Halfyear
- Is Halfyear to Date
- Is Beginning of Halfyear
- Is End of Halfyear
- Is Past Halfyear
- Beginning of Halfyear
- End of Halfyear
- Halfyear Number
- Halfyear Name Long
- Halfyear Name Long CS
- Halfyear Name Short
- Is Previous Year
- Is Current Year
- Is Following Year
- Is Year to Date
- Is Beginning of Year
- Is End of Year
- Is Past Year
- Beginning of Year
- End of Year
- Year Number
- Year Name Long
- Year Name Short
- Year Quarter Text
- Year Month Day
- Year Halfyear
- Year Quarter
- Year Month
- Year Day of Year
- Is Leap Year
- Distance in Days from Today
- Distance in Working Days from Today
- Distance in Calendar Weeks from Today
- Distance in Calendar Months from Today
- Distance in Calendar Quarters from Today
- Distance in Calendar Halfyears from Today
- Distance in Calendar Years from Today
- Nth Day of Week in Month
- Reverse Nth Day of Week in Month
I created interactive spreadsheet where you can create your own time dimension for PostgreSQL database.
Well Raj More, Its a nice Post and very helpful script for Creating a calender, The Other fields which you can include in the same table may be- 1) QuateroftheYear 2) IsWeekend 3) IsWeekday