My job was to create a report for monthly totals of a specific year. It will compute monthly totals from April 1 of that year to March 31 of the next year and must return exactly 12 rows for that 12 months even if there may no records for a certain month. A tough job indeed.
Early Solution
My colleague was the one given first the task and what she did was create a temporary tables for the dates using some form of looping. It inserts dates while looping making that 12 month 1 year cycle.
The next step was to join the real table with the temporary table and compute the totals. It works because the temporary table fills the hole – the possible missing month is filled with empty values using the month of the temporary table.
However, the solution was not accepted by the quality control team therefore rejected. My job was to refactor and finish it all the way.
Overview
The report is just too simple: sum up a certain value according to categories by month from April 1 to March 31 of the next year depending of the start year input. What makes it complicated was that the query must return a complete 12 rows representing the 12 month cycle even if there are no data or even if there are missing months on the table.
For the first part of the query: the summation part, it looks like this:
SELECT (CONVERT(VARCHAR(4), DATEPART(YEAR, t01.estimated_date)) + '/' + REPLACE(STR(DATEPART(MONTH, t01.estimated_date), 2), ' ', 0)) AS estimated_month,
SUM(CASE WHEN t20.some_category_field = 'value_1' THEN t01.weight ELSE 0 END) AS weight_01,
SUM(CASE WHEN t20.some_category_field = 'value_2' THEN t01.weight ELSE 0 END) AS weight_02
FROM table_1 t01
JOIN table_20 t20 ON t01.foreign_key = t20.primary_key
WHERE t01.estimated_date BETWEEN @start_date AND @end_date
AND t01.weight > 0
GROUP BY (CONVERT(VARCHAR(4), DATEPART(YEAR, t01.estimated_date)) + '/' + REPLACE(STR(DATEPART(MONTH, t01.estimated_date), 2), ' ', 0))
The final output must format the date into yyyy/mm format.
The other part is that we need to generate the dates or shall we say the months from April 1 to March 31. We can use looping but according to the review team, it is not allowed. That’s why I searched the internet and found a way to generate dates using T-SQL via CTE from a blog.
What I need is a way to generate yyyy/mm and that the day is not important. However to complete the DATETIME field, a value of 1 for the day part is acceptable. So here is the CTE query.
WITH CTE AS
(SELECT @start_date AS cte_start_date
UNION ALL SELECT DATEADD(MONTH, 1, cte_start_date)
FROM CTE
WHERE DATEADD(MONTH, 1, cte_start_date) <= @end_date)
The contents are like: 2010/04/01, 2010/05/01 … 2011/03/01 (12 rows all in all). The idea is to use FULL OUTER JOIN for the totals and the required months. By stripping off the day part of the CTE content, we can join CTE and out original query.
Combining it all
Finally, this is what the final SQL script should be.
USE project_name
GO
DECLARE @year int;
DECLARE @start_date datetime;
DECLARE @end_date datetime;
-- Edit parameter year
SET @year = 2010;
-- Do not edit
SET @start_date = convert(datetime, convert(varchar(4), @year) + '/04/01');
SET @end_date = convert(datetime, convert(varchar(4), @year+1) + '/03/31');
-- Create date range for 12 months in 12 rows
WITH CTE AS
(SELECT @start_date AS cte_start_date
UNION ALL SELECT DATEADD(MONTH, 1, cte_start_date)
FROM CTE
WHERE DATEADD(MONTH, 1, cte_start_date) <= @end_date)
SELECT un_months.year_month,
(CASE
WHEN ISNULL(weight_01, -1) = -1 THEN 0
ELSE weight_01
END) AS weight_01,
(CASE
WHEN ISNULL(weight_02, -1) = -1 THEN 0
ELSE weight_02
END) AS weight_02
FROM
(SELECT (CONVERT(VARCHAR(4), DATEPART(YEAR, t01.estimated_date)) + '/' + REPLACE(STR(DATEPART(MONTH, t01.estimated_date), 2), ' ', 0)) AS estimated_month,
SUM(CASE WHEN t20.some_category_field = 'value_1' THEN t01.weight ELSE 0 END) AS weight_01,
SUM(CASE WHEN t20.some_category_field = 'value_2' THEN t01.weight ELSE 0 END) AS weight_02
FROM table_1 t01
JOIN table_20 t20 ON t01.foreign_key = t20.primary_key
WHERE t01.estimated_date BETWEEN @start_date AND @end_date
AND t01.weight > 0
GROUP BY (CONVERT(VARCHAR(4), DATEPART(YEAR, t01.estimated_date)) + '/' + REPLACE(STR(DATEPART(MONTH, t01.estimated_date), 2), ' ', 0))) un_totals
FULL OUTER JOIN
(SELECT (CONVERT(VARCHAR(4), DATEPART(YEAR, cte_start_date)) + '/' + REPLACE(STR(DATEPART(MONTH, cte_start_date), 2), ' ', 0)) AS year_month
FROM CTE) un_months ON un_totals.estimated_month = un_months.year_month
ORDER BY un_months.year_month ASC
I added a NULL checking at the top SELECT to ensure that NULL values due to missing months are displayed as 0.
That’s it.
Hello, This is good information. I have similar issue, If a period is given as 2015-09 I need to go back 12 periods from given period, also need to get another 12 periods from 2014-09 backward to compare the sales month by between previous year and current year. Please help me to resolve this
Thanks
Just move the starting year 2 years backwards so that you will have a 24 months columns.