Oracle SQL script to generate Date Dimension data for use in Microsoft Excel Data Models

Date dimension is that one dimension or business perspective without which an Excel Data Model cannot ever exist. Here is an Oracle SQL script to generate the data for a Date Dimension. All that you have to do is change the start date and end date values in the in the last line of the below SQL query and execute it. Try it and experience the magic..!! Please leave your suggestions, comments and any enhancements that you might want to see in this.

Here is the downloadable script on Oracle LiveSQL:

/***********************
Description: Date Dimension Data Generation
Date Created: 14/Nov/2016
Author: Balakrishna Sappa
************************/

CREATE OR REPLACE TYPE DATE_OBJ AS OBJECT(DATE_KEY VARCHAR2(8), DATE_DT DATE, DATE_TYPE VARCHAR2(10), DAY_OF_MONTH SMALLINT, CAL_DAY_OF_YEAR SMALLINT, DAY_OF_WEEK_FULL_NAME VARCHAR2(9)
, DAY_OF_WEEK_SHORT_NAME CHAR(3), CAL_MONTH_FULL_NAME VARCHAR2(9), CAL_MONTH_SHORT_NAME CHAR(3), CAL_MONTH_ENUM SMALLINT, CAL_QTR_NAME CHAR(2), CAL_QTR_ENUM SMALLINT, CAL_HYR_NAME CHAR(2)
, CAL_HYR_ENUM SMALLINT, CAL_YEAR INT, START_OF_MONTH DATE, END_OF_MONTH DATE);
CREATE OR REPLACE TYPE CALENDAR_DATES IS TABLE OF DATE_OBJ;
CREATE OR REPLACE FUNCTION UDF_CALENDAR_GENERATOR(START_DATE IN DATE, END_DATE IN DATE)
RETURN CALENDAR_DATES
IS
CALENDAR_DATE CALENDAR_DATES;
BEGIN
SELECT CAST(MULTISET(WITH RCTE_DATES (DATE_DT)
AS
(SELECT START_DATE AS DATE_DT
FROM DUAL
UNION ALL
SELECT DATE_DT + 1 AS DATE_DT
FROM RCTE_DATES
WHERE DATE_DT + 1 <= END_DATE
)
SELECT CAST(EXTRACT(YEAR FROM DATE_DT) AS CHAR(4)) || SUBSTR(’00’ || EXTRACT(MONTH FROM DATE_DT), -2,2) || SUBSTR(’00’ || EXTRACT(DAY FROM DATE_DT), -2, 2) AS DATE_KEY
, DATE_DT
, ‘DATE’ AS DATE_TYPE
, EXTRACT(DAY FROM DATE_DT) AS DAY_OF_MONTH
, CAST(TO_CHAR(DATE_DT, ‘DDD’) AS SMALLINT) AS CAL_DAY_OF_YEAR
, TO_CHAR(DATE_DT, ‘DAY’) AS DAY_OF_WEEK_FULL_NAME
, TO_CHAR(DATE_DT, ‘DY’) AS DAY_OF_WEEK_SHORT_NAME
, TO_CHAR(DATE_DT, ‘MONTH’) AS CAL_MONTH_FULL_NAME
, TO_CHAR(DATE_DT, ‘MON’) AS CAL_MONTH_SHORT_NAME
, EXTRACT(MONTH FROM DATE_DT) AS CAL_MONTH_ENUM
, ‘Q’ || TO_CHAR(DATE_DT, ‘Q’) AS CAL_QTR_NAME
, CAST(TO_CHAR(DATE_DT, ‘Q’) AS SMALLINT) AS CAL_QTR_ENUM
, ‘H’ || CASE WHEN EXTRACT(MONTH FROM DATE_DT) IN (1,2,3,4,5,6)
THEN 1
ELSE 2
END AS CAL_HYR_NAME
, CASE WHEN EXTRACT(MONTH FROM DATE_DT) IN (1,2,3,4,5,6)
THEN 1
ELSE 2
END AS CAL_HYR_ENUM
, EXTRACT(YEAR FROM DATE_DT) AS CAL_YEAR
, TRUNC(DATE_DT, ‘MONTH’) AS START_OF_MONTH
, TRUNC(ADD_MONTHS(DATE_DT, 1), ‘MONTH’)-1 AS END_OF_MONTH
FROM RCTE_DATES
UNION
SELECT ‘999999’ AS DATE_KEY
, TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’) AS DATE_DT
, ‘NA’ AS DATE_TYPE
, 0 AS DAY_OF_MONTH
, 0 AS CAL_DAY_OF_YEAR
, ‘NA’ AS DAY_OF_WEEK_FULL_NAME
, ‘NA’ AS DAY_OF_WEEK_SHORT_NAME
, ‘NA’ AS CAL_MONTH_FULL_NAME
, ‘NA’ AS CAL_MONTH_SHORT_NAME
, 0 AS CAL_MONTH_ENUM
, ‘NA’ AS CAL_QTR_NAME
, 0 AS CAL_QTR_ENUM
, ‘NA’ AS CAL_HYR_NAME
, 0 AS CAL_HYR_ENUM
, 9999 AS CAL_YEAR
, TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’) AS START_OF_MONTH
, TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’) AS END_OF_MONTH
FROM DUAL
)
AS CALENDAR_DATES
)
INTO CALENDAR_DATE
FROM DUAL;
RETURN CALENDAR_DATE;
END;

Below is as screenshot of the results of this SQL executed in Oracle’s fee online SQL learning and execution tool: Oracle Live SQL.

datedimensiondatagenerator

 

Advertisements

A White Paper – Solving Relational Division problem in SQL – The Analyst Way

Hi readers..!!

Here I go posting a white paper on one of the classical and intellectually challenging problems that an analyst might come across. The paper is titled – “Understanding and Solving Relational Division problems in SQL – The Analyst way”.

Hope you enjoy reading this paper.

Please leave your comments and suggestions, if any, here.

Will be back with more stuff. Stay tuned to the blog. Until then, happy learning..!!

 

A Use Case of CROSS JOINS in SQL

Use case of SQL CROSS JOIN: Although CROSS JOIN is available in SQL, you rarely find any instance where you can use it. Here is one use case for CROSS JOIN in SQL:

Example with fictitious data: Assuming a tournament of Soccer to happen among four teams, what are the all possible matches between each of these teams, such that each team gets a chance to play with the remaining other three teams..?

You can use the CREATE and INSERT statements in the below screenshot 1 to create this table. Then run the SQL SELECT statement as in the screenshot 2 to get the desired results:

Screenshots:

image

Here is the SQL script containing all the statements that can be copied: SQL Cross Joins Use Case

Caveat: The CROSS JOIN here actually returns all permutation of the matches which includes the repetition of the combinations giving significance to the order of the teams in a match. i.e., a match between Brazil Vs Italy and Italy Vs Brazil will be treated as separate.

Solution: The predicate in the WHERE clause takes care of this and avoids any such repeated combinations and lets you disregard any significance attached to the order of the teams in a match.

Mathematical formula for Combinations

Happy learning..!! Don’t forget you leave your comments friends..!! Smile

Complement Negation: SQL Design Pattern to solve a frequently occurring interesting business data analysis problem

Here is an interesting business data analysis problem that I have come across multiple times in the recent times. Since I have seen this problem surfacing multiple times and the solution boils down to the same technique, it becomes an SQL Design Pattern and I named this baby as “Complement Negation”. Have shared the justification for the name at the end of this write-up.

Now, let me explain the scenario with an example using some fictitious data.

We have a table named “Projects” with the status details of various projects. Each project is given a unique project id. A Project may have multiple milestones where each milestone is identified by milestone id. The table also has the details of status of project and the status of each of the milestones. Status project and milestone can be either “closed” or “in-progress” (i.e., they are complementary).

Here we create the “Projects” table using below SQL:

—Step:1. Creating the table
CREATE TABLE Projects
(ProjectID VARCHAR(5)
, MilestoneID VARCHAR(5)
, ProjectStatus VARCHAR(15)
, MilestoneStatus VARCHAR(15)
PRIMARY KEY (ProjectID, MilestoneID)
);

Now let’s populate the table with some fictitious data using the below SQL:

INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P201’, ‘MS01’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P201’, ‘MS02’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P202’, ‘MS01’, ‘in-progress’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P202’, ‘MS03’, ‘in-progress’, ‘in-progress’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P202’, ‘MS02’, ‘in-progress’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P203’, ‘MS01’, ‘in-progress’, ‘in-progress’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS01’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS02’, ‘closed’, ‘in-progress’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS03’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS04’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS06’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P204’, ‘MS07’, ‘closed’, ‘in-progress’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P207’, ‘MS04’, ‘closed’, ‘closed’);
INSERT INTO Projects (ProjectID, MilestoneID, ProjectStatus, MilestoneStatus)
VALUES(‘P207’, ‘MS06’, ‘closed’, ‘in-progress’);

So, below is how the table looks:

SELECT * FROM Projects;

05June2013

Business rule is that a project is considered to be closed only when all the milestones under the project are closed.

However, the table has some records that does not satisfy this rule. e.g.: Project – P204 has two milestones (viz., MS02, MS07) whose status is “in-progress” but the project status itself is “closed”. Similarly, Project – P207 has one milestone (viz., MS06) whose status is “in-progress” but the project status itself is “closed”. These cases that doesn’t satisfy the business rule are considered abnormal cases/Projects.

Challenge at hand: Now the challenge at hand is to query the details of all abnormal projects (i.e., details or projects where project status is “closed” but the status of one or more milestones under the project is “in-progress”) and report the complete data.

Solution: Below is the SQL used to query/retrieve all the abnormal packages.

WITH temp AS (
SELECT ProjectID, MilestoneID, ProjectStatus, MilestoneStatus
, SUM(CASE WHEN MilestoneStatus = ‘in-progress’
                  THEN 1
                  ELSE 0
          END) OVER(PARTITION BY ProjectID) AS Count_Indicator
FROM Projects
)
SELECT ProjectID, MilestoneID, ProjectStatus, MilestoneStatus
FROM temp
WHERE Count_Indicator > 0
AND ProjectStatus = ‘closed’;

Results: The results appear as below:

ComplementNegation_SQLDesignPattern

Explanation: First, in the CTE, we retrieve all the details of projects that need to be reported. Then we derive a column that counts only the number of “in-progress” milestones under each project and name this derived column as, say Count_Indicator.

Now we retrieve the records where Project Status is “closed” but with the count of “in-progress” milestones (indicated by Count_Indicator derived column) being greater than 0 (i.e., there exists at least one milestone that is “in-progress”). This is nothing but retrieving the records where project status appears “closed” but with one or more underlying milestones still being “in-progress”.

Name Justification: The word Complement – since the status’ “closed” and “in-progress” are complementary (i.e., # of closed projects or milestones within each project plus # of in-progress projects or milestones within each project equals the total # of projects or milestones within each project respectively). The word Negation – since we test for status of one field for “closed” and the status of the other field for “in-progress” (i.e., “closed” test is negation of “in-progress” or vice versa).

Hence I feel the name for this SQL Design Pattern is justified to be “Complement – Negation”.

Hope you enjoyed reading this post. Do share your comments, suggestions, questions or clarifications, if any. Have great time until my next post..!! Smile

WordPress Tags: CTE,common table expression,Microsoft,sql server,t-sql,SQL,Complement,Negation,Design,Pattern,Here,data,analysis,times,solution,technique,justification,scenario,example,status,Project,Step,CREATE,TABLE,ProjectID,VARCHAR,MilestoneID,ProjectStatus,MilestoneStatus,PRIMARY,INSERT,INTO,VALUES,SELECT,FROM,records,cases,Challenge,packages,CASE,ELSE,OVER,PARTITION,Count_Indicator,WHERE,Results,Explanation,column,Name,word,Complementary,equals,itself

Truncate time part from Datetime column or literal in Oracle SQL

I have shown how to truncate the time part from a datetime column value in SQL Server T-SQL in one of my previous posts here –

https://analystarsenal.wordpress.com/2012/12/19/comparing-datetime-column-with-a-date-literal-in-t-sql/

Here is the same (how to truncate the time part from a datetime column value) in Oracle SQL:

TRUNC(datetime_column)

or

TRUNC(datetime_expression)

very straight forward…!! isn’t it.. ? 🙂

Inclusiveness of SQL – BETWEEN operator

Both parameters passed to the BETWEEN operator in SQL are inclusive to it.

e.g. SELECT * FROM Employees WHERE Age BETWEEN 20 and 30;

The above SQL returns all the records from the table – Employees where the age is >= 20 and <= 30. Here the employee records whose age is either 20 or 30 are also retrieved. See the below.

BETWEEN Operator in SQL
BETWEEN Operator in SQL

Happy time until my next blog post.. 🙂