SQL Server – Generating Date Range With CTE

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    
)
&#91;/sourcecode&#93;

The contents are like: 2010/04/01, 2010/05/01 ... 2011/03/01 (12 rows all in all).

The idea is to use <code>FULL OUTER JOIN</code> 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.

<h2>Combining it all</h2>

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.

This entry was posted in SQL, SQL Server and tagged , , , , , . Bookmark the permalink.

2 Responses to SQL Server – Generating Date Range With CTE

  1. Rajesh says:

    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

  2. lysender says:

    Just move the starting year 2 years backwards so that you will have a 24 months columns.

Leave a Reply

Your email address will not be published. Required fields are marked *