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.

 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.