Post reply

Just give me two digits, pleeeeze

  • October 31, 2007 at 1:09 pm

    #63110

    I am preparing a stored procedure to obtain some financial data. One of the data items is a 30 day average. I have written a case statement to capture this as an integer if it is a large number, but I would like to limit the value to two digits if the average is less than 1.

    I've tried ROUND, CAST, DECIMAL, FLOOR and every combination thereof, but I cannot force .77272721 to convert/truncate to .77 or .78 (either would be acceptable). Does anyone know how to do this?

    The underlying individual values are floats, if that is relevant.

    Also, if there is a better solution in SQL 2005, I could use that.

    TIA,

    Elliott

  • October 31, 2007 at 1:19 pm

    #747226

    declare @a float

    set @a = .77272721

    select @a

    select convert(decimal(9,2),@a)

    select cast( @a as decimal(9,2))

  • October 31, 2007 at 2:38 pm

    #747255

    Sorry, my description was incomplete.

    The procedure captures averages and stores them in a table as floats. When I call up a result-set from that table I would like to display any averages with (absolute) values less than one as two decimal place numerics. I would like to display any averages with (absolute) values greater than one as simple integers (no decimal).

    My query syntax looks like this:

    Select Name, Date,

    CASE

    WHEN ABS(MyAvg)> 1 THEN CAST(MyAvg as int) ELSE CAST(MyAvg as decimal(2,2)

    I would like data that looks like this:

    ACME 10/29/07 .77

    BETA 10/29/07 -14

    CARA

  • October 31, 2007 at 2:42 pm

    #747256

    Oops, one slip of the mouse and the previous incomplete message posted.

    Desired result set:

    ACME 10/29/07 .77

    BETA 10/29/07 -14

    GAMMA 10/29/07 58

    Current results:

    ACME 10/29/07 .77

    BETA 10/29/07 -14.00

    GAMMA 10/29/07 58.00

    Depending on the data conversions I try, either all the numbers go to simple integers (in which case the .77 average displays as zero) or all numbers contain two decimal places. I would like to use a case statement to dynamically choose the display depending on the value.

    Elliott

  • October 31, 2007 at 2:49 pm

    #747259

    A columncan only return one data type. You could kludge it by returning the value as a varchar and using string manipulation to return what is left or right of the decimal depending on the position of the decimal.

  • October 31, 2007 at 3:17 pm

    #747265

    **A column can only return one datatype**

    It seems reasonable that a table column could only hold one data-type, but I thought I could force a result-set to be more flexible.

    I agree that string processing might be the way to go (at least for the web page display), but ...Arggggh.

    BTW--I wonder how Microsoft does it. When I look at my table in Enterprise Manager, the values are displayed just like I want, even though they are floats in reality.

  • October 31, 2007 at 3:40 pm

    #747274

    It's done in front end application - EM.

    Same about datetime values.

    _____________
    Code for TallyGenerator

  • November 1, 2007 at 5:43 am

    #747385

    IF you are doing this in a web page then use the format function in vbscript.

  • November 1, 2007 at 8:55 am

    #747437

    Thanks for the info, guys. I definitely understand that SQL is generally the wrong place to manipulate the diplay of data. But our company just lost one of the two programmers, and so I thought I could do most of the work in a proc, and just leave the programmer the task of calling that proc from the web application.

    I am still mystified though about how Microsoft presents a "friendly" display of the values in a table (e.g. displaying decimal fractions and whole numbers in the same column). Maybe they are using some kind of application to power Enterprise Manager?

    Anyway, I won't waste more time on it. Thanks again.

    Elliott

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply