Art of BI: SQL Fiscal Calendar Build Script
Author: Christian Screen | | May 23, 2011
Okay, I can’t help it, I have been sitting on so much code and I am getting ready to start Open Sourcing a lot of it. More on that later this year. But as I scan through a lot of the files that I’ve been using on many a personal project over the last several years I see that several of the scripts and code snippets that I have written may be useful to others. So, consider this one of the many in a line of shared logic rants that are yet to come.
If you are an Oracle BI pro then you know that you can grab a better calendar from the BI Apps time dimensions. If you are SQL Server AS pro then you know that you can get a better calendar built by the time dimension wizard. However, if you are just looking for a quick and dirty calendar based a start and stop timeframe from which you have complete control, etc. then this will be a helpful start for your mission. Let me know if it helps. This was written originally for a SQL Server database but clearly just adopt it for Oracle by changing it to the related PL/SQL syntax.
--/////////////////////////////////////////////////////////
-- calendarBasic
--/////////////////////////////////////////////////////////
IF OBJECT_ID('calendarbasic') IS NOT NULL
DROP TABLE [dbo].[calendarBasic]
GO
CREATE TABLE [dbo].[calendarBasic]
(
dateID [int] NOT NULL PRIMARY KEY CLUSTERED,
[fullDate] datetime NOT NULL,
[dateName] [char](10) NOT NULL,
yearMonth int,
yearWeek int,
[YQMD] [char](10) NOT NULL,
[dayOfWeek] [int] NOT NULL,
[dayOfWeekName] [varchar](10) NOT NULL,
[dayOfMonth] [int] NOT NULL,
[monthName] [varchar](10) NOT NULL,
[monthAbbr] [char](3) NOT NULL,
[dayOfYear] [int] NOT NULL,
[isWeekDay] [bit] NOT NULL,
[isWeekEndDay] [bit] NOT NULL,
[isHoliday] [bit] NOT NULL,
[weekOfYear] [int] NOT NULL,
[monthOfYear] [int] NOT NULL,
[isLastDayOfMonth] [bit] NOT NULL,
[calendarQuarter] [int] NOT NULL,
[calendarSemester] [int] NOT NULL,
[calendarYear] [int] NOT NULL,
[fiscalMonthOfYear] [int] NOT NULL,
[fiscalQuarter] [int] NOT NULL,
[fiscalSemester] [int] NOT NULL,
[fiscalYear] [int] NOT NULL
)
GO
DECLARE @ProcessDate datetime
SELECT @ProcessDate = '2000-01-01' --initialize
WHILE @ProcessDate < = '2030-12-31'
BEGIN
--SELECT @ProcessDate --display
INSERT [dbo].[calendarBasic]
(
[dateID],
[FullDate],
[DateName],
yearMonth,
yearWeek,
[YQMD],
[DayOfWeek],
[DayOfWeekName],
[DayOfMonth],
[MonthName],
[MonthAbbr],
[DayOfYear],
[IsWeekDay],
[IsWeekEndDay],
[IsHoliday],
[WeekOfYear],
[MonthOfYear],
[IsLastDayOfMonth],
[CalendarQuarter],
[CalendarSemester],
[CalendarYear],
[FiscalMonthOfYear],
[FiscalQuarter],
[FiscalSemester],
[FiscalYear]
)
SELECT
CONVERT(int, CONVERT(char(8), @ProcessDate, 112)) AS [dateID],
CONVERT(datetime, @ProcessDate, 101) AS [FullDate],
CONVERT(char(10), @ProcessDate, 126) AS [DateName],
CONVERT(int, LEFT(CONVERT(char(6), @ProcessDate, 112), 6)) AS yearMonth,
CONVERT(int, LEFT(CONVERT(char(4), @ProcessDate, 112), 6) + RIGHT('00' + CONVERT(varchar(2), DATEPART(wk, @ProcessDate)), 2)) AS yearWeek,
CONVERT(char(10), CONVERT(char(4), DATEPART(yy, @ProcessDate)) +
N'0' + CONVERT(char(1), DATEPART(qq, @ProcessDate)) +
CASE WHEN LEN(DATEPART(mm, @ProcessDate)) < 2 THEN N'0' + CONVERT(char(1), DATEPART(mm, @ProcessDate)) ELSE + CONVERT(char(2), DATEPART(mm, @ProcessDate)) END +
CASE WHEN LEN(DATEPART(dd, @ProcessDate)) < 2 THEN N'0' + CONVERT(char(1), DATEPART(dd, @ProcessDate)) ELSE + CONVERT(char(2), DATEPART(dd, @ProcessDate)) END) AS [YQMD],
CONVERT(int, DATEPART(dw, @ProcessDate)) AS [DayOfWeek],
CONVERT(varchar(10), DATENAME(dw, @ProcessDate)) AS [DayOfWeekName],
CONVERT(int, DATEPART(dd, @ProcessDate)) AS [DayOfMonth],
CONVERT(varchar(10), DATENAME(mm, @ProcessDate)) AS [MonthName],
CONVERT(char(3), DATENAME(mm, @ProcessDate)) AS [MonthAbbr],
CONVERT(int, DATEPART(dy, @ProcessDate)) AS [DayOfYear],
CONVERT(bit, CASE WHEN DATENAME(dw, @ProcessDate) NOT IN (N'Saturday', N'Sunday') THEN 1 ELSE 0 END) AS [IsWeekDay],
CONVERT(bit, CASE WHEN DATENAME(dw, @ProcessDate) IN (N'Saturday', N'Sunday') THEN 1 ELSE 0 END) AS [IsWeekEndDay],
CONVERT(bit, 0) AS [IsHoliday],
CONVERT(int, DATEPART(wk, @ProcessDate)) AS [WeekOfYear],
CONVERT(int, DATEPART(mm, @ProcessDate)) AS [MonthOfYear],
CONVERT(bit, CASE WHEN DATEPART(mm, @ProcessDate) <> DATEPART(mm, DATEADD(dd, 1, @ProcessDate)) THEN 1 ELSE 0 END) AS [IsLastDayOfMonth],
CONVERT(int, DATEPART(qq, @ProcessDate)) AS [CalendarQuarter],
CONVERT(int, CASE WHEN DATEPART(mm, @ProcessDate) < 7 THEN 1 ELSE 2 END) AS [CalendarSemester],
CONVERT(int, DATEPART(yy, @ProcessDate)) AS [CalendarYear],
CONVERT(int, DATEPART(mm, @ProcessDate)) AS [FiscalMonthOfYear],
CONVERT(int, DATEPART(qq, @ProcessDate)) AS [FiscalQuarter],
CONVERT(int, CASE WHEN DATEPART(mm, @ProcessDate) < 7 THEN 1 ELSE 2 END) AS [FiscalSemester],
CONVERT(int, DATEPART(yy, @ProcessDate)) AS [FiscalYear]
SELECT @ProcessDate = DATEADD(dd, 1, @ProcessDate) --increment
END
GO
--SELECT * FROM calendarBasic
Blog Author
Related Posts
Oracle BI Publisher (BIP) Tips: Functions, Calculations & More
Check out these BI Publisher tips including functions & calculations so you can understand more about the production and support of BI Publisher reports.
How to Index a Fact Table – A Best Practice
At the base of any good BI project is a solid data warehouse or data mart.
Qlik vs. Tableau vs. Power BI: Which BI Tool Is Right for You?
Tableau, Power BI, and Qlik each have their benefits. What are they and how do you choose? Read this blog post for a quick analysis.