Calculate Percentage Column 2

[larrydavid]

Hello,

IN SS2005, I am trying to calculate a percentage column based on counts from two tables:

SELECT	   [Dispute Rate] = (SELECT count(*) FROM TEST1 WHERE RECEIVED_DATE > 1/1/2011) / (SELECT count(*) FROM TEST2) * 100, 
FROM         TEST t INNER JOIN
             TEST2 t2 ON t.CLAIMNO = t2.CLAIMNO INNER JOIN
WHERE	    TEST1.PAIDDATE > '20110101'

With the above query I am returning 0. I've been through several forums and my understanding is I need an outer query, for instance with a derived table or cte. Then convert the result to varchar and add a '%' to the result. Am I on the right track? If someone could show me an example I would so greatly appreciate it.

Thanks,
Larry

 

[bborissov]

SELECT Tst1.Cnt / Tst2.Cnt AS [Dispute Rate]
FROM (SELECT COUNT(*)*1.0 AS Cnt 
             FROM TEST1
      WHERE RECEIVED_DATE > '200110101') tst1
CROSS JOIN (SELECT COUNT(*)*1.0 AS Cnt 
             FROM TEST2) tst2

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

 

[larrydavid]

Boris,

Many thanks. Now I have to figure out how to implement this within my overall query.

Best Regards,
Larry

 

[Qik3Coder]

Sometimes, with odd calculations, it's just easier to join this value in as a derived query.


SELECT..., prcnt.MyPercentage
FROM...(Your EXISTING query)
LEFT JOIN
[tab]( percentages query) as prcnt ON
?.ID = prcnt.ID

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[larrydavid]

Thanks everyone!