RE: Date and Time dimension creation and population T-SQL

Chad Carter

Ten Centuries

Points: 1089

February 13, 2009 at 11:59 am

#943872

Here is the same code without the special characters.

--Create the tables

CREATE TABLE [dbo].[dim_Date](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Date] [datetime] NOT NULL,

[Day] [char](2) NOT NULL,

[DaySuffix] [varchar](4) NOT NULL,

[DayOfWeek] [varchar](9) NOT NULL,

[Month] [char](2) NOT NULL,

[MonthName] [varchar](9) NOT NULL,

[Quarter] [tinyint] NOT NULL,

[QuarterName] [varchar](6) NOT NULL,

[Year] [char](4) NOT NULL,

[StandardDate] [varchar](10) NULL,

[HolidayText] [varchar](50) NULL,

CONSTRAINT [PK_dim_Date] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

CREATE TABLE [dbo].[dim_Time](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Time] [char](8) NOT NULL,

[Hour] [char](2) NOT NULL,

[MilitaryHour] [char](2) NOT NULL,

[Minute] [char](2) NOT NULL,

[Second] [char](2) NOT NULL,

[AmPm] [char](2) NOT NULL,

[StandardTime] [char](11) NULL,

CONSTRAINT [PK_dim_Time] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

--Populate Date dimension --select count(*) from dim_date

print convert(varchar,getdate(),113)

truncate table dim_Date

DBCC CHECKIDENT (dim_Date, RESEED, 60000) --In case you need to add earlier dates later.

DECLARE @StartDate datetime

, @EndDate datetime

, @Date datetime

print getdate()

SELECT @StartDate = '1/1/1900'

, @EndDate = '1/1/2050'--Non inclusive. Stops on the day before this.

SELECT @Date = @StartDate

--select count(ID) from dim_Date --truncate table dim_Date

WHILE @Date < @EndDate

BEGIN

INSERT INTO dim_Date

(

[Date]

, [Day]

, [DaySuffix]

, [DayOfWeek]

, [Month]

, [MonthName]

, [Quarter]

, [QuarterName]

, [Year]

)

SELECT @Date [Date]

, DATEPART(DAY,@DATE) [Day]

, CASE

WHEN DATEPART(DAY,@DATE) IN (11,12,13) THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'th'

WHEN RIGHT(DATEPART(DAY,@DATE),1) = 1 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'st'

WHEN RIGHT(DATEPART(DAY,@DATE),1) = 2 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'nd'

WHEN RIGHT(DATEPART(DAY,@DATE),1) = 3 THEN CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'rd'

ELSE CAST(DATEPART(DAY,@DATE) AS VARCHAR) + 'th'

END AS [DaySuffix]

, CASE DATEPART(DW, @DATE)

WHEN 1 THEN 'Sunday'

WHEN 2 THEN 'Monday'

WHEN 3 THEN 'Tuesday'

WHEN 4 THEN 'Wednesday'

WHEN 5 THEN 'Thursday'

WHEN 6 THEN 'Friday'

WHEN 7 THEN 'Saturday'

END AS [DayOfWeek]

, DATEPART(MONTH,@DATE) [Month]

, DATENAME(MONTH,@DATE) [MonthName]

, DATEPART(qq,@DATE) [Quarter]

, CASE DATEPART(qq,@DATE)

WHEN 1 THEN 'First'

WHEN 2 THEN 'Second'

WHEN 3 THEN 'Third'

WHEN 4 THEN 'Fourth'

END AS [QuarterName]

, DATEPART(YEAR,@Date) [Year]

SELECT @Date = DATEADD(dd,1,@Date)

END

UPDATE dbo.dim_Date

SET [DAY] = '0' + [DAY]

WHERE LEN([DAY]) = 1

UPDATE dbo.dim_Date

SET [MONTH] = '0' + [MONTH]

WHERE LEN([MONTH]) = 1

UPDATE dbo.dim_Date

SET STANDARDDATE = [MONTH] + '/' + [DAY] + '/' + [YEAR]

--Add HOLIDAYS --------------------------------------------------------------------------------------------------------------

--THANKSGIVING --------------------------------------------------------------------------------------------------------------

CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))

TRUNCATE TABLE #tmpHoliday

INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])

SELECT [id], [YEAR], [DAY]

FROM dbo.dim_Date

WHERE [MONTH] = 11

AND [Dayofweek] = 'Thursday'

ORDER BY year, day

DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT

SELECT @CURRENTYEAR = MIN([YEAR])

, @STARTYEAR = MIN([YEAR])

, @ENDYEAR = MAX([YEAR])

FROM #tmpHoliday

SELECT @CNTR, @POS, @STARTYEAR, @ENDYEAR, @CURRENTYEAR

WHILE @CURRENTYEAR <= @ENDYEAR

BEGIN

SELECT @CNTR = COUNT([YEAR])

FROM #tmpHoliday

WHERE [YEAR] = @CURRENTYEAR

SET @POS = 1

WHILE @POS <= @CNTR

BEGIN

SELECT @MINDAY = MIN(DAY)

FROM #tmpHoliday

WHERE [YEAR] = @CURRENTYEAR

AND [WEEK] IS NULL

UPDATE #tmpHoliday

SET [WEEK] = @POS

WHERE [YEAR] = @CURRENTYEAR

AND [DAY] = @MINDAY

SELECT @POS = @POS + 1

END

SELECT @CURRENTYEAR = @CURRENTYEAR + 1

END

UPDATE DT

SET HolidayText = 'Thanksgiving Day'

FROM dbo.dim_Date DT

JOIN #tmpHoliday HL

ON HL.DateID = DT.ID

WHERE [WEEK] = 4

DROP TABLE #tmpHoliday

GO

--CHRISTMAS -------------------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET --IsHoliday = 1,

HolidayText = 'Christmas Day'

WHERE [MONTH] = 12 AND [DAY] = 25

--4th of July ---------------------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET --IsHoliday = 1,

HolidayText = 'Independance Day'

WHERE [MONTH] = 7 AND [DAY] = 4

-- New Years Day ---------------------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET --IsHoliday = 1,

HolidayText = 'New Year''s Day'

WHERE [MONTH] = 1 AND [DAY] = 1

--Memorial Day ----------------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET --IsHoliday = 1,

HolidayText = 'Memorial Day'

FROM dim_Date

WHERE ID IN

(

SELECT MAX([ID])

FROM dbo.dim_Date

WHERE [MonthName] = 'May'

AND [DayOfWeek] = 'Monday'

GROUP BY [YEAR], [MONTH]

)

--Labor Day -------------------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET --IsHoliday = 1,

HolidayText = 'Labor Day'

FROM dim_Date

WHERE ID IN

(

SELECT MIN([ID])

FROM dbo.dim_Date

WHERE [MonthName] = 'September'

AND [DayOfWeek] = 'Monday'

GROUP BY [YEAR], [MONTH]

)

-- Valentine's Day ---------------------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET --IsHoliday = 1,

HolidayText = 'Valentine''s Day'

WHERE [MONTH] = 2 AND [DAY] = 14

-- Saint Patrick's Day -----------------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET --IsHoliday = 1,

HolidayText = 'Saint Patrick''s Day'

WHERE [MONTH] = 3 AND [DAY] = 17

--Martin Luthor King Day ---------------------------------------------------------------------------------------

BEGIN TRY

drop table #tmpHoliday

END TRY

BEGIN CATCH

--do nothing

end catch

CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))

TRUNCATE TABLE #tmpHoliday

INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])

SELECT [id], [YEAR], [DAY]

FROM dbo.dim_Date

WHERE [MONTH] = 1

AND [Dayofweek] = 'Monday'

AND [YEAR] >= 1983

ORDER BY year, day

DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT

SELECT @CURRENTYEAR = MIN([YEAR])

, @STARTYEAR = MIN([YEAR])

, @ENDYEAR = MAX([YEAR])

FROM #tmpHoliday

WHILE @CURRENTYEAR <= @ENDYEAR

BEGIN

SELECT @CNTR = COUNT([YEAR])

FROM #tmpHoliday

WHERE [YEAR] = @CURRENTYEAR

SET @POS = 1

WHILE @POS <= @CNTR

BEGIN

SELECT @MINDAY = MIN(DAY)

FROM #tmpHoliday

WHERE [YEAR] = @CURRENTYEAR

AND [WEEK] IS NULL

UPDATE #tmpHoliday

SET [WEEK] = @POS

WHERE [YEAR] = @CURRENTYEAR

AND [DAY] = @MINDAY

SELECT @POS = @POS + 1

END

SELECT @CURRENTYEAR = @CURRENTYEAR + 1

END

UPDATE DT

SET HolidayText = 'Martin Luthor King Jr Day'

FROM dbo.dim_Date DT

JOIN #tmpHoliday HL

ON HL.DateID = DT.ID

WHERE [WEEK] = 3

DROP TABLE #tmpHoliday

--UPDATE dim_Date SET HOLIDAYTEXT = NULL WHERE HOLIDAYTEXT = 'Martin Luthor King Day' and year < 1983

GO

--President's Day ---------------------------------------------------------------------------------------

BEGIN TRY

drop table #tmpHoliday

END TRY

BEGIN CATCH

--do nothing

end catch

CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))

TRUNCATE TABLE #tmpHoliday

INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])

SELECT [id], [YEAR], [DAY]

FROM dbo.dim_Date

WHERE [MONTH] = 2

AND [Dayofweek] = 'Monday'

ORDER BY year, day

DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT

SELECT @CURRENTYEAR = MIN([YEAR])

, @STARTYEAR = MIN([YEAR])

, @ENDYEAR = MAX([YEAR])

FROM #tmpHoliday

WHILE @CURRENTYEAR <= @ENDYEAR

BEGIN

SELECT @CNTR = COUNT([YEAR])

FROM #tmpHoliday

WHERE [YEAR] = @CURRENTYEAR

SET @POS = 1

WHILE @POS <= @CNTR

BEGIN

SELECT @MINDAY = MIN(DAY)

FROM #tmpHoliday

WHERE [YEAR] = @CURRENTYEAR

AND [WEEK] IS NULL

UPDATE #tmpHoliday

SET [WEEK] = @POS

WHERE [YEAR] = @CURRENTYEAR

AND [DAY] = @MINDAY

SELECT @POS = @POS + 1

END

SELECT @CURRENTYEAR = @CURRENTYEAR + 1

END

UPDATE DT

SET --IsHoliday = 1,

HolidayText = 'President''s Day'

FROM dbo.dim_Date DT

JOIN #tmpHoliday HL

ON HL.DateID = DT.ID

WHERE [WEEK] = 3

DROP TABLE #tmpHoliday

GO

--Mother's Day ---------------------------------------------------------------------------------------

BEGIN TRY

drop table #tmpHoliday

END TRY

BEGIN CATCH

--do nothing

end catch

CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))

--drop table #tmpHoliday

TRUNCATE TABLE #tmpHoliday

INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])

SELECT [id], [YEAR], [DAY]

FROM dbo.dim_Date

WHERE [MONTH] = 5

AND [Dayofweek] = 'Sunday'

ORDER BY year, day

DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT

SELECT @CURRENTYEAR = MIN([YEAR])

, @STARTYEAR = MIN([YEAR])

, @ENDYEAR = MAX([YEAR])

FROM #tmpHoliday

WHILE @CURRENTYEAR <= @ENDYEAR

BEGIN

SELECT @CNTR = COUNT([YEAR])

FROM #tmpHoliday

WHERE [YEAR] = @CURRENTYEAR

SET @POS = 1

WHILE @POS <= @CNTR

BEGIN

SELECT @MINDAY = MIN(DAY)

FROM #tmpHoliday

WHERE [YEAR] = @CURRENTYEAR

AND [WEEK] IS NULL

UPDATE #tmpHoliday

SET [WEEK] = @POS

WHERE [YEAR] = @CURRENTYEAR

AND [DAY] = @MINDAY

SELECT @POS = @POS + 1

END

SELECT @CURRENTYEAR = @CURRENTYEAR + 1

END

UPDATE DT

SET HolidayText = 'Mother''s Day'

FROM dbo.dim_Date DT

JOIN #tmpHoliday HL

ON HL.DateID = DT.ID

WHERE [WEEK] = 2

DROP TABLE #tmpHoliday

GO

--Father's Day ---------------------------------------------------------------------------------------

BEGIN TRY

drop table #tmpHoliday

END TRY

BEGIN CATCH

--do nothing

end catch

CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))

TRUNCATE TABLE #tmpHoliday

INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])

SELECT [id], [YEAR], [DAY]

FROM dbo.dim_Date

WHERE [MONTH] = 6

AND [Dayofweek] = 'Sunday'

ORDER BY year, day

DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT

SELECT @CURRENTYEAR = MIN([YEAR])

, @STARTYEAR = MIN([YEAR])

, @ENDYEAR = MAX([YEAR])

FROM #tmpHoliday

WHILE @CURRENTYEAR <= @ENDYEAR

BEGIN

SELECT @CNTR = COUNT([YEAR])

FROM #tmpHoliday

WHERE [YEAR] = @CURRENTYEAR

SET @POS = 1

WHILE @POS <= @CNTR

BEGIN

SELECT @MINDAY = MIN(DAY)

FROM #tmpHoliday

WHERE [YEAR] = @CURRENTYEAR

AND [WEEK] IS NULL

UPDATE #tmpHoliday

SET [WEEK] = @POS

WHERE [YEAR] = @CURRENTYEAR

AND [DAY] = @MINDAY

SELECT @POS = @POS + 1

END

SELECT @CURRENTYEAR = @CURRENTYEAR + 1

END

UPDATE DT

SET HolidayText = 'Father''s Day'

FROM dbo.dim_Date DT

JOIN #tmpHoliday HL

ON HL.DateID = DT.ID

WHERE [WEEK] = 3

DROP TABLE #tmpHoliday

GO

--Halloween 10/31 ----------------------------------------------------------------------------------

UPDATE dbo.dim_Date

SET HolidayText = 'Halloween'

WHERE [MONTH] = 10 AND [DAY] = 31

--Election Day--------------------------------------------------------------------------------------

--The first Tuesday after the first Monday in November.

BEGIN TRY

drop table #tmpHoliday

END TRY

BEGIN CATCH

--do nothing

end catch

CREATE TABLE #tmpHoliday(ID INT IDENTITY(1,1), DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))

TRUNCATE TABLE #tmpHoliday

INSERT INTO #tmpHoliday(DateID, [YEAR],[DAY])

SELECT [id], [YEAR], [DAY]

FROM dbo.dim_Date

WHERE [MONTH] = 11

AND [Dayofweek] = 'Monday'

ORDER BY year, day

DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @CURRENTYEAR INT, @MINDAY INT

SELECT @CURRENTYEAR = MIN([YEAR])

, @STARTYEAR = MIN([YEAR])

, @ENDYEAR = MAX([YEAR])

FROM #tmpHoliday

WHILE @CURRENTYEAR <= @ENDYEAR

BEGIN

SELECT @CNTR = COUNT([YEAR])

FROM #tmpHoliday

WHERE [YEAR] = @CURRENTYEAR

SET @POS = 1

WHILE @POS <= @CNTR

BEGIN

SELECT @MINDAY = MIN(DAY)

FROM #tmpHoliday

WHERE [YEAR] = @CURRENTYEAR

AND [WEEK] IS NULL

UPDATE #tmpHoliday

SET [WEEK] = @POS

WHERE [YEAR] = @CURRENTYEAR

AND [DAY] = @MINDAY

SELECT @POS = @POS + 1

END

SELECT @CURRENTYEAR = @CURRENTYEAR + 1

END

UPDATE DT

SET HolidayText = 'Election Day'

FROM dbo.dim_Date DT

JOIN #tmpHoliday HL

ON (HL.DateID + 1) = DT.ID

WHERE [WEEK] = 1

DROP TABLE #tmpHoliday

GO

--------------------------------------------------------------------------------------------------------

print convert(varchar,getdate(),113)

--Load time data for every second of a day

DECLARE @Time DATETIME

SET @TIME = CONVERT(VARCHAR,'12:00:00 AM',108)

SELECT @TIME

SELECT CONVERT(VARCHAR,@TIME,108)

TRUNCATE TABLE dim_Time

WHILE @TIME <= '11:59:59 PM'

BEGIN

INSERT INTO dbo.dim_Time([Time], [Hour], [MilitaryHour], [Minute], [Second], [AmPm])

SELECT CONVERT(VARCHAR,@TIME,108) [Time]

, CASE

WHEN DATEPART(HOUR,@Time) > 12 THEN DATEPART(HOUR,@Time) - 12

ELSE DATEPART(HOUR,@Time)

END AS [Hour]

, CAST(SUBSTRING(CONVERT(VARCHAR,@TIME,108),1,2) AS INT) [MilitaryHour]

, DATEPART(MINUTE,@Time) [Minute]

, DATEPART(SECOND,@Time) [Second]

, CASE

WHEN DATEPART(HOUR,@Time) >= 12 THEN 'PM'

ELSE 'AM'

END AS [AmPm]

SELECT @TIME = DATEADD(second,1,@Time)

END

UPDATE dim_Time

SET [HOUR] = '0' + [HOUR]

WHERE LEN([HOUR]) = 1

UPDATE dim_Time

SET [MINUTE] = '0' + [MINUTE]

WHERE LEN([MINUTE]) = 1

UPDATE dim_Time

SET [SECOND] = '0' + [SECOND]

WHERE LEN([SECOND]) = 1

UPDATE dim_Time

SET StandardTime = [Hour] + ':' + [Minute] + ':' + [Second] + ' ' + AmPm

WHERE StandardTime is null

AND HOUR <> '00'

UPDATE dim_Time

SET StandardTime = '12' + ':' + [Minute] + ':' + [Second] + ' ' + AmPm

WHERE [HOUR] = '00'

--dim_date indexes

CREATE UNIQUE NONCLUSTERED INDEX [IDX_dim_Date_Date] ON [dbo].[dim_Date]

(

[Date] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_dim_Date_Day] ON [dbo].[dim_Date]

(

[Day] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Date_DayOfWeek] ON [dbo].[dim_Date]

(

[DayOfWeek] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Date_Month] ON [dbo].[dim_Date]

(

[Month] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Date_MonthName] ON [dbo].[dim_Date]

(

[MonthName] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Date_Quarter] ON [dbo].[dim_Date]

(

[Quarter] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Date_QuarterName] ON [dbo].[dim_Date]

(

[QuarterName] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Date_Year] ON [dbo].[dim_Date]

(

[Year] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Time_HolidayText] ON [dbo].[dim_Date]

(

[HolidayText] ASC

--dim_Time indexes

CREATE UNIQUE NONCLUSTERED INDEX [IDX_dim_Time_Time] ON [dbo].[dim_Time]

(

[Time] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Time_Hour] ON [dbo].[dim_Time]

(

[Hour] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Time_MilitaryHour] ON [dbo].[dim_Time]

(

[MilitaryHour] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Time_Minute] ON [dbo].[dim_Time]

(

[Minute] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Time_Second] ON [dbo].[dim_Time]

(

[Second] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Time_AmPm] ON [dbo].[dim_Time]

(

[AmPm] ASC

CREATE NONCLUSTERED INDEX [IDX_dim_Time_StandardTime] ON [dbo].[dim_Time]

(

[StandardTime] ASC

--USAGE EXAMPLES:

--Join date and time and give all records for a specific date and time.--------------------

DECLARE @DATETIME DATETIME

SET @DATETIME = '07/30/1976 4:01:02 PM'

SELECT [DiffDays] = DATEDIFF(dd,[DATE],GETDATE())

, [DiffYears] = DATEDIFF(dd,[DATE],GETDATE()) / 365.242199

,*, [StandardDateTime] = STANDARDDATE + ' ' + STANDARDTIME

FROM dim_Date DT

JOIN dim_Time TM

ON TM.TIME = CONVERT(VARCHAR,@DATETIME,108)

WHERE DATE = CONVERT(VARCHAR,@DATETIME,101)

-------------------------------------------------------------------------------------------

--GET MONTH AND YEAR WHERE IT HAS MORE THAN 4 FRIDAYS

SELECT Month, Year, COUNT(DAY)

FROM dim_Date

WHERE DAYOFWEEK = 'FRIDAY'

AND YEAR IN (2008, 2009)

GROUP BY MONTH, YEAR

HAVING COUNT(DAY) > 4

ORDER BY YEAR, MONTH

--Get the number of days per year.

SELECT YEAR, COUNT(DAY) [days]

FROM dim_Date

GROUP BY YEAR

--Get paydays where 1/2/2009 is a payday.

SELECT CAST((DATEDIFF(dd,'1/2/2009',DATE) / 14.00) AS VARCHAR) [DiffFromStart], *

FROM dim_Date

WHERE DAYOFWEEK = 'Friday'

AND DATE >= '1/2/2001'--Starting at this date

AND (DATEDIFF(dd,'1/2/2009',DATE) / 14.0) = ROUND((DATEDIFF(dd,'1/2/2009',DATE) / 14.0),0)

--Month and year where we get three paydays in one month from 2009 on...

SELECT MONTH, YEAR

FROM dim_Date

WHERE DATE >= '1/1/2009'

AND (DATEDIFF(dd,'1/2/2009',DATE) / 14.0) = ROUND((DATEDIFF(dd,'1/2/2009',DATE) / 14.0),0)

GROUP BY MonthName, Month, Year

HAVING COUNT(DAY) >= 3

ORDER BY YEAR, MONTH