Creating a Gantt-chart in SQL

There are many things that can help make this world a better place. A pure SQL-based Gantt-chart is not one of them. However, that is exactly what this article will present to you. So you might as well skip it.

Here's the deal: with a data set that represents tasks, events, memberships, worldrecords, job position or anything at all that has a startdate and an enddate, presentation in a Gantt-chart may be useful, to get a good insight in the relative ordering and length of these periods. A Gantt-chart displays all periods as horizontal bars, parallel to the time-axis. In SQL, that could look like this:

Here is how to create such a useful little chart in SQL.

Let's create a table with data that demands a Gantt-chart for proper display.

Note: only the very first in-line (view) query is specific for the data set, the other inline views are generic for the Gantt-chart.

Employee Historical Records

Let's take a look at another example: the EMP table in the venerate SCOTT schema. I have slightly manipulated this table, to be able to show the job history for all employees. First I have added the column firedate:

alter table emp
add (firedate date)
/

then I have randomly set the firedate, to reflect the whimsical nature of our president – who even sacked himself:

Next steps

For next steps we could look at day or week agendas with activities lasting only a few hours or less. And activities that repeat, have more than once instance. And at better axis-markers, showing the date-lables vertically instead of horizontally and not just beginning and end-date or time.

8 Comments

Sorry to be so late in responding to this blog entry.. However, how hard would you thinkÂ it would be to change the coding to be a resource gantt chart? Â Having resources on the X axis and having the name of an user using the resource displayed on the Y axis? Thank you,

I will be using this query a lot in the near future, as we have a lot of timelines in our datamodel. Mainly to visualize whether or not there are gaps in the timelines, which in most cases should not be present. I rank this query on the same level with the one Marco Gralike and Tom Kyte mentioned last week http://technology.amis.nl/blog/?p=1882

meta

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Email Address

About

AMIS is internationally recognized for its deep technological insight in Oracle technology. This knowledge is reflected in the presentations we deliver at international conferences such as Oracle OpenWorld, Hotsos and many user conferences around the world. Our AMIS Technology Blog, the most referred Oracle technology knowledge base outside the oracle.com domain. However you arrived here, we appreciate your interest in AMIS. Link to our Google+ Profile AMIS