Decimal to Integer Conversion 1

[larrydavid]

Hello,

Currently I have this query in a SQL Server 2000 database:

SELECT		TOP 10 [Test_Ratio] = (COUNT(dbo.CLAIM.[CLAIM])* 1.0 / (SELECT count(*) FROM dbo.[CASE] WHERE [CASE].[TEST_REASON] like '%deny%')
FROM        dbo.[CASE] INNER JOIN
            dbo.CLAIM ON dbo.[CASE].CLAIM = dbo.CLAIM.CLAIM 
WHERE	   dbo.[CASE].STATUS = 2 
ORDER BY	[Test_Ratio] DESC

Which gives me this output:

[Test_Ratio]
0.049320748540
0.016394784221
0.008674777674
0.008047356647
0.007419935621
0.006628839543
0.005919580991
0.005892301816
0.005701347591
0.005210322439

Which I am trying to convert to this output:

049 
016 
008 
008 
007 
006 
005 
005 
005 
005

I have tried CAST and CONVERT to int (returns 0), to string then parsing out the characters I want via substring (but fails when trying to use a comparison operator), trim, round, you name it. I'm completely stumped as to how I can achieve the output I need on this.

The end purpose of this is to be able to do a greater than comparison on this value such as:

if (dr["Test_Ratio"] != null && (int)dr["Test_Ratio"] > 005)
{
    testColor = _red;
}

Any help would be so greatly appreciated.

Thanks,
Larry

 

[bborissov]

SELECT TOP 10 
       CAST((CLAIM.CntClaim / Tbl1.Cnt) * 1000 as int) AS Test_Ratio
FROM dbo.[CASE]
FULL JOIN (SELECT count(*)*1.0 AS Cnt
                  FROM [CASE]
            WHERE [CASE].[TEST_REASON] like '%deny%') Tbl1
INNER JOIN (SELECT CLAIM, COUNT(CLAIM)*1.0 AS CntClaim
                   FROM CLAIM 
            GROUP BY CLAIM) Claim
      ON [CASE].CLAIM = CLAIM
WHERE    [CASE].STATUS = 2
ORDER BY [Test_Ratio] DESC

Bu that wouldn't show you the leading zeroes.
(not tested)

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[larrydavid]

Boris,

You are the man. I wish I had 1/10th of your SQL knowledge.

Many thanks,
Larry