I'm working on a query to display in my IPAM page the DHCP scopes that are empty or near empty. Close to getting it done, but I'd like my output to be a bit friendlier if possible, wondering if there is any way to do it and I have a couple questions. Here is my full query as I have it right now, my questions will be based on this a bit.
SELECT FriendlyName AS Scope, (ROUND(PercentUsed, 2) * 1.0) AS [Percent IP Space Used], AvailableCount AS [IPS Available], UsedCount AS [IPS Used], ReservedCount AS [IPS Reserved]
WHERE (PercentUsed < 5) AND (TotalCount > 253)
ORDER BY PercentUsed, UsedCount, AvailableCount, FriendlyName
The first question being, where I have [Percent IP Space Used] as an alias for the column, I would actually love to have it more like the column names in the standard "Top XX Subnets by % IP Address Used" that is on the main IPAM page, or specifically I'd like the column named "% IP Address Used". I've tried everything I can think of from escaping the '%' with a backslash to trying to represent it as a hex digit, quoting it, etc. etc... Nothing has worked. Any ideas how to get it to look nicer?
The second question also deals with that same column, but the values produced in the table. Ideally I'd like them to be percentages to two decimal points, also like in the standard resource noted above. If I used the column itself (ie: PercentUsed), I get 4 decimal places after the decimal point. Using ROUND() on its own seems to have no effect on the number of places after the decimal point, only on the significant digits past the decimal point. ie: If the column value is 0.12345 and I used ROUNT(PercentUsed, 2) it changes it to 0.12000, where I'd really like it to strip the 0's after the '12'. Thinking this is a bug, but not sure how to fix it.
Now, if I multiply the value given to me by the ROUND() function by something like 1.0 (not an integer like '1'), I get what I want somewhat. From the example above I would end up getting '0.12'. It's not quite right in the case of an initial value of something like '0.90000' would show up as '0.9' at this point, rather than 0.90. Or, if its just a straight '0', I get that instead of 0.00. (ie: ideally I'd like 2 places no matter what, or zero padded)... But it's close, so now you're saying that I figured it out already and what the heck am I asking?
Ok, the problem then arises in that I want to put a '%' after it, once again so it better matches the built in resource shown above. So in my first example I would like to end up seeing '0.12%' in the column. No problem you say, just convert it to a string with ToString() and add a '%' at the end. The minute I wrap a ToString() around my results, it goes back to the 5 places after the decimal point. ie: if I leave it at "ROUND(PercentUsed, 2) * 1.0" I get 0.12. If I change that to "ToString(ROUND(PercentUsed, 2) * 1.0) + '%'" I get "0.12000%"!!
I'm just finding it very frustrating trying to get things formatted as I'd like them to be. It seems as if things aren't working quite as they should be, I'd call it a bug.
Anyone have any insight on this on how to get it to do what I want? Even getting closer would be nice...