How to get Decimal place (Full Version)

All Forums >> [Management Products] >> Microsoft Systems Management Server >> Web Reporting



Message

kcas -> How to get Decimal place (7/16/2008 3:04:45 PM)

Hi
Can anyone tell me how to modifity the following to get 2 decimal places on the "% Compliant" column?
so this needs to be somehow changed:
(100 * TotalInstalled / CASE WHEN (TotalInstalled + TotalMissing) = 0 Then 1 else (TotalInstalled + TotalMissing) End) as '% Compliant'


Select SnapshotDate, SiteID, PatchID, TotalInstalled, TotalMissing, (TotalInstalled + TotalMissing) as Total,
(100 * TotalInstalled / CASE WHEN (TotalInstalled + TotalMissing) = 0 Then 1 else (TotalInstalled + TotalMissing) End) as '% Compliant'

from PatchDB

Thanks in Advance.



jnelson993 -> RE: How to get Decimal place (7/16/2008 3:53:15 PM)

Use CAST or CONVERT to force the result to a specific precision (left and right of the decimal) and scale(right of the decimal)

SELECT
SnapshotDate,
SiteID,
PatchID,
TotalInstalled,
TotalMissing,
(TotalInstalled + TotalMissing) AS Total,
CAST((100 * TotalInstalled / CASE
                           WHEN (TotalInstalled + TotalMissing) = 0 THEN 1
                           ELSE (TotalInstalled + TotalMissing)
                         END) AS DECIMAL(5,2)) AS [% Compliant]



jnelson993 -> RE: How to get Decimal place (7/16/2008 3:57:59 PM)

Oh, and I mistyped that first sentance.  (left of the decimal)  should be (left AND right of the decimal)

 100.00 would be a max precision of 5. 
    .00 would mean a scale of 2

So Decimal(5,2) would cover it for you.



kcas -> RE: How to get Decimal place (7/16/2008 4:27:29 PM)

Thanks John!   When I ran it I got 2 decimal place but unfortunately they all zeros.  Any ideas on that?



jnelson993 -> RE: How to get Decimal place (7/16/2008 5:16:57 PM)

Oh, if you're doing multiplication/division on all integers, it will do integer math.  If you include a floating point number, it will do floating point math. 

So, just put 100.0 in there and it will treat the whole thing as a floating point operation
SELECT
  SnapshotDate,
  SiteID,
  PatchID,
  TotalInstalled,
  TotalMissing,
  (TotalInstalled + TotalMissing) AS Total,
  CAST((100.0 * TotalInstalled / CASE
                                    WHEN (TotalInstalled + TotalMissing) = 0 THEN 1
                                    ELSE (TotalInstalled + TotalMissing)
                                 END) AS DECIMAL(5,2)) AS [% Compliant]



kcas -> RE: How to get Decimal place (7/16/2008 5:30:03 PM)

Worked great.  You're da man!

Thanks.  keith
IXOYE



jnelson993 -> RE: How to get Decimal place (7/16/2008 5:44:14 PM)

YHWH !



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.25