rickyProgrammer rickyProgrammer - 1 year ago 98
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
select * from #SalesSum
order by location, day

Answer Source

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 #SalesDetailed
    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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download