Post reply

Convert Function?

  • December 31, 2007 at 11:49 am

    #181713

    I have a field in one of my tables that is a varchar filed and it holds decimal values ranging from 2.7 to 2.70000002 I need to do an average on this field but I can't use aggregate operations on varchar. Can I convert this to Int to be able to do this calculation. Realistically the values that are 2.7000002 I would like truncated to 2.7 and then do an average....is this possible??

  • December 31, 2007 at 12:27 pm

    #763769

    Hey Molly,

    I would use the convert function as you suggested, but you would not want to use INT. You will want to use the decimal data type. Depending on what size you want, you can choose to round the values by reducing the number of decimals in the definition. Here is an example:

    [font="System"]select convert (decimal(18, 8), '2.70002');[/font]

    This will preserve all of the digits in this case. If you only wanted 1 digit after the decimal place, you could do this:

    [font="System"]select convert (decimal(18, 1), '2.70002');[/font]

    You should get the idea. Also, you may still use the average function. For more info on the nuances of decimal, check out books online.

    Thanks,

    Eric

  • December 31, 2007 at 1:09 pm

    #763781

    The correct answer should be...

    ... change the column in the table to the correct data type.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 2, 2008 at 6:36 am

    #764009

    I can't change the data type in the table because it's used as a free text field, values are based on INR levels for medical records. For reporting purposes I just need to change this field in a view in order to report on it, I am using Crystal 8.5 and it won't see it as a decimal number to average. So in essence I'm trying to find a work around. the convert decimal works but I have various values that need to be converted?

  • January 2, 2008 at 6:42 am

    #764012

    Molly Cary (1/2/2008)

    I can't change the data type in the table because it's used as a free text field, values are based on INR levels for medical records. For reporting purposes I just need to change this field in a view in order to report on it, I am using Crystal 8.5 and it won't see it as a decimal number to average. So in essence I'm trying to find a work around. the convert decimal works but I have various values that need to be converted?

    1 As said, always use proper datatype

    2 If you cant change the column, if possible, add new column with proper datatype;update it with values from varchar column and use that column for further maipulations

    3 If you want to do calculations at Crystal reports, make use of formula field to convert that to decimal and use that field in the report

    Madhivanan

    Failing to plan is Planning to fail

  • January 2, 2008 at 6:43 am

    #764013

    What other values would you like to convert?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 2, 2008 at 7:13 am

    #764029

    It's just that one column that I need to convert all values to a decimal. I'm limited due to the way our system is setup I can't manipulate this table in anyway, therefore having to do it in the background. I've attempted to do a formula in crystal, either I'm doing it wrong or it won't work because of the data type.

  • January 2, 2008 at 7:24 am

    #764037

    Molly,

    Have you considered creating a View with the modified column structure, or a series of calculated columns off of the original "free form" Column?

    If so, you could create your view, preserving the original table and data, and the build your Crystal report off of the view.

    Keep Us Posted.

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • January 2, 2008 at 7:28 am

    #764039

    Well my report is based off a view already, so in essense yes I guess I could do that and redefine the column there..... I've thought about that but I'm not quite sure how to do this 😀

  • January 2, 2008 at 7:41 am

    #764040

    Molly,

    The best way to learn is "under fire"! 😎

    Can you drop/recreate the view? If so then ...

    1) Post create table statement of the base table in question.

    2) Post the Create view statement as is.

    3) Let us know what column(s) need to be changed within the view.

    The goal here is to modify the view in such a way that all we need to do in Crystal is change the font sizes and the like, letting SQL Server do all the behind the scenes work. I refer to this as "new paint and screen doors", or make the data look pretty.

    For future reference, please see:

    Forum Etiquette: How to post data/code on a forum to get the best help

    http://qa.sqlservercentral.com/articles/Best+Practices/61537/

    This will show you how to build a test case for others to review and work with in future postings.

    Keep Us Posted.

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • January 2, 2008 at 7:42 am

    #764041

    I ran this query and found that I have null values also in this column, this is messing up my calculations as well.

  • January 2, 2008 at 7:44 am

    #764042

    THANKS!! I will do that Much appriciated!

  • January 2, 2008 at 8:05 am

    #764053

    Molly,

    For your NULL issue, look up the ISNULL Function in SQL Server Books On Line (BOL). It will allow you to change the value in the given column from a NULL to a default value of your specification. (I assume that a Zero will suffice here, for your calculation purposes.)

    There a more ways to deal with NULL values, if that does not meet your needs.

    Keep Us Posted.

    "Key"
    MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

  • January 2, 2008 at 9:12 pm

    #764249

    For future reference, please see:

    Forum Etiquette: How to post data/code on a forum to get the best help

    http://qa.sqlservercentral.com/articles/Best+Practices/61537/

    This will show you how to build a test case for others to review and work with in future postings.

    Man, am I glad someone besides me keeps recommending that article 😛 Thanks, Damon... if we could get all the regulars to do the same thing, we'd be able to concentrate more on helping folks fix their problems.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • January 3, 2008 at 8:31 am

    #764388

    Yes thanks....I wasn't aware of this travesty that I was doing. 😉 I have reposted (hopefully correctly).... 😀

Viewing 15 posts - 1 through 15 (of 20 total)

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