Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Thursday, December 15, 2011

DimDate - Date Dimension

The below script helps to create valid date dimension attribures:


USE

[DatabaseName]

GO

IF OBJECT_ID('Date','U') IS NOT NULL

DROP TABLE Date

GO

 

CREATE TABLE [dbo].[Date](

[DateSK] [int] NOT NULL,

[FullDate] [datetime] NOT NULL,

[DateName] [char](11) NOT NULL,

[DayOfWeek] [tinyint] NOT NULL,

[DayNameOfWeek] [char](10) NOT NULL,

[DayOfMonth] [tinyint] NOT NULL,

[DayOfYear] [smallint] NOT NULL,

[WeekdayWeekend] [char](7) NOT NULL,

[WeekOfYear] [tinyint] NOT NULL,

[MonthName] [char](10) NOT NULL,

[MonthOfYear] [tinyint] NOT NULL,

[CalendarQuarter] [tinyint] NOT NULL,

[CalendarYear] [smallint] NOT NULL,

[CalendarYearMonth] [char](7) NOT NULL,

[CalendarYearQtr] [char](15) NOT NULL,

CONSTRAINT PK_Date_DateID PRIMARY KEY (DateSK)

) ON [PRIMARY]

GO

RAISERROR
('Table Date created successfully!',0,1)

DECLARE @StartDate datetime, @EndDate datetime

-- Set StartDate and EndDate as per your requirement

SELECT @StartDate = '2009-01-01', @EndDate = '2010-12-31'

WHILE (@StartDate <= @EndDate )

BEGIN

INSERT
INTO Date

SELECT

CAST(CONVERT(varchar(8),@StartDate,112) AS int) DateSK

,@StartDate AS [Date]

,CONVERT(varchar(20),@StartDate,106) AS DateName

,DATEPART(DW,@StartDate) [DayOfWeek]

,DATENAME(DW,@StartDate) [DayNameOfWeek]

,DATENAME(DD,@StartDate) [DayOfMonth]

,DATENAME(DY,@StartDate) [DayOfYear]

,CASE WHEN DATEPART(DW,@StartDate) IN (1,7) THEN 'WeekEnd'

ELSE 'WeekDay' END [WeekdayWeekend]

,DATEPART(WW,@StartDate) [WeekOfYear]

,DATENAME(MM ,@StartDate) [MonthName]

,DATEPART(MM ,@StartDate) [MonthOfYear]

,DATEPART(QQ,@StartDate) [CalendarQuarter]

,DATEPART(YY ,@StartDate) [CalendarYear]

,DATENAME(YY,@StartDate)+'-'+RIGHT('0'+CAST(Month(@StartDate) as varchar),2) [CalendarYearMonth]

,DATENAME(YY,@StartDate)+'-Q'+DATENAME(QQ,@StartDate) [CalendarYearQtr]

SET @StartDate = @StartDate +1

END

GO

 

No comments:

Post a Comment