By: Paul Schuliger

BI Consultant at Key2 Consulting


This is the second article about the use of the Date Matrix in reporting applications. The first article can be found here if you missed it. The Date Matrix is a SQL tool that is used to solve the problem of needing multiple and different time periods in a column format within a report. It’s a table that looks like the table below. Be sure refer to the part 1 of this post for a complete understanding of each part’s purpose in the table below.

SQL Date Matrix - Aggregated Results

This article will cover the actual creation and implementation of the Date Matrix. Remember that the Date Matrix shown in this article is just an illustration of what it can do and that there are many more possibilities in your own implementation.

Let’s get started with the creation and implementation of the Date Matrix. The SQL DDL below can be used to create the SQL table in any database, as this is a SQL tool and is not dependent on a specific flavor of a database. The DDL clearly shows that there are three field groups that need to be created:  Grouping fields, Join fields, and the Multipliers.

(Figure 1)

The next step is to populate the Date Matrix with all of the necessary records. The example shown above has 13 records that cover one grouping for the period of August 2016 (the specific value is 201608). Refer to the Date Matrix (figure 1) above to help in understanding the code.

The SQL stored procedure described below (figure 2) is used to create the records that populate each Grouping Value. Keep in mind that the hardest part of the stored procedure is often the Date logic that is necessary to create the multipliers. For example, creating a multiplier that reflects a rolling 12 months or 18 months would need extra attention in ensuring that the logic is accurate.

The stored procedure starts with the declaration of variables. There is one variable for each field in the Date Matrix table, along with a couple of local variables that are used during the processing.

(Figure 2)

The next step is critical in the stored procedure as it controls the dates that are used in the stored procedure. The @v_loop_join variable represents the Join Value (or 201508) that is being processed within the Grouping Value (201608), which is represented by the @v_loop_current variable.

Setting these variables determines what time periods are going to be processed within the Grouping Value. You can see the start of the looping functionality in Figure 3. The rest of the code in the loop is focused on creating the Multipliers that are necessary for the specific Join Value record within the current Grouping Value.

The reason that the first value for the Join Values is 201508 is that it’s necessary to go back 13 months to properly determine the multiplier “Same Month Last Year”. So when building your implementation, make sure to determine how far back in time you need to go in order to produce the correct results. If you are going many years back in time, know that your Date Matrix will be have many records, which could be a performance issue.

In this simple illustration, the dates are set up to run one grouping value at a time. If you’re reading this article I’m going to assume that you have the ability to add logic that would automatically change the Grouping Value and continue processing for other values.

(Figure 3)

Setting the Grouping Values and the Join Values

Using the variables that were declared for each field at the top of the stored procedure, each variable is assigned a value that is then used in the INSERT statement below. The four variables in Figure 3 are used to populate the variables shown in Figure 4 with some date string manipulation needed to create a specific field of datatype DATE.

(Figure 4)

Multiplier Logic

This is where the real work is done in determining the Multipler logic. In this example, there are four Multipliers with straight forward logic – current month, last month, current month last year, and year-to-date (YTD). If you were to add other Multipliers, this is where the logic would be created. And the logic is used to create the correct values for each specific Join Value that the stored procedure loops through. For each time through the looping structure, ensure that your logic is correct for each multiplier individually.

(Figure 5)

After all of the Multipliers are completed and the values are accurately populated, the next step is to use all of the variables within the Join Value and insert them into the Date Matrix. Below is the insert statement that is used to do so.

(Figure 6)

Once the insert statement is run, the stored procedure increments the looping value (which is the Join Value) and proceeds to process the next Join Value. In the illustration, the Join Value would be changed from 201508 to 201509 after the first record is processed. You can see that there is a check in the logic to determine if the current month for the Join Value is the 12th month of the year. If so, the code correctly makes the Join Value the 1st month of the next year.

(Figure 7)

Remember that the goal of the Date Matrix is to ease the development of your reporting application by providing a database table that allows for easy aggregation of multiple and different time periods. The table can be used to join with fact tables to get results like those shown in figure 8 (below) as an example. The Date Matrix is an excellent table to have (in your Data Warehouse or Data Mart) that all users or reporting systems can take advantage of.

(Figure 8)

SQL Date Matrix - Aggregated Result Set

Though it does take effort to initially create and populate the Date Matrix table, the functionality that it provides for reporting purposes is well worth the time! If you find yourself developing SQL to create WTD, MTD, or YTD comparisons, use the Date Matrix to do it for you.

You can be creative with the different reporting periods that you put into the system. And you can surprise your user community with varied timeframes that are not easy to come by in typical reporting solutions – like 7 weeks running, or the just the weekend days in each month.

The Date Matrix used in this article is done for Months but there can be separate tables that are used for a Weekly Date Matrix or if your database holds many years of historical date you can create a Yearly Date Matrix.

The Date Matrix is not needed for every report but when necessary it’s very helpful!

If you would like the complete code in an electronic format please send us an e-mail here at Key2 Consulting. We would be glad to send it to you. And if you implement the Date Matrix, we would love to hear how you have done so.