Creating a Gantt-chart in SQL

8

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.

 create table activities
( label varchar2(100)
, start_date date
, end_date date
)
/

Let's add some data to that table.

insert into activities
( label, start_date, end_date )
values
( 'Functional Analysis'
, to_date('12-03-2001','DD-MM-YYYY')
, to_date('12-11-2001','DD-MM-YYYY')
)
/
insert into activities
( label, start_date, end_date )
values
( 'Technical Architecture'
, to_date('04-05-2001','DD-MM-YYYY')
, to_date('18-08-2002','DD-MM-YYYY')
)
/
insert into activities
( label, start_date, end_date )
values
( 'Technical Design'
, to_date('01-12-2001','DD-MM-YYYY')
, to_date('24-06-2002','DD-MM-YYYY')
)
/
insert into activities
( label, start_date, end_date )
values
( 'Development'
, to_date('01-05-2002','DD-MM-YYYY')
, to_date('12-10-2003','DD-MM-YYYY')
)
/
insert into activities
( label, start_date, end_date )
values
( 'Acceptance Test'
, to_date('10-09-2003','DD-MM-YYYY')
, to_date('22-03-2004','DD-MM-YYYY')
)
/

And now for a little SQL query that returns the chart: 

with periods as
( select label
  ,      start_date
  ,      end_date
  from   activities
)
, limits as -- determine the earliest starting date and the latest end date to determine the overall width of the chart
( select min(start_date) period_start
  ,      max(end_date) period_end
  ,      80 width -- set the width as the number of characters
  from   periods
)
, bars as
( select   lpad(label, '20')||'|' activity
  ,        (start_date - period_start)/(period_end - period_start) * width from_pos -- the starting position for the bar
  ,        (end_date - period_start)/(period_end - period_start)   * width to_pos   -- the end position for the bar
  from     periods
  ,        limits
)
select  activity||
        lpad('I',from_pos)
         ||rpad('-', to_pos - from_pos, '-')
         ||'I' gantt
from     bars
union all
select rpad('_',width + 22,'_')
from   limits
union all
select lpad('|',21)
       ||to_char(period_start,'DD-MON-YYYY')
       ||lpad(to_char(period_end,'DD-MON-YYYY'), width - 11)
from   limits
/

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:

update emp
set    firedate = hiredate + 500 * (1.1 - dbms_random.value)
/

The query to produce the Gantt-chart is almost the same as before, only the first in-line view is different:

with periods as
( select ename label
  ,      hiredate start_date
  ,      firedate end_date
  from   emp
  order
  by     hiredate
)
, limits as
( select min(start_date) period_start
  ,      max(end_date) period_end
  ,      80 width
  from   periods
)
, bars as
( select   lpad(label, '20')||'|' activity
  ,        (start_date - period_start)/(period_end - period_start) * width from_pos
  ,        (end_date - period_start)/(period_end - period_start) * width to_pos
  from     periods
  ,        limits
)
select  activity||
        lpad('I',from_pos)
         ||rpad('-', to_pos - from_pos, '-')
         ||'I' gantt
from     bars
union all
select rpad('_',width + 22,'_')
from   limits
union all
select lpad('|',21)
       ||to_char(period_start,'DD-MON-YYYY')
       ||lpad(to_char(period_end,'DD-MON-YYYY'), width - 11)
from   limits
/

 

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.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

8 Comments

  1. 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,

    Tony Miller
    LuvMuffin Software

     

  2. Hi there,

    I wold love to try this query out but the whole page appears to be broken .. :-(

    Cheers,
    Susanne

  3. 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