This article presents how to generate GANTT reports using sql.

A GANTT charts are commonly used in project management or batch processing, as one of the most popular and useful ways of showing activities (tasks or events) displayed against time. On the left of the chart is a list of the activities and along the top is a suitable time scale. Each activity is represented by a bar; the position and length of the bar reflects the start date, duration and end date of the activity. This allows you to see at a glance:

• What the various activities are
• When each activity begins and ends
• How long each activity is scheduled to last
• Where activities overlap with other activities, and by how much
• The start and end date of the whole project

First I need to generate dummy data

CREATE TABLE test_tbl
(
  job_id       NUMBER,
  job_name     VARCHAR2(10),
  job_data     NUMBER,
  start_date   DATE,
  end_date     DATE
);

INSERT INTO test_tbl
SELECT level, 'job_'||level, level*5, null, null
  FROM dual
CONNECT BY LEVEL <= 5;

COMMIT;

SELECT * 
FROM test_tbl;

JOB_ID JOB_NAME     JOB_DATA START_DATE  END_DATE 
------ ---------- ---------- ----------- ---------
     1 job_1               5                      
     2 job_2              10                      
     3 job_3              15                      
     4 job_4              20                      
     5 job_5              25

Update dates

UPDATE test_tbl
SET 
  start_date = to_date('01-06-1995','dd-mm-yyyy'), 
  end_date = to_date('15-08-2004','dd-mm-yyyy')
WHERE job_id IN (2);

UPDATE test_tbl
SET 
  start_date = to_date('01-09-1999','dd-mm-yyyy'), 
  end_date = to_date('01-03-2009','dd-mm-yyyy')
WHERE job_id IN (3);

UPDATE test_tbl
SET 
  start_date = to_date('01-09-2010','dd-mm-yyyy'), 
  end_date = to_date('01-03-2012','dd-mm-yyyy')
WHERE job_id IN (4);

UPDATE test_tbl
SET 
  start_date = to_date('20-05-1998','dd-mm-yyyy')
WHERE job_id IN (5);

COMMIT;

and finally create nice GANTT report

ALTER SESSION SET NLS_LANGUAGE=english;

WITH periods AS
( 
  SELECT 
    job_name label,
    CAST(start_date   AS DATE) start_date,
    nvl(CAST(end_date AS DATE), SYSDATE) end_date
    FROM test_tbl
  ORDER BY start_date
), 
limits AS
( 
  SELECT
    MIN(start_date) per_start,
    MAX(end_date)   per_end,
    40              width
   FROM periods
),
bars AS
( 
  SELECT   
    label,
    lpad(label, '6')||'|' activity,
    (start_date - per_start)/(per_end - per_start) * width from_pos,
    (end_date - per_start)/(per_end - per_start) * width   to_pos
  FROM     
    periods, limits
  ORDER BY label  
)
SELECT  
  label, 
  activity||lpad('I',from_pos)||
  rpad('-', to_pos - from_pos, '-')||'I' gantt
FROM     bars
UNION ALL
SELECT 'TOTAL', lpad('|',7)
       ||to_char(per_start,'DD-MON-YYYY')
       ||lpad(to_char(per_end,'DD-MON-YYYY'), width - 11)
FROM   limits
/

LABEL   GANTT
------- --------------------------------------------------
job_1   job_1|        I--------I
job_2   job_2|-----------------I
job_3   job_3|       I------------------I
job_4   job_4|                            I--I
job_5   job_5|    I----------------------------------I
TOTAL        |01-JUN-1995                  30-DEC-2015

of course it would make more sense to add start_date and end_date to each line 🙂

Have a fun 🙂

Tomasz