How to get Decimal place

myITforum.com Community Forum

Home Forums Blogs Live Support chat Search Articles Wiki FAQ Email Lists Register Login My Profile Inbox Address Book My Subscription My Forums

Photo Gallery Member List Search Calendars FAQ Ticket List Log Out

All Forums RSS Feed Subscription:

How to get Decimal place

View related threads: (in this forum | in all forums)

Logged in as: Guest

Printable Version

All Forums >> [Management Products] >> Microsoft Systems Management Server >> Web Reporting >> How to get Decimal place

Page: [1]

Message

<< Older Topic Newer Topic >>

How to get Decimal place - 7/16/2008 3:04:45 PM

kcas

Posts: 66
Score: 1
Joined: 3/25/2003
Status: offline

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.

Post #: 1

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

jnelson993

Posts: 794
Score: 109
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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]

< Message edited by jnelson993 -- 7/16/2008 3:58:27 PM >

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to kcas)

Post #: 2

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

jnelson993

Posts: 794
Score: 109
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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.

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to jnelson993)

Post #: 3

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

kcas

Posts: 66
Score: 1
Joined: 3/25/2003
Status: offline

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

(in reply to jnelson993)

Post #: 4

RE: How to get Decimal place - 7/16/2008 5:16:57 PM

jnelson993

Posts: 794
Score: 109
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

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]

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts

(in reply to kcas)

Post #: 5

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

kcas

Posts: 66
Score: 1
Joined: 3/25/2003
Status: offline

Worked great. You're da man!

Thanks. keith
IXOYE

(in reply to jnelson993)

Post #: 6

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

jnelson993

Posts: 794
Score: 109
Joined: 2/18/2005
From: Minneapolis, MN
Status: offline

YHWH !

_____________________________

Number2 (John Nelson)
MyITForum - Blog
MyITForum - Forum Posts