Art of BI: SQL Fiscal Calendar Build Script

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.

[sourcecode language=”sql”]
–/////////////////////////////////////////////////////////
— 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 [/sourcecode]

Contact Us
Christian Screen
Christian is an innovator in analytics and data warehousing design, best practices, and delivery. With more than fifteenyears of decision support and data warehousing with key experiences at Office Depot HQ, Sierra-Cedar, and Capgemini, he oversees the Oracle Analytics Practice which includes the technical development and delivery of Oracle BI collaboration software, data warehouse solutions, Oracle BI/EPM projects, and packaged analytics solutions at Datavail.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).

1 thought on “Art of BI: SQL Fiscal Calendar Build Script”
  1. Hi, i’d like to make a difference beteween calendar year and fiscal. Couse my fiscal period starts at december. How to modify your code to achive it?