Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1

    Answered: Precentages in result

    Trying to create a dataset for a report. I need to bring back percentage in the result set. The fields that I am using to get the percentage have valid data but the result field is 0.00%. Is there reason I cant bring back a percentage field?

    Code:
    SELECT JobNum, Mailed, LT_7,  BT_6_10, GT_10, 
    Format([LT_7]/[Mailed], 'p') AS PctScaned1, 
    Format([BT_6_10]/[Mailed], 'p') AS PctScaned2, 
    Format([GT_10]/[Mailed], 'p') AS PctScaned3, 
    [Mailed]-TotScan AS No_Scans 
    FROM 
    	(
            SELECT 
    	dbo.CT2_View_1.JobNum, 
    	Count(dbo.CT2_View_1.HT_SerNum) AS Mailed, 
    	Sum(IIf([WorkDays]<7,1,0)) AS LT_7, 
    	Sum(IIf([workDays] Between 7 And 10,1,0)) AS BT_6_10, 
    	Sum(IIf([WorkDays]>10,1,0)) AS GT_10, 
    	Sum(IIf([WorkDays]>0,1,0)) AS TotScan 
    	FROM dbo.CT2_View_1
    	GROUP BY dbo.CT2_View_1.JobNum
    	) as a;

  2. Best Answer
    Posted by Pat Phelan

    "Just to amuse me (and test my assumption), try running with the following modification:
    Code:
    SELECT JobNum, Mailed, LT_7,  BT_6_10, GT_10, 
    Format([LT_7]/[Mailed], 'p') AS PctScaned1, 
    Format([BT_6_10]/[Mailed], 'p') AS PctScaned2, 
    Format([GT_10]/[Mailed], 'p') AS PctScaned3, 
    [Mailed]-TotScan AS No_Scans 
    FROM 
    	(
            SELECT 
    	dbo.CT2_View_1.JobNum, 
    	1e0*Count(dbo.CT2_View_1.HT_SerNum) AS Mailed, 
    	Sum(IIf([WorkDays]<7,1,0)) AS LT_7, 
    	Sum(IIf([workDays] Between 7 And 10,1,0)) AS BT_6_10, 
    	Sum(IIf([WorkDays]>10,1,0)) AS GT_10, 
    	Sum(IIf([WorkDays]>0,1,0)) AS TotScan 
    	FROM dbo.CT2_View_1
    	GROUP BY dbo.CT2_View_1.JobNum
    	) as a;
    If that works I can give you a much better explanation and solution, but it is much more typing.

    -PatP"


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just to amuse me (and test my assumption), try running with the following modification:
    Code:
    SELECT JobNum, Mailed, LT_7,  BT_6_10, GT_10, 
    Format([LT_7]/[Mailed], 'p') AS PctScaned1, 
    Format([BT_6_10]/[Mailed], 'p') AS PctScaned2, 
    Format([GT_10]/[Mailed], 'p') AS PctScaned3, 
    [Mailed]-TotScan AS No_Scans 
    FROM 
    	(
            SELECT 
    	dbo.CT2_View_1.JobNum, 
    	1e0*Count(dbo.CT2_View_1.HT_SerNum) AS Mailed, 
    	Sum(IIf([WorkDays]<7,1,0)) AS LT_7, 
    	Sum(IIf([workDays] Between 7 And 10,1,0)) AS BT_6_10, 
    	Sum(IIf([WorkDays]>10,1,0)) AS GT_10, 
    	Sum(IIf([WorkDays]>0,1,0)) AS TotScan 
    	FROM dbo.CT2_View_1
    	GROUP BY dbo.CT2_View_1.JobNum
    	) as a;
    If that works I can give you a much better explanation and solution, but it is much more typing.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Mar 2015
    Posts
    38
    Provided Answers: 1
    It worked, Ok I would be more than interested in why. Thank you very much for answer. Once again you have saved the day.
    Last edited by wjburke2; 11-05-15 at 10:06.

  5. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The kernel of your problem is a classic type conflict.

    When doing integer math, 3 / 5 is zero.

    When doing real math, 3.0 / 5.0 is 0.60.

    1e0 is a constant that represents 1 as a floating point number (look up scientific notation if the E notation throws you off). When you multiply the result of the Count(*) by 1e0, you force it to be a real number. Type promotion always works predictably, and nearly always moves to the more general representation in an expression (so an int divided by a real causes the int to be promoted to a real) which tends to be what people want and expect.

    As you probably noticed, the quick and dirty patch that I threw in to keep the typing simple cause any place where you referenced [Mailed] by itself or in an expression that only used plus or minus operators to be used and shown as a real number. This can cause formatting nuisances, and it can cause really subtle and funky math problems if you build on these expressions.

    The work around I originally gave you works, and shouldn't cause any harm based on the code that you've posted. In order to do things "right" from a computer science standpoint you should remove the multiplier from the Count() expression, then formally CAST() [Mailed] to a REAL datatype where you explicitly want it to be treated as a real number when you are dividing by it. Doing that would look like:
    Code:
    SELECT JobNum, Mailed, LT_7,  BT_6_10, GT_10
    ,  Format([LT_7]   / Cast([Mailed] AS REAL), 'p') AS PctScaned1
    ,  Format([BT_6_10]/ Cast([Mailed] AS REAL), 'p') AS PctScaned2
    ,  Format([GT_10]  / Cast([Mailed] AS REAL), 'p') AS PctScaned3
    ,  [Mailed]-TotScan AS No_Scans 
       FROM 
          (SELECT 
             dbo.CT2_View_1.JobNum
    ,        Count(dbo.CT2_View_1.HT_SerNum) AS Mailed, 
    ,        Sum(IIf([WorkDays]<7,1,0)) AS LT_7, 
    ,        Sum(IIf([workDays] Between 7 And 10,1,0)) AS BT_6_10, 
    ,        Sum(IIf([WorkDays]>10,1,0)) AS GT_10, 
    ,        Sum(IIf([WorkDays]>0,1,0)) AS TotScan 
             FROM dbo.CT2_View_1
             GROUP BY dbo.CT2_View_1.JobNum
          ) as a;
    This will keep the data types as you would expect, so INT values stay as INTs and REAL values (percents) are REALs. It is a bit more typing, but it makes what you want explicit so even if (heaven forbid) the type conversion/promotion rules should change then this code ought to work safely or at the very least raise an appropriate error message.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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