My Goal is to load a monthly-daily tabular presentation of sales data with sum total and other average computation at the bottom,
I have one data result set with one column that is named as 'Day' which corresponds to the days of the month, with automatic datatype of int.
select datepart(day, a.date ) as 'Day'
select * from #SalesDetailed
select * from #SalesSum
order by location, day
Assuming your union query returns the correct results, just messes up the order, you can use
isnumeric in the order by clause to manipulate your sort:
SELECT * FROM ( SELECT * FROM #SalesDetailed UNION ALL SELECT * FROM #SalesSum ) u ORDER BY location, ISNUMERIC(day) DESC, CASE WHEN ISNUMERIC(day) = 1 THEN cast(day as int) end
isnumeric will return 1 when day is a number and 0 when it's not.