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

    Unanswered: Help with decimals

    Hi all,
    This is what I have:
    DECLARE @OnTime int
    DECLARE @UnControlled int
    DECLARE @Volume int
    DECLARE @GrossEffect decimal(10,2)
    DECLARE @NetEffect decimal(10,2)
    DECLARE @WeekEndDate datetime

    SET @OnTime = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE OnTimeFlag = 1 AND ARSScanType = 'D' AND ARSType='AN')
    SET @UnControlled = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE ControlFlag = 'U' AND ARSScanType = 'D' AND ARSType='AN')
    SET @Volume = (SELECT COUNT(DataID) FROM tblEDITempARS WHERE ARSScanType = 'D' AND ARSType='AN')
    SET @GrossEffect = (@OnTime/@Volume * 100)
    SET @NetEffect = ((@OnTime + @UnControlled)/@Volume * 100)
    SET @WeekEndDate = (SELECT DISTINCT WeekEndDate FROM tblEDITempARS)

    INSERT INTO tblSummaryData2(ReportType, Volume, NetEffect, GrossEffect, WeekEndDate)
    VALUES ('AN',
    @Volume,
    @NetEffect,
    @GrossEffect,
    @WeekEndDate)

    My numbers are:
    OnTime=8089
    Uncontrolled=6
    Volume=8095

    The GrossEffect comes as 0 and the NetEffect=100
    AN 8095 0 100 2/21/2004

    Why is my GrossEffect = 0??? it should be 99.9
    Last edited by bpolunin; 02-22-04 at 16:15.

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    Change

    SET @GrossEffect = (@OnTime/@Volume * 100)

    into

    SET @GrossEffect = (CAST((CAST(@OnTime AS NUMERIC(38, 2))/CAST(@Volume AS NUMERIC(38, 2))*100) AS NUMERIC(4,2)))

    Output will be 99.93

    Check the SQL Server Books Online with the keywords "numeric data type" for the details.

Posting Permissions

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