SQL Server Query - Sorting data using Order By in Union All

Question

Here's the sample data, this table summarized the sales of one tenant in 5 years and per month. Also, the total sales, average sales per month and per day are computed. to be able to do this I have used 4 sql query to get the

1. Per Month and per year sales,
2. Total Sales,
3. Average Sales Per Month, and
4. Average Sales Per Day

Then, I used the UNION ALL method to produce this kind of output:

Here's the part of the code

SELECT tenantcode, MONTH, year1, year2, year3, year4, year5
from #SalesPerYear 
UNION ALL
SELECT * FROM #TotalSales 
UNION ALL
SELECT * FROM #AveSalesMonth 
UNION ALL 
SELECT * FROM #AveSalesDay
ORDER BY TENANTCODE

Each temp table corresponds to each query to get the mentioned each query set above.

But this only produce an output in a SINGLE TENANT only, what I want is to make it flexible by making it multiple tenant selection, I was able to do it but the ordering is not pleasing. The desired output is to be something like this

But the actual output is NOT ACHIEVED. the Tenant1 ordering is okay, but the tenant2 is rumbled.

something to this

I hope I explained it well enough.

Answer 1

You can add a faux column in all queries that identify the type of row (per year, total, monthly average and daily average2). Then sort the combined result by tenant, row type and month number, in that order:

SELECT 1 AS [row type], tenantcode, [month], year1, year2, year3, year4, year5 FROM #SalesPerYear
UNION ALL
SELECT 2, #TotalSales.* FROM #TotalSales
UNION ALL
SELECT 3, #AveSalesMonth.* FROM #AveSalesMonth
UNION ALL
SELECT 4, #AveSalesDay.* FROM #AveSalesDay
ORDER BY tenantcode, [row type], [month]

You can convert month name to number using DATEPART(MONTH, 'January 1, 2000') or month number to name using DATENAME(MONTH, '2000-1-1').

Alternate solution

Since the results should be ordered by the month column in this order: JAUNARY, ..., DECEMBER, TOTAL, AVE/MONTH, AVE/DAY. You can simply use a nested query and a lengthy CASE statement:

SELECT * FROM (
    SELECT tenantcode, [month], year1, year2, year3, year4, year5 FROM #SalesPerYear  UNION ALL
    SELECT * FROM #TotalSales    UNION ALL
    SELECT * FROM #AveSalesMonth UNION ALL 
    SELECT * FROM #AveSalesDay
) AS foobar
ORDER BY tenantcode, CASE [month] 
    WHEN 'JANUARY'   THEN 1
    WHEN 'FEBRUARY'  THEN 2
    WHEN 'MARCH'     THEN 3
    WHEN 'APRIL'     THEN 4
    WHEN 'MAY'       THEN 5
    WHEN 'JUNE'      THEN 6
    WHEN 'JULY'      THEN 7
    WHEN 'AUGUST'    THEN 8
    WHEN 'SEPTEMBER' THEN 9
    WHEN 'OCTOBER'   THEN 10
    WHEN 'NOVEMBER'  THEN 11
    WHEN 'DECEMBER'  THEN 12
END

There are a couple of ways to simplify the case statement but I would rather keep it readable.