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")

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.

All-Star

35218 Points

9955 Posts

Moderator

## Need to present data in x / y form, not as decimal

Oct 30, 2020 11:57 AM|bbcompent1|LINK

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.

Mark as answer posts that helped you.All-Star

53691 Points

24031 Posts

## Re: Need to present data in x / y form, not as decimal

Oct 30, 2020 12:25 PM|mgebhard|LINK

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

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.