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'
day sales
1 10
2 20
3 30
4 10
5 20
6 30
.
.
.
31 10
Sum 130
select * from #SalesDetailed
UNION ALL
select * from #SalesSum
order by location, day
Assuming your union query returns the correct results, just messes up the order, you can use case
with 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
The isnumeric
will return 1 when day is a number and 0 when it's not.