Conditionally formatting results

[gbaughma]

SO I have this table... and I've been told the rule is:

"If the number is < 10, then it should be 3 decimal places. If the number is > 10, then it should be 0 decimal places."

In other words:
0.110 ----> 0.110
1.1 ----> 1.100
9.99 ----> 9.990
10.123 ----> 10
150.5 ----> 150

... and so forth.

So meanwhile, the "Result" field is a nvarchar (meaning that it is a string).... and because of some other calculations going on, there is a "CASE" statement in there as well.... here's a snippet....

... previous part of select statement ....
CASE WebSample.Units
WHEN 'mg/Kg' THEN [Sign] + Result
WHEN 'ug/KG' THEN [Sign] + CAST(CONVERT(decimal(18,0),Result)*.001 AS nvarchar)
WHEN '%' THEN [Sign] + Result + '%'
WHEN '' THEN 'N/A'
ELSE [Sign] + Result
END AS Result,
.... continued part of select, including PIVOT ....

So as you can see, there is some math going on there already, but I need to additionally process and say "If it's <10, then format it as #0.000 otherwise format it as ##0" sort of thing.

Any thoughts on an easy way to accomplish this? If I were writing it in VB, I'd just write a quick function to convert the string to a number, see if it's > 10, if so than INT the number, otherwise format it #.000.....

TIA!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

It doesn't look like math to me. It looks like it is concatenating strings.

Anyway... you may be better off using a computed column for this. I kinda like computed columns because it automatically updates and you can easily decide where to use it.

To do this, you'll need to alter the WebSample table to include the computed column, and then modify your code above to use the new computed column instead of the existing one.

To alter the table...

Alter Table WebSample 
Add   ResultsDisplay As 
        Case When IsNumeric(Result) = 1 
             Then Case When Convert(Decimal(20,3), Result) >= 10 
                       Then Convert(VarChar(10), Convert(Decimal(20,0), Result))
                       Else Convert(VarChar(10), Convert(Decimal(20,3), Result))
                       End
             Else Result End

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gbaughma]

That would work, if the results were always numeric.

Did I mention that the database here would make even the most seasoned SQL person rip their hair out in frustration?

Sometimes the result may be "N/A" (Not Applicable), or "N/D" (No Detect), or "Pos" (Positive) or "Neg" (Negative) or even "See Report" (if tests were outsourced).

Pretty much EVERY field in the backend database is a string field, even for things that would (normally) be numeric or dates or whatever, just because of that .5% of "Except when it's....."

But I'll take a look at that code more closely tomorrow... tonight, it's me and the playstation (until the wife gets home... lol)



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gmmastros]

It does handle non-numeric data. If the data is not numeric, it does not change. Only if it is numeric will it have the conditional formatting.

I tested it like this:

Create Table #WebSample([sign] varchar(1), Result VarChar(20))

Insert Into #WebSample Values('', '9.99')
Insert Into #WebSample Values('-', '9.99')
Insert Into #WebSample Values('', '24.123')
Insert Into #WebSample Values('-', '30.456')
Insert Into #WebSample Values('-', 'Not a number')

Alter Table #WebSample 
Add   ResultsDisplay As 
		Case When IsNumeric(Result) = 1 
             Then Case When Convert(Decimal(20,3), Result) >= 10 
                       Then Convert(VarChar(10), Convert(Decimal(20,0), Result))
                       Else Convert(VarChar(10), Convert(Decimal(20,3), Result))
                       End
             Else Result End

Select * From #WebSample

Drop Table #WebSample

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom