Post reply

No Decimal in Reults

  • November 6, 2009 at 6:50 am

    #216459

    The results of the below Query are 85.074626865671600 I want to make this only 85%, but not sure how to do this.

    select (SELECT Count(CallLog.ClosedBy)*1.0 From [Support].[dbo].[CallLog] WHERE ClosedDate Between '2009-01-07' and '2009-01-08')

    /

    (SELECT Count(CallLog.RecvdBy)*1.0 From [Support].[dbo].[CallLog] WHERE RecvdDate Between '2009-01-07' and '2009-01-09') *100 as pct

  • November 6, 2009 at 6:52 am

    #1075877

    how about using 'round' ?


    Clear Sky SQL
    My Blog[/url]

  • November 6, 2009 at 6:53 am

    #1075878

    ... or CONVERT(INT, 85.074626865671600)

  • November 6, 2009 at 6:55 am

    #1075879

    I am not sure how to do either of those I am about a week into learning / using SQL, sorry I am a newbie

  • November 6, 2009 at 7:08 am

    #1075886

    BOL links

    round : http://msdn.microsoft.com/en-us/library/ms175003.aspx

    convert : http://msdn.microsoft.com/en-us/library/ms187928.aspx


    Clear Sky SQL
    My Blog[/url]

  • November 6, 2009 at 7:14 am

    #1075891

    You can enclose your current result with ROUND/CONVERT. Follow Dave's links to see how the functions work.

    Greets

    Flo

  • November 7, 2009 at 3:54 am

    #1076296

    This might assist:

    -- Create a test table variable

    DECLARE @CallLog

    TABLE

    (

    ClosedBy VARCHAR(30) NULL,

    RecvdBy VARCHAR(30) NULL

    );

    -- Some sample data

    INSERT @CallLog (ClosedBy, RecvdBy) VALUES ('Bob', 'Alan');

    INSERT @CallLog (ClosedBy, RecvdBy) VALUES (NULL, 'Charlie');

    INSERT @CallLog (ClosedBy, RecvdBy) VALUES ('Gene', 'Sally');

    INSERT @CallLog (ClosedBy, RecvdBy) VALUES ('Susan', 'Bob');

    INSERT @CallLog (ClosedBy, RecvdBy) VALUES (NULL, 'Gene');

    INSERT @CallLog (ClosedBy, RecvdBy) VALUES ('John', 'Gene');

    -- The query

    SELECT pct = FLOOR(ROUND(COUNT(ClosedBy) * 100.0 / COUNT(RecvdBy), 0))

    FROM @CallLog;

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • November 9, 2009 at 3:37 am

    #1076565

    Round(Number to be rouned, number of digit after the '.')

  • November 9, 2009 at 7:11 am

    #1076637

    Try out this sorry if I'm wrong,

    select ceiling(85.074626865671600),floor(85.074626865671600),round(85.074626865671600,1)

  • November 9, 2009 at 7:16 am

    #1076639

    i think round(number,0) or floor(number) do that

  • November 9, 2009 at 7:21 am

    #1076641

    Omar halwagy (11/9/2009)

    i think round(number,0) or floor(number) do that

    i think round(number,0) -->round (85.074626865671600,0) will display like this '85.000000000000000'

    Thanks,

    Chandru

  • November 9, 2009 at 7:48 am

    #1076647

    that true wasnt he want to get 85 ?

  • November 9, 2009 at 7:56 am

    #1076651

    How would I put it in the this Query?

    select (SELECT Count(CallLog.ClosedBy)*1.0 From [Support].[dbo].[CallLog] WHERE ClosedDate Between '2005-01-07' and '2010-01-08')

    /

    (SELECT Count(CallLog.RecvdBy)*1.0 From [Support].[dbo].[CallLog] WHERE RecvdDate Between '2005-01-07' and '2010-01-09') *100 as pct

    Everything I try gives me a syntax error

  • November 11, 2009 at 1:58 am

    #1077686

    It would help if you provided the actual error message you are getting.

  • November 11, 2009 at 2:13 am

    #1077689

    jason.gerding (11/9/2009)

    How would I put it in the this Query?

    select (SELECT Count(CallLog.ClosedBy)*1.0 From [Support].[dbo].[CallLog] WHERE ClosedDate Between '2005-01-07' and '2010-01-08')/(SELECT Count(CallLog.RecvdBy)*1.0 From [Support].[dbo].[CallLog] WHERE RecvdDate Between '2005-01-07' and '2010-01-09') *100 as pct

    Everything I try gives me a syntax error

    Have you tried running the code I posted before?

    I went to great trouble to provide sample data, and give you a finished optimized query.

    Did you read it or try it or think about it?

    It's difficult to stay positive some days.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

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

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