cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
EShook
Engaged Sweeper
I am currently using the following bit of code to format System Uptime:
 
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' d ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hrs ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' mins ' As Uptime,


My question is, is there a way to have the report sort this column by the default tblAssets.Uptime values when the header is clicked? Since I had to convert the ticks away from ints it seems to simply grab highest first digit, which can be very inaccurate. Any help is appreciated!
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
SQL uses Order By, not Sort By. If you would like to order by the Uptime field within the SQL query, you will need to do so as seen in the first sample query posted here. If you would like to order by the Uptime field within the report results by clicking on the column header, you will need to display the unconverted uptime values within the report results as a separate column/expression.

View solution in original post

3 REPLIES 3
Susan_A
Lansweeper Alumni
SQL uses Order By, not Sort By. If you would like to order by the Uptime field within the SQL query, you will need to do so as seen in the first sample query posted here. If you would like to order by the Uptime field within the report results by clicking on the column header, you will need to display the unconverted uptime values within the report results as a separate column/expression.
EShook
Engaged Sweeper
Yeah, I understand that is my problem. I want to know if there is a way to sort this by a default field. There is no way to just call the tblAssets.Uptime field for sorting purposes? I can and have already set it to sort by this initially but once you sort by anything else it's undone and you have to relaunch the report. I would think there has to be a way to have a field sorted by the values in an associated field, but I am fairly new to SQL so maybe I am just expecting too much. I would expect something like:

Sort [Uptime] by tblAssets.Uptime


or maybe

Order [Uptime] by tblAssets.Uptime


Something similar to the overall report view, but specified for a specific fields operations only.
RCorbeil
Honored Sweeper II
You're creating a text string there, so if you try to sort on the output you're creating, it will be an ASCII-betical sort (e.g. 1, 10, 11, 12, 2, 20, 21, ...). Unless you include the numeric uptime field to sort on, the only way you'll be able to sort your text string the way you want is if you make sure to zero-pad the converted values, e.g. "005 d 01 hrs 02 mins".