MySQL Occupation Health and Safety Financial Report

Introduction:

As part of my job with the National Forest Service, I have to prepare the financial report of Occupational Health and Safety every year. Lately I have been doing this in SAP Business Object and thought it would be fun to do the same in MySQL Workbench.

About the dataset:

I created a simplified data model of a fictitious forest services company. The complete database can be loaded into MySQL Workbench with this single file. You can find the financial report in views.

Below there is an EER diagram I have created in workbench using reversed engineering. It is a more or less star data model with dimension (blue) and fact (salmon) tables and "one to many" relationships between them.

The tables themselves look like this:

Query description:

Every year in February, the forest services company is required to prepare a health and safety financial report for the last year for the Central Statistical Office. The report consists of 4 categories that are not directly reflected in their database. In addition, the categories mix materials and activities. The result should be:

Let`s get down to MySQL:

First step:

I started with getting the data of the materials from the [plan_material] table. It should be joined with other tables such as [plan] and [occupation] to retrieve information about the plan year and occupation description:

Last year:

The report has to be prepaired for the last year. To achieve this I used a CURDATE function in WHERE clause:

Union:

Apart from materials we will also need activities. The queries for materials and activities are pretty much the same. To append the tables we will utilize UNION ALL operator:

Pivoting and grouping

Now that we have one list of activities and materials, we need to group them together to meet the final requirement. In addition, the groups should be pivoted. The CASE WHEN method can do these two things for us. However, first our source query should be wrapped in Common Table Expression:

Totals

The last thing to do is to get the sums for the rows and columns. To achieve totals for columns, you can use the ROLLUP extension of GROUP BY clause at the end of our query:

The result is:

Totals for rows need another special CASE WHEN statement:

In this case the SUM function has to be used separately:

Finally the query is: