Select Page

Art of BI: SQL Fiscal Calendar Build Script

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

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Art of BI: BI Publisher (BIP) Quick Guide and Tips

Read our blog post on how to take over production support of BI Publisher reports.

Sherry Milad | January 15, 2018

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.

Christian Screen | March 16, 2010

Art of BI: How to Add Comments in Oracle BI (OBIEE)

Ultimately the goal of commentary in OBIEE is to have a system for persisting feedback, creating a call to action, and recognizing the prolific users.

Christian Screen | December 29, 2013

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS