Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187

    Unanswered: Help with my SP please

    Hey guys, I have a question.
    I have a table with 2 decimal fields 10,2.
    In my sp i get those fields and divid them by an number.
    I get like 5 or 6 0's trailing the calculation.
    Ex: 191.6/2 I get 95.800000
    Why?
    Here is my sp:
    CREATE PROCEDURE sp_SummaryReport
    (
    @startdate datetime,
    @enddate datetime
    )

    AS
    BEGIN

    SELECT tblLegendReportAbv.ReportType AS ReportType,
    SUM(tblSummaryData.Volume) AS Volume,
    SUM(tblSummaryData.NetEffect)/COUNT(tblSummaryData.DataID) AS NetEffect,
    SUM(tblSummaryData.GrossEffect)/COUNT(tblSummaryData.DataID) AS GrossEffect
    FROM tblSummaryData
    INNER JOIN tblLegendReportAbv ON LTRIM(RTRIM(LOWER(tblSummaryData.ReportType))) = LTRIM(RTRIM(LOWER(tblLegendReportAbv.ReportAbv)))
    WHERE tblSummaryData.WeekEndDate BETWEEN @startdate AND @enddate
    GROUP BY tblSummaryData.ReportType,tblLegendReportAbv.Repor tType

    END
    GO




    Please help

  2. #2
    Join Date
    Dec 2002
    Posts
    29

    Talking

    That's beacause the implicit conversion made by SQL Server when dividing to different types. For more info see "Data Type Precedence" in your SQL help file.

    But this only an aesthetic problem, it can be easily solved with an explicit conversion or with a cast like this:

    cast(191.6/2 as decimal(10,2))

    For more info on cast and convert see "CAST and CONVERT" in your SQL help file.


    Best regards!

  3. #3
    Join Date
    May 2003
    Location
    Atlanta, GA
    Posts
    187
    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •