SQL Server – Using Temporary Tables

The original query was almost perfect – it displays the daily totals for a given month. However, specs changed and they need to add the grand total to the last row. Then another specs for the MAX and MIN rows. Overwhelmed with the specs, I used temporary tables to make it quick.

WARNING: All queries below are not tested and will probably have typo’s or errors. All I wanted to show is how the logic is done.

Requirements

The original SQL query already displays the daily totals for a given month and the new requirements were:

1. Display the grand total based on the previous query and position it after the daily totals
2. Display the monthly average based on the previous daily totals and position next to grand total row
3. Display the monthly maximum the same way as above
4. Display the monthly minimum the same way as above

What makes me decide to use a temporary table was the original query is too long that repeating the query makes it more complicated. Let’s assume that the query looks like below (simplified):

SELECT
	estimated_date,
	SUM(weight_1),
	SUM(weight_2)
FROM
	table_name
WHERE
	estimated_date BETWEEN @start_date AND @end_date
GROUP BY
	estimated_date
ORDER BY
	estimated_date ASC

Though the actual query is way longer than our example, they both have the same requirements. So that’s it! How do we add grand total to the last row? Add another row (average) after grand total? Maximum and minimum follows.

The only solution I could think is to use UNION. I will create unions for grand totals, average, maximum and minimum using the same query as the first! But that means a lot of work, so let’s use temporary tables.

Final Query

First, I used SELECT INTO so that the original query’s result is inserted to the temporary table.

SELECT
	estimated_date,
	SUM(weight_1),
	SUM(weight_2)
INTO
	#temp_by_month
FROM
	table_name
WHERE
	estimated_date BETWEEN @start_date AND @end_date
GROUP BY
	estimated_date

Since I already get the data I need, the final query would simply query the temporary database and run aggregate functions on them. Then I’ll use UNION to merge them all. I also added some sort of sorting key so that I can arrange the data the way I want, first the daily totals, next the grand total, next the average, next the maximum and last the minimum.

SELECT
	estimated_date,
	weight_1,
	weight_2
FROM
	(
		SELECT
			estimated_date,
			weight_1,
			weight_2,
			1 AS sort_key
		FROM
			#temp_by_month

		UNION

		SELECT
			'Grand Total' AS estimated_date,
			SUM(weight_1) AS weight_1,
			SUM(weight_2) AS weight_2,
			2 AS sort_key
		FROM
			#temp_by_month

		UNION

		SELECT
			'Average' AS estimated_date,
			AVG(weight_1) AS weight_1,
			AVG(weight_2) AS weight_2,
			3 AS sort_key
		FROM
			#temp_by_month

		UNION

		SELECT
			'Max' AS estimated_date,
			MAX(weight_1) AS weight_1,
			MAX(weight_2) AS weight_2,
			4 AS sort_key
		FROM
			#temp_by_month

		UNION

		SELECT
			'Min' AS estimated_date,
			MIN(weight_1) AS weight_1,
			MIN(weight_2) AS weight_2,
			5 AS sort_key
		FROM
			#temp_by_month
	) un
ORDER BY
	un.sort_key ASC

That’s it! Although there are more simple solution such as GROUP BY plus ROLLUP or WITH ROLLUP, I prefer not to use the vendor specific syntax and use ISO compliant code, which means more work for me but less trouble in the future.