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:

Creating a Gantt-chart in SQL gantt 1

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
/

Creating a Gantt-chart in SQL gantt 2

 

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

  1. tony miller January 26, 2011
  2. Erik Kerkhoven December 15, 2009
  3. Susanne May 14, 2009
  4. Jan May 8, 2007
  5. Marco Gralike May 8, 2007
  6. Patrick Wolf May 7, 2007
  7. Eric May 7, 2007
  8. Gerard Grafhorst May 7, 2007