Linear Calendar

The Problem

We were needing a tool to graphically show staff absences. We also needed a way of displaying project activities (like a Gantt chart). It struck me that there is not much difference between the two outputs, so I wrote a PL/SQL package to do both jobs.

Optimistic as always, I thought it would be a simple task, but it took a few evenings to crack it.

The Solution

I've included the source code for the header and body so you can play with it (please send me your improvements!), but as always, I can take no responsibility for any damage your use of this code may cause.

There are two procedures to call: the first, imaginitively called "SHOW", displays the calendar. The other, "SHOW_KEY", displays a key (but you already guessed that).

The SHOW procedure requires five parameters:

p_start_date - the first date to display

p_end_date - the last date to display

p_time_scale - the units in the horizontal access, 
  D = Days, 
  W = Weeks,
  M = Months
  Q = Quarters (using the British Tax year April - March)
  Y = Years (also using the British Tax year)

p_data_sql - a string containing the select statement to return the data in 
             the following format:
  select 
    row_title,  - appears in the first column.  Duplicate row_titles share a row.
    start_date, - the date the activity starts
    end_date,   - the date the activity ends
    key_code,   - a varchar code to the key table
    description - a description of the activity (appears as hover text)
  from 
    [the_data_source]

p_key_sql - a string containing the select statement to return the key in 
            the following format:
  select 
    key_code,   - the varchar code categorising the activity
    key_name,   - the category name
    key_colour  - the colour to display it using 6 digit hex RGB format 'FFFFFF'
  from 
    [the_key_source]

It would be fairly straightforward to adapt this for organisations that use calendar years rather than tax years.

The SHOW_KEY procedure only takes one parameter, the p_key_sql string as described above.