Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Date and Time dimension creation and population T-SQL Expand / Collapse
Author
Message
Posted Tuesday, March 8, 2011 10:18 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:40 PM
Points: 733, Visits: 75
Sorry for the criticism, but I find this article very poorly written. There is no information given why I would want to like into this code, what purpose it serves, or what problem it cures. It is just lacking background information as a whole. Personally, I’m not prone to dig into code without the slightest hint or tease as to why I would want to.
Post #1074956
Posted Tuesday, May 3, 2011 8:07 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 21, 2014 8:35 AM
Points: 3,642, Visits: 72,429
Since we're sharing our Date Dimensions...

I did my latest incarnation of it mostly using derived columns. So far performance seems to have improved over when I was using all typed columns. We've got combined Canadian and US businesses working on the same data, so that's why there's a US and CA values.

It's populated using Jeff Moden's Tally table to generate Calendar_dates, the other values are imparted by loading data from OLTP managed calendar tables to indicate holidays and partial holidays.

CREATE TABLE [dim_calendar](
[Calendar_Key] [int] IDENTITY(1,1) NOT NULL,
[Calendar_Date] [date] NOT NULL,
[US_Holiday_Indicator] [varchar](10) NOT NULL,
[CA_Holiday_Indicator] [varchar](10) NOT NULL,
[US_Working_Days_In_Month] [int] NOT NULL,
[CA_Working_Days_In_Month] [int] NOT NULL,
[US_Working_Hours_In_Day] [int] NOT NULL,
[CA_Working_Hours_In_Day] [int] NOT NULL,
[Week_Ending_Date] AS (dateadd(day,(7)-datepart(weekday,[Calendar_Date]),[Calendar_date])),
[Year_Numeric] AS (datepart(year,[calendar_date])),
[Year_Text] AS (datename(year,[Calendar_Date])),
[Quarter_Numeric] AS (datepart(quarter,[Calendar_Date])),
[Quarter_Smart_key] AS (datepart(year,[Calendar_Date])*(100)+datepart(quarter,[Calendar_date])),
[Quarter_Text_QQYYYY] AS ((('Q'+datename(quarter,[Calendar_Date]))+' ')+datename(year,[Calendar_Date])),
[Quarter_Text_YYYYQQ] AS ((datename(year,[Calendar_Date])+' Q')+datename(quarter,[Calendar_Date])),
[Month_Numeric] AS (datepart(month,[Calendar_Date])),
[Month_Smart_Key] AS (datepart(year,[Calendar_Date])*(100)+datepart(month,[Calendar_Date])),
[Month_Text] AS ((datename(month,[Calendar_Date])+' ')+datename(year,[CAlendar_Date])),
[Day_Numeric] AS (datepart(day,[Calendar_Date])),
[Day_Smart_Key] AS (((datepart(year,[Calendar_Date])*(1000000)+datepart(quarter,[Calendar_Date])*(10000))+datepart(month,[Calendar_Date])*(100))+datepart(day,[Calendar_Date])),
[Day_In_Week] AS (datepart(weekday,[Calendar_Date])),
[Day_Text] AS ((((datename(month,[Calendar_Date])+' ')+datename(day,[Calendar_Date]))+', ')+datename(year,[Calendar_Date])),
[Day_Name] AS (datename(weekday,[Calendar_Date])),
[Day_Type] AS (case when datename(weekday,[Calendar_Date])='Sunday' OR datename(weekday,[Calendar_Date])='Saturday' then 'Weekend' else 'Weekday' end),
[Thirty_Day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(30) else datediff(day,[Calendar_Date],getdate())/(30) end),
[Thirty_Day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(30)),(0))+' 30 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(30) when (0) then 'Current 30 Day Period' when (1) then 'Prior 30 Day Period' when (2) then 'Tertiary 30 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(30),(0))+' 30 Day Periods Ago' end end),
[Ninety_day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(90) else datediff(day,[Calendar_Date],getdate())/(90) end),
[Ninety_day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(90)),(0))+' 90 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(90) when (0) then 'Current 90 Day Period' when (1) then 'Prior 90 Day Period' when (2) then 'Tertiary 90 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(90),(0))+' 90 Day Periods Ago' end end),
[Threehundredsixtyfive_day_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(day,[Calendar_Date],getdate())/(365) else datediff(day,[Calendar_Date],getdate())/(365) end),
[Threehundredsixtyfive_day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(day,[Calendar_Date],getdate())/(365)),(0))+' 365 Day Period(s) in the Future' else case datediff(day,[Calendar_Date],getdate())/(365) when (0) then 'Current 365 Day Period' when (1) then 'Prior 365 Day Period' when (2) then 'Tertiary 365 Day Period' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate())/(365),(0))+' 365 Day Periods Ago' end end),
[Six_Month_Periods] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then (-1)+datediff(month,[Calendar_Date],getdate())/(6) else datediff(month,[Calendar_Date],getdate())/(6) end),
[Six_Month_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs((-1)+datediff(month,[Calendar_Date],getdate())/(6)),(0))+' 6 Month Period(s) in the Future' else case datediff(month,[Calendar_Date],getdate())/(6) when (0) then 'Current 6 Month Period' when (1) then 'Prior 6 Month Period' when (2) then 'Tertiary 6 Month Period' else CONVERT([varchar],datediff(month,[Calendar_Date],getdate())/(6),(0))+' 6 Month Periods Ago' end end),
[Quarters_Periods] AS (datediff(quarter,[Calendar_Date],getdate())),
[Quarters_Periods_Text] AS (case when datediff(quarter,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(quarter,[Calendar_Date],getdate())),(0))+' Quarter(s) in the future' else case datediff(quarter,[Calendar_Date],getdate()) when (0) then 'Current Quarter' when (1) then 'Prior Quarter' when (2) then 'Tertiary Quarter' else CONVERT([varchar],datediff(quarter,[Calendar_Date],getdate()),(0))+' Quarters Ago' end end),
[Months_Periods] AS (datediff(month,[Calendar_Date],getdate())),
[Months_Periods_Text] AS (case when datediff(month,[Calendar_Date],getdate())<(0) then CONVERT([varchar],abs(datediff(month,[Calendar_Date],getdate())),(0))+' Month(s) in the Future' else case datediff(month,[Calendar_Date],getdate()) when (0) then 'Current Month' when (1) then 'Prior Month' when (2) then 'Tertiary Month' else CONVERT([varchar],datediff(month,[Calendar_Date],getdate()),(0))+' Months Ago' end end),
[Day_Periods] AS (datediff(day,[Calendar_Date],getdate())),
[Day_Periods_Text] AS (case when datediff(day,[Calendar_Date],getdate())<(0) then case datediff(day,[Calendar_Date],getdate()) when (-1) then 'Tomorrow' else CONVERT([varchar],abs(datediff(day,[Calendar_Date],getdate())),(0))+' Days in the future' end when datediff(day,[Calendar_Date],getdate())=(0) then 'Today' when datediff(day,[Calendar_Date],getdate())=(1) then 'Yesterday' else CONVERT([varchar],datediff(day,[Calendar_Date],getdate()),(0))+' Days Ago' end),
[Week_Periods] AS (datediff(week,[Calendar_Date],getdate())),
[Week_Periods_Text] AS (case when datediff(week,[Calendar_Date],getdate())<(0) then case datediff(week,[Calendar_Date],getdate()) when (-1) then 'Next Week' else CONVERT([varchar],abs(datediff(week,[Calendar_Date],getdate())),(0))+' Weeks in the future' end when datediff(week,[Calendar_Date],getdate())=(0) then 'This Week' when datediff(week,[Calendar_Date],getdate())=(1) then 'Last Week' else CONVERT([varchar],datediff(week,[Calendar_Date],getdate()),(0))+' Weeks Ago' end),
[Days_In_Month] AS (datediff(day,dateadd(month,datediff(month,(0),[Calendar_Date]),(0)),dateadd(month,(1)+datediff(month,(0),[Calendar_Date]),(0)))),
CONSTRAINT [PK__dim_cale__3C52D19446486B8E] PRIMARY KEY CLUSTERED
(
[Calendar_Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]





--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1102371
Posted Tuesday, December 6, 2011 8:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 18, 2013 6:16 AM
Points: 1, Visits: 4
daleintacoma (3/8/2011)
Sorry for the criticism, but I find this article very poorly written. There is no information given why I would want to like into this code, what purpose it serves, or what problem it cures. It is just lacking background information as a whole. Personally, I’m not prone to dig into code without the slightest hint or tease as to why I would want to.


Most people that would search on this article, or come into it, know what it is and what it is used for. It not so much an article as it is script... and that is why it is found in the scripts section and not the articles. If you have a sandbox you can run the code against you can see it's output tables. If you know warehousing, you'll know why/how you'd use it. If you don't, I'd suggest you read a datawarehousing book first.
Post #1217164
Posted Tuesday, February 4, 2014 6:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:02 PM
Points: 16, Visits: 104
Couple typos:
- Independence Day
- Martin Luther King Jr
Post #1537726
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse