Creating a Gantt-chart in SQL

0 0

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.

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

lucas.jellema@amis.nl

https://www.amis.nl/

Happy

0 0 %

Sad

0 0 %

Excited

0 0 %

Sleepy

0 0 %

Angry

0 0 %

Surprise

0 0 %

Related posts:

Oracle Quiz on SQL and PL/SQL – See Water Burning – paper for ODTUG 2005 Death of the End Date – How LAG and LEAD help fight redundancy PL/SQL Source Code Control inside the database – After Compile trigger for automatic archiving New PL/SQL Compiler Warning in Oracle 11g Oracle 11g for Developers – presenting at Oracle Benelux User Group Conference 2007