MySql Store Procedure (Routine) to break a date range into a record per day for reporting


Problem!

Project resource assignments are tracked in a Excel Spreadsheet, the following data as outline in the table below is tracked.

Person
Name
TitleRoleStatusAllocationProject NameProject NumberStart DateEnd DateClientProject TypeDepartmentEmployee NumberProduct SkillSkillset
Person ABusiness AnalystBusiness AnalystFull Time100%Web ImplementationP0022/15/20104/2/2010Client AClientBusiness Services00001    
Person BDeveloperDeveloperContractor20%New Corporate Web SiteP0044/5/20108/6/2010MarketingInternalTechnical Delivery00002Java, JbossC, Java

 

Utilizing the pivotal table feature of Excel, utilization can be tracked. To show the utilization an excel formula is used to display the utilization percentage, 100% meaning fully utilized during that period. See below the pivotal view and on the right side the allocation by week.

                 Total Utilization for Weeks ViewedWeek Starting On    
Person NameTitleRoleProject NameProject NumberProject TypeClientAllocation Start DateAllocation End Date  2/15/20102/22/20103/1/2010
Person ABusiness AnalystBusiness AnalystWeb ImplementationP002Client AClient A2/15/20104/2/2010100%100%100%100%  
Person BDeveloperDeveloperNew Corporate Web SiteP004InternalMarketing4/5/20108/6/20100%0%0%0%  

This is a simple way of doing project and portfolio management, some simple reporting can be done using Excel, more sophisticated reports is better done using Crystal Reports or any open source report designer. To really use the power of Crystal Reports the data as shown above, needs to be moved to a database.

The Solution

Mysql was chosen as the db, a db table was setup named host_sheet, the table structure followed the columns of the excel spreadsheet, as shown below.

Table host_sheet
—————-
host_sheet_id INT
host_sheet_person VARCHAR
host_sheet_title VARCHAR
host_sheet_role VARCHAR
host_sheet_start_date DATETIME
host_sheet_end_date DATETIME
host_sheet_allocation DOUBLE
host_sheet_project_name VARCHAR
host_sheet_project_number VARCHAR
host_sheet_status VARCHAR
host_sheet_client VARCHAR
host_sheet_project_type VARCHAR
host_sheet_department VARCHAR
host_sheet_employee VARCHAR
host_sheet_product_skill VARCHAR
host_sheet_request_skillset VARCHAR

To transfer the data from Excel, the Pentaho Data Integration – ETL (extract, transform, load) tool was used, a transformation was created and scheduling on automatic updates.

This was the first step to move data from Excel to a MySQL database, the next step was to take the records in the database and break them into a record by day. As you can see at the top, showing the Excel spreadsheet data, only start and end date of an assignment are known. For the flexibility of reporting, the data had to be broken down to a record by day.

To achieve a record by day, a MYSQL stored procedure was created, it reads the start date a loops around and add a record tills it reach the end date. Below shows the store procedure

 

delimiter //

create procedure tph ()
begin
      declare begin_date datetime;
      declare end_date datetime;
      declare start_date datetime;
      declare total_days int;
      declare allocation decimal(3,2);
      declare hours decimal(25,6);
      declare cur_date datetime;
      declare role nvarchar(256);
        declare title nvarchar(256);
      declare project_number nvarchar(30);
      declare status nvarchar(256);
      declare client nvarchar(256);
      declare project_type nvarchar(256);
      declare department nvarchar(256);
      declare employee nvarchar(256);
      declare product_skill nvarchar(256);
      declare skillset nvarchar(256);
      declare project_name nvarchar(256);
      declare person nvarchar(256);
      declare daysinweek int default 5;
      declare hoursinweek int default 7.5;
      declare no_more_records int default 0;
      declare td cursor for
      select
            host_sheet_person,
            host_sheet_title,
            host_sheet_role,
            host_sheet_start_date,
            host_sheet_end_date,
            host_sheet_allocation,
            host_sheet_project_name,
            host_sheet_project_number,
            host_sheet_status,
            host_sheet_client,
            host_sheet_project_type,
            host_sheet_department,
            host_sheet_employee,
            host_sheet_product_skill,
            host_sheet_request_skillset,
        datediff(host_sheet_end_date, host_sheet_start_date)+1

    from
            host_sheet
      where ( 2010-01-01 <= host_sheet_start_date or 2011-12-31 >= host_sheet_end_date) and host_sheet_project_type = Client
      order by host_sheet_start_date;
          DECLARE CONTINUE HANDLER FOR NOT FOUND
          SET no_more_records = 1;

      create table tp_data (
                  td_date datetime,
                  td_hours decimal(25,9),
                  td_person nvarchar(256),
                  td_title nvarchar(256),
                  td_role nvarchar(256),
                  td_start_date datetime,
                  td_end_date datetime,
                  td_allocation decimal(3,2),
                  td_project_name nvarchar(256),
                  td_project_number nvarchar(30),
                  td_status nvarchar(256),
                  td_client nvarchar(256),
                  td_project_type nvarchar(256),
                  td_department nvarchar(256),
                  td_employee nvarchar(256),
                  td_product_skill nvarchar(256),
                  td_skillset nvarchar(256));
      open td;

      FETCH from td INTO person, title, role, start_date, end_date, allocation, project_name, project_number, status, client, project_type, department, employee, product_skill, skillset, total_days;
      set allocation = allocation/daysinweek;

      REPEAT
              set cur_date = start_date;
              WHILE total_days > 0 DO
              set hours = allocation * hoursinweek;
                      if DAYNAME(cur_date) in ( Monday , Tuesday , Wednesday , Thursday , Friday ) then
                              INSERT INTO tp_data (td_date, td_hours, td_person, td_title, td_role, td_start_date, td_end_date, td_allocation, td_project_name, td_project_number, td_status, td_client, td_project_type, td_department, td_employee, td_product_skill, td_skillset) VALUES
                              (cur_date, hours, person, title, role, start_date, end_date, allocation, project_name, project_number, status, client, project_type, department, employee, product_skill, skillset);
                      end if;
                      set cur_date = DATE_ADD(cur_date, INTERVAL 1 DAY);
                      set total_days = total_days – 1;
              END WHILE;

      FETCH from td INTO person, title, role, start_date, end_date, allocation, project_name, project_number, status, client, project_type, department, employee, product_skill, skillset, total_days;
      set allocation = allocation/daysinweek;

      UNTIL no_more_records = 1
      END REPEAT;
      close td;

END

//

delimiter ;

To create the store procedure in your database, copy the following into MySQL Workbench or any other MySQL tool and execute.

use staffingtool;
drop database if exists tp_data;
drop procedure if exists tph;

delimiter //

create procedure tph ()
begin
      declare begin_date datetime;
      declare end_date datetime;
      declare start_date datetime;
      declare total_days int;
      declare allocation decimal(3,2);
      declare hours decimal(25,6);
      declare cur_date datetime;
      declare role nvarchar(256);
        declare title nvarchar(256);
      declare project_number nvarchar(30);
      declare status nvarchar(256);
      declare client nvarchar(256);
      declare project_type nvarchar(256);
      declare department nvarchar(256);
      declare employee nvarchar(256);
      declare product_skill nvarchar(256);
      declare skillset nvarchar(256);
      declare project_name nvarchar(256);
      declare person nvarchar(256);
      declare daysinweek int default 5;
      declare hoursinweek int default 7.5;
      declare no_more_records int default 0;
      declare td cursor for
      select
            host_sheet_person,
            host_sheet_title,
            host_sheet_role,
            host_sheet_start_date,
            host_sheet_end_date,
            host_sheet_allocation,
            host_sheet_project_name,
            host_sheet_project_number,
            host_sheet_status,
            host_sheet_client,
            host_sheet_project_type,
            host_sheet_department,
            host_sheet_employee,
            host_sheet_product_skill,
            host_sheet_request_skillset,
        datediff(host_sheet_end_date, host_sheet_start_date)+1

    from
            host_sheet
      where ( 2010-01-01 <= host_sheet_start_date or 2011-12-31 >= host_sheet_end_date) and host_sheet_project_type = Client
      order by host_sheet_start_date;
          DECLARE CONTINUE HANDLER FOR NOT FOUND
          SET no_more_records = 1;

      create table tp_data (
                  td_date datetime,
                  td_hours decimal(25,9),
                  td_person nvarchar(256),
                  td_title nvarchar(256),
                  td_role nvarchar(256),
                  td_start_date datetime,
                  td_end_date datetime,
                  td_allocation decimal(3,2),
                  td_project_name nvarchar(256),
                  td_project_number nvarchar(30),
                  td_status nvarchar(256),
                  td_client nvarchar(256),
                  td_project_type nvarchar(256),
                  td_department nvarchar(256),
                  td_employee nvarchar(256),
                  td_product_skill nvarchar(256),
             &nbsp;   td_skillset nvarchar(256));
      open td;

      FETCH from td INTO person, title, role, start_date, end_date, allocation, project_name, project_number, status, client, project_type, department, employee, product_skill, skillset, total_days;
      set allocation = allocation/daysinweek;

      REPEAT
              set cur_date = start_date;
              WHILE total_days > 0 DO
              set hours = allocation * hoursinweek;
                      if DAYNAME(cur_date) in ( Monday , Tuesday , Wednesday , Thursday , Friday ) then
                              INSERT INTO tp_data (td_date, td_hours, td_person, td_title, td_role, td_start_date, td_end_date, td_allocation, td_project_name, td_project_number, td_status, td_client, td_project_type, td_department, td_employee, td_product_skill, td_skillset) VALUES
                              (cur_date, hours, person, title, role, start_date, end_date, allocation, project_name, project_number, status, client, project_type, department, employee, product_skill, skillset);
                      end if;
                      set cur_date = DATE_ADD(cur_date, INTERVAL 1 DAY);
                      set total_days = total_days – 1;
              END WHILE;

      FETCH from td INTO person, title, role, start_date, end_date, allocation, project_name, project_number, status, client, project_type, department, employee, product_skill, skillset, total_days;
      set allocation = allocation/daysinweek;

      UNTIL no_more_records = 1
      END REPEAT;
      close td;

END

//

delimiter ;

When it is loaded – the store procedure can be called by issue this command.

call tph();

As the output of the store procedure, a new database table tp_data is created – the structure is as show below.

td_date           DATETIME
td_hours         DECIMAL
td_person       VARCHAR
td_title         VARCHAR
td_role           VARCHAR
td_start_date DATETIME
td_end_date   DATETIME
td_allocation DECIMAL
td_project_name VARCHAR
td_project_number VARCHAR
td_status       VARCHAR
td_client       VARCHAR
td_project_type VARCHAR
td_department VARCHAR
td_employee   VARCHAR
td_product_skill VARCHAR
td_skillset   VARCHAR

There is one record by working day, weekends are skipped. The new data set allows accurate reporting e.g. utilization by role, utilization by project, resource availability by day or week or month.

There is an endless list of reports that can be created.

Menu