----- Populate Date Dimesion ---- Created by Pradeesh Mathew
select * from dimdate
----------------------------------------------------------
CREATE TABLE [dbo].[DimDate](
[DateKey] [int] NOT NULL,
[Date] [date] NOT NULL,
[DateFullName] [nvarchar](50) NULL,
[DayofWeek] [tinyint] NOT NULL,
[DaynumberinMonth] [tinyint] NOT NULL,
[DaynumberOverall] [smallint] NOT NULL,
[DayName] [nvarchar](9) NOT NULL,
[DayAbbreviation] [nchar](3) NOT NULL,
[WeekNumberinYear] [tinyint] NOT NULL,
[WeekNumberOverall] [smallint] NOT NULL,
[WeekBeginDate] [smalldatetime] NULL,
[Month] [tinyint] NOT NULL,
[MonthNumberOverall] [smallint] NOT NULL,
[MonthName] [nvarchar](9) NOT NULL,
[MonthAbbreviation] [nchar](3) NOT NULL,
[Quarter] [nchar](2) NOT NULL,
[Year] [smallint] NOT NULL,
[YearMonth] [int] NOT NULL,
[FiscalMonth] [tinyint] NOT NULL,
[FiscalQuarter] [nchar](2) NOT NULL,
[FiscalYear] [smallint] NOT NULL,
[IsLastDayinMonth] [nchar](1) NOT NULL,
[SameDayYearAgoDate] [smalldatetime] NULL,
[DateType] [nvarchar](50) NULL,
[IsWeekend] [bit] NULL,
[IsLeapYear] [bit] NULL,
[IsHoliday] [bit] NOT NULL,
[HolidayName] [nvarchar](50) NOT NULL,
[Season] [nvarchar](50) NOT NULL,
[InsertAuditKey] [int] NOT NULL,
[UpdateAuditKey] [int] NOT NULL,
CONSTRAINT [PK_dbo.DimDate] PRIMARY KEY CLUSTERED
(
[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
---------------- sql code ------------------------------
DECLARE @FullDate DATETIME
DECLARE @DayofWeek tinyint
DECLARE @DayName nvarchar(10)
DECLARE @DayofMonth tinyint
DECLARE @DayofYear smallint
DECLARE @MonthName nvarchar(10)
DECLARE @CalWEEK tinyint
DECLARE @CalMONTH int
DECLARE @CalQUARTER tinyint
DECLARE @CalYEAR int
DECLARE @FisWEEK tinyint
DECLARE @FisMONTH int
DECLARE @FisQUARTER tinyint
DECLARE @FisYEAR int
DECLARE @IsWeekend BIT
--DECLARE @IsHoliday BIT
DECLARE @LeapYear BIT
SELECT @FisWeek = 1 --assuming we start with 1 Jan YYYY
SELECT @CalWeek = 1
SELECT @LeapYear =0
DECLARE @startdate DATETIME ='01/01/2010'
DECLARE @enddate DATETIME ='12/31/2011'
declare @overallday int=1
declare @overallMonth int=1
declare @overallyear int=1
SELECT @FULLDATE = @STARTDATE
WHILE @FullDate<=@enddate
BEGIN
SELECT @DAYOFWEEK = DATEPART (DW , @FULLDATE)
SELECT @DAYNAME = DATENAME(WEEKDAY,@FULLDATE)
SELECT @DAYOFMONTH = DATEPART (DAY , @FULLDATE)
SELECT @DAYOFYEAR = DATEPART (DY , @FULLDATE)
SELECT @MONTHNAME = DATENAME(MONTH,@FULLDATE)
SELECT @CALYEAR = DATEPART (YEAR, @FULLDATE)
SELECT @CALQUARTER = DATEPART (QUARTER, @FULLDATE)
SELECT @CALMONTH = DATEPART (MONTH , @FULLDATE)
SELECT @CALWEEK = DATEPART (WEEK , @FULLDATE)
SELECT @FISYEAR = DATEPART (YEAR, @FULLDATE)
SELECT @FISQUARTER = DATEPART (QUARTER, @FULLDATE)
SELECT @FISMONTH = DATEPART (MONTH , @FULLDATE)
SELECT @FISWEEK = DATEPART (WEEK , @FULLDATE)
SELECT @ISWEEKEND = 0
--check for leap year
IF ((@calyear % 4 = 0) AND (@calYEAR % 100 != 0 OR @calYEAR % 400 = 0))
SELECT @LeapYear =1
ELSE SELECT @LeapYear =0
INSERT INTO DIMDATE
SELECT CAST(CONVERT(VARCHAR(8),@FULLDATE,112) AS INT), --- DATEKEY
CAST(@FULLDATE AS DATE), --- DATE
@FULLDATE , --- DATEFULLNAME
@DAYOFWEEK, --- DATE OF THE WEEK
@DAYOFMONTH, --- DAY NUMBER IN MONTH
@overallday, --- DAYNUMBEROVERALL
@DAYNAME, --- DAY NAME
SUBSTRING(@DAYNAME,1,3), --- DAY ABBREVIATION
DATENAME(WEEK,@FULLDATE), --- WEEK NUMBER IN YEAR
(@overallday/7) + 1 , --- WEEKNUMBEROVERALL
DATEADD(WW, DATEDIFF(WW,0,@FULLDATE), 0), --- WEEKBEGINDATE STARTS WITH MONDAY
@CALMONTH, --- MONTH
0, --- OVER ALL MONTH NUMBER
@MONTHNAME, --- MONTHNAME
SUBSTRING(@MONTHNAME,1,3), --- ABBREVIATED MONTHNAME
'Q' + CAST(DATEPART(QUARTER,@FULLDATE) AS VARCHAR(1)),-- QUARTER
@CALYEAR, --- YEAR
0, --- YEAR MONTH
@FISMONTH,
@FISQUARTER,
@FISYEAR,
CASE WHEN DATEADD(S,-1,DATEADD(MM, DATEDIFF(M,0,@FULLDATE)+1,0))=@FULLDATE THEN 'Y'
ELSE 'N' END , --- IS LAST DAY IN MONTH
DATEADD(DD,-365,@FULLDATE), --- SAME DAY LAST YEAR
CASE WHEN @DAYNAME IN ('SUNDAY','SATURDAY') THEN 'Weekend'
ELSE 'Weekday' END , --- DATETYPE
CASE WHEN @DAYNAME IN ('SUNDAY','SATURDAY') THEN 1 -- Weekend bit
ELSE 0 END ,
@LeapYear, --- Is Leap Year
0, --- IS HOLIDAY
'No Holiday', --- HOLIDAY NAME
'Season Name', --- SEASONNAME
0, --- INSERTAUDITKEY
0 --- UPDATEDAUDITKEY
SET @FULLDATE = @FULLDATE +1
set @overallday=@overallday +1
END
