SQL Question
Sort Numbers in varchar value in SQL Server
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'
On my second result set, is the loading of the sum at the bottom, it happens that the word 'Sum' is aligned to the column of Day, and I used Union All TO COMBINE the result set together, expected result set is something to this like
day sales
1 10
2 20
3 30
4 10
5 20
6 30
.
.
.
31 10
Sum 130
What I did is to convert the day value, originally in int to varchar datatype. this is to successfully join columns and it did, the new conflict is the sorting of the number
select * from #SalesDetailed
UNION ALL
select * from #SalesSum
order by location, day
Answer
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.
Source (Stackoverflow)