locked
Need to present data in x / y form, not as decimal RRS feed

  • Question

  • User-718146471 posted

    Hello and good morning folks. The above mentioned I have working well in my web application. What I've been tasked with is showing that calculation as a SQL Query Result. This is where I am stuck. To further clarify, say my vulnerability risk density is 2/10000. The web application takes .0023482383479 and rounds it to the nearest .00x. What I'm trying to do is display that risk density as that fraction in a SQL Query Result. Here's the sql query that I have thus far, maybe you folks can help me fill in the missing pieces? Thanks in advance for any help.

    SELECT        Project_Name, 
                        CONVERT(varchar(10), CAST(Last_Scan_Date AS DateTime), 101) AS ScanDate, 
                        CAST(Last_Scan_Date AS DateTime) AS Sort, LOC, CAST(HighVuln AS int) AS High, 
                        CAST(MedVuln AS int) AS Medium, 
                        CAST(CAST(HighVuln AS float) / NULLIF (CAST(LOC AS float), 0) AS float) AS DefectRatio
    FROM         RawData
    WHERE       (DateIns = '2020-10-01')
    ORDER BY Sort DESC
    
          -- Want values to present as 1/10000 for DefectRatio in SQL Query
          -- In c# business logic on web application values are created by:
    
          -- num1 = vIssue1 / lines1;
          -- exponent1 = GetExponent(num1);
          -- value1 = (decimal)Math.Pow(10, exponent1);
          -- calc1 = num1 * vIssue1;
          -- (calc1 * 10).ToString("0.0") + "/" + (value1 * 10).ToString("0")

    Friday, October 30, 2020 11:57 AM

All replies

  • User475983607 posted

    This is a math question.  If the denominator is always 10,000 then the math is....

    0.0023482383479 * 10000 = 23.4

    From there it's just converting the number to a string and a little concatenation to get "23.4/10000"

    If the denominator is variable you'll need to find another constant like the number of decimal places.   In a loop, multiply the decimal result by 10 until the value is greater than 1 (or 10 or 100 depending on the decimal places).  The loop count is the number of 10 in the denominator.  ie. 10*10*10...

    Edit: you can use standard numeric formatting to get an exponent; "2.34E+5"  from there use string functions and a look up to get to the expected format.  Split the string at "E".  Look up the +5 to get 10,000.  this could be a switch statement or table lookup.  Then concatenate. 

    Friday, October 30, 2020 12:25 PM