Something I see quite often when people are dealing with a date dimension is complicated logic to determine the previous quarter, or previous month when that quarter or month is the first of the year. In date dimensions it is quite common to represent year / month combinations as integers like 201601 for January 2016, and year / quarter combinations 20161 to represent the first quarter of 2016.
Calculate previous month, quarter with string manipulation
I have seen many production instances where a lot of complicated string logic was used to convert from INT to CHAR and back again several times to get the desired results. I admit, in my earlier days I did the same thing myself. It is not uncommon to see string manipulation like this to calculate something relatively simple into something fairly difficult to read and more than just a few seconds to code. Say it is January 15th in the year 2016 and my date dimension has a CurrentMonth column with a value of 201601, and the current quarter column with a value of 20161. Below is code similar to what I have found to calculate the previous month and previous quarter in production systems:
DECLARE @Month INT = 201601 ,@Quarter INT = 20161 SELECT CASE WHEN RIGHT(CONVERT(CHAR(6),@Month),1) = 1 THEN CONVERT(CHAR(4),LEFT(CONVERT(CHAR(6),@Month),4) - 1) + '12' ELSE @Month - 1 END AS PreviousMonth ,CASE WHEN RIGHT(CONVERT(CHAR(5),@Quarter),1) = 1 THEN CONVERT(CHAR(4),LEFT(CONVERT(CHAR(5),@Quarter),4) - 1) + '4' ELSE @Quarter - 1 END AS PreviousQuarter;
By using a CASE statement to test if the last digit equals 1 you determine whether to use your string manipulation, or to do some simpler math. The CASE statement also requires some string manipulation. I have seen all sorts of complicated string manipulation used to do this sort of previous period calculation.
Calculate previous month, quarter with simple math
Although I think there is a good argument to building these sorts of values into your date dimension, there are many cases where these columns do not exist. I offer a slightly simpler alternative. It is just a little less CPU intensive and I think it is considerably easier to read. When your current month and current quarter values are simple integers, use simple math.
DECLARE @Month INT = 201601 ,@Quarter INT = 20161 SELECT CASE WHEN @Month % 100 = 1 THEN @Month- 89 ELSE @Month - 1 END AS PreviousMonth ,CASE WHEN @Quarter % 10 = 1 THEN @Quarter - 7 ELSE @Quarter - 1 END AS PreviousQuarter;
In the case statement you modulo the month value by 100, or the quarter value by 10. If the remainder is equal to 1 then you are in the first period of the year. If you are calculating the previous month you subtract 89 from the current month. If you are calculating the previous quarter you subtract 7.
The results are exactly the same whether you use string manipulation
or math to resolve the previous period.
The math is a little more CPU efficient and I think considerably easier to look at to figure out what it is doing.
With a little thought you can certainly extend this logic to a host of previous or future periods.
I hope you find this information useful. Thanks for reading.