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.

[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]

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

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