A few days ago I received an interesting challenge from one of our clients. The lady was attempting to estimate her potential monthly revenue recognition for the fiscal year beginning January 1, 2015, through December 31, 2015. The lady named Linda sells goods and services (each class yielding differing sales margins).

In the first portion of this two-part discussion, we shall be looking at the revenue projections for goods.

Linda’s business rules are as follows.

In the month in which a client order for goods is booked (and this may be some time in the ‘future’ say for Christmas time), if the order is placed before the 21^st of that month then she ‘claims’ the total revenue for the booked month. Should the order date be greater than the 21^st of that month then she claims only 70% and the remaining 30% in the following month.

Our challenge for today’s get together is to create a query that will provide Linda the information that she requires based upon her corporate data.

Let’s get started!

Getting Started

Perhaps the best manner in which to begin is to have a high-level glance at how the final data will appear AFTER applying Linda’s revenue projection rules.

The reader will note that on row 1 (in the screen dump above), the order date was ‘2015-02-12’ which was earlier than Linda’s ‘cut off’ date of the 21^st of the month. This said she will project the revenue from this row ‘in full’ for the month of February 2015. Further, because the order date was less than the 21^st of the month, the defined field ‘SplitYN’ has been set to ‘nosplit’. The implications of this will become apparent in a few minutes.

Having a look at row 4 (see above) we note that the order date is ‘2015-02-27’ which is, in fact, GREATER than Linda’s 21^st cut-off date for recognizing the full revenue for the month of February 2015 and therefore she will only recognize 70% of the ‘Original Revenue’ (see the last column of the screenshot above) in February, and the remaining 30% in March 2015 (See row 5 above). In this case, the defined field ‘SplitYN’ is set to ‘split’.

With a high-level understanding of Linda’s business rules and having seen some of the expected results, let us begin constructing our business solution. The full code listing may be found in Addenda 1.

We begin by declaring a few variables

The purpose of these variables will become apparent in a few seconds.

Remembering that Linda wishes to look at the data for the 2015 calendar / fiscal year, then our start date must be December 1, 2014, running through and including December 2015.

At this point, you are probably crying “foul”!! It is important to remember that Linda may have had split data booked in the month of December where she posted 70% in that December and 30% for January 2015. It is this 30% that must be added to our January 2015 projections.

We set our data extraction dates to

At this point, an explanation is required. @BeginFiscal will be the start date for our raw data extract. @EndFiscal will be the end date of data extract. The reader is reminded that some data may have been split in December 2014 and we, therefore, must extract this data. We shall rid ourselves of the December 2014 data that was not split plus the split data posted in December 2014, in our final extract (via the predicate logic) as we shall shortly see.

Linda’s raw data (from the orders table) may be seen in the screen shot above.

Creating and processing our query

Our first task is to ascertain which orders must have their revenues split in order to allocate the projections correctly.

Parts of the code snippet above also require an explanation. In this snippet, we set the value of the field ‘SplitYN’. The business rules state that I must split my revenue if and only if the order date is beyond the 21^st of any given month. Many ‘moons’ ago I created a small user-defined function called YearMth. YearMth accepts a datetime field and returns a varchar(6) value containing YYYYMM. The listing for YearMth may be found in Addenda 2.

The results of executing this code snippet may be seen below:

Note also that we have trapped the number of rows that have been extracted through the use of the following code. We shall use the number of rows shortly.

The avid reader will have by this time realized that in order for us to achieve our end goal, for any ‘split record’ that we must reduce the revenue of the original month to 70% and add an additional record (for the next calendar month) representing the remaining 30%, thus making up the total revenue over two calendar months as opposed to one. The “nosplit” records are not an issue.

To achieve this we are going to create a temporary table called #rawdata10 which will have an identity as a key field. The reason for this will become apparent in a few seconds.

We now insert the records shown in the screen dump above into #rawdata10. The results of doing so may be seen in the screen shot below:

At this point, the astute reader will have noted two important facts pertaining only to records whose revenue must be split over two months

1. The revenue for first month must reduced to 70% (as discussed above).
2. An additional row must be added for the next month and that row’s revenue must be 30% of the revenue of the previous month, i.e. to make up the full 100% (once again as discussed above).

In order to achieve this goal and have an understanding of the purpose for creating the Identity column “Id”, we are going to parse each record within the #rawdata10 temporary table and should the “SplitYN” field has a value of “Split”, then we going to write that row (with only 70% of the revenue) to #rawData11.

This achieved we shall increment YearMth by one month, only this time we are going to write the same record with the incremented “Yearmth” and 30% of the original data to the same temporary data file, #rawdata11. All non-split rows are written as well to #rawdata11, however once and only once.

In order to achieve all of this, we need to utilize a loop. As we shall be looping and inserting rows into #rawdata11 as we go, the temporary table must have been created PRIOR to entering the loop.

The ‘looping code’ may be seen below:

We then increment the counter and re-iterate through within the loop

For the ‘non-believers’ amongst us, I have taken the liberty of extracting the splits before applying the predicate logic. Please see the screenshot below and then compare it to the result set following that screenshot.

Adding the “nosplits” we find the following for our extract (see below).

It is important to remember that at this stage that the records have not been sorted by YearMth.

Formating the data for a Matrix

Linda has told us that she wishes to see the revenue for each month summarized by the department , by month.

In order to achieve this, we must define a few more variables and set their values accordingly (see below).

Running the code immediately above produces the following results. The reader will note that the @month variables run from 201501 through 201512.

Adding the final touch to our extract

In order to give Linda the summary that she requires, we are now going to summarize the data by department and by month. This time, however, we shall be utilizing the value of “NewYearMth”. As a recap for split records, there will be two consecutive values for “NewYearMth” and for the non-split records, “NewYearMth” will be the same as “YearMth”.

The code to summarize the data may be seen below:

The result from executing this code may be seen below:

The astute reader will note that not only are there results for the current months of this fiscal year, but there are also results for October and November 2015 (which at the time of writing this article are future dates).

Quo Vadis

Using the SQL Server Reporting techniques that I have described in my previous articles, one may create a stored procedure from this code and utilize it to create a “matrix-based reports”.

Further, with the same code, one can extract the results to be placed in any one of the plethoras of charts available to Reporting Services. This exercise is left up to the reader.

Conclusions

In today’s get together we saw how we could help Linda project her revenue for any fiscal year. We also saw that we could split the projected revenue into two parts if the ‘deal’ was signed too late in the month to recognize the whole amount.

Linda also caters for parties which essentially is a service that she provides and, as such, she has different business rules to projecting this type of revenue. We shall have a look at this in our next get together.

In the interim, happy programming!

Addenda 1

Addenda2

• Author
• Recent Posts

Steve Simon

Steve Simon is a SQL Server MVP and a senior BI Development Engineer with Atrion Networking. He has been involved with database design and analysis for over 29 years.

Steve has presented papers at 8 PASS Summits and one at PASS Europe 2009 and 2010. He has recently presented a Master Data Services presentation at the PASS Amsterdam Rally.

Steve has presented 5 papers at the Information Builders' Summits. He is a PASS regional mentor.

View all posts by Steve Simon

Latest posts by Steve Simon (see all)

• Reporting in SQL Server – Using calculated Expressions within reports - December 19, 2016
• How to use Expressions within SQL Server Reporting Services to create efficient reports - December 9, 2016
• How to use SQL Server Data Quality Services to ensure the correct aggregation of data - November 9, 2016